All, I've taken a stab at creating a reproduceable test case based on the characterisitics of the production issues I'm seeing. But clearly there's an element I'm missing, because I'm not able to produce the bug with a pgbench-based test case.
My current test has FKs, updating both FK'd tables, updating both indexed and non-indexed columns, and doing multiple updates in the same transaction, and lock-blocking. Files are attached in case someone has better ideas. queue_bench.sql is the setup file, and then you do: pgbench -n -T 600 -c 15 -j 5 -f queue_adder.bench -f queue_worker.bench -f queue_worker.bench -f queue_worker.bench -f queue_worker.bench {dbname} ... or whatever levels of c and j make sense on your hardware. FWIW, this particular test case might make a nice destruction test case for replication testing, too. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
CREATE TABLE queue ( queue_id serial not null primary key, ts timestamptz not null default now(), cat int not null, status text not null default 'new', current_step int not null default 1 ); CREATE TABLE queue_steps ( queue_id int not null, step int not null, status text not null default 'pending', done timestamptz, payload text ); INSERT INTO queue ( ts, cat, current_step ) SELECT now() - interval '15 seconds' + ( i * interval '1 second' ), (random()*3)::int, 1 FROM generate_series(1,12) as gs(i); INSERT INTO queue_steps ( queue_id, step, status, payload ) SELECT queue_id, gs.i, 'pending', md5(random()::text) FROM queue, generate_series(1,3) as gs(i); CREATE INDEX queue_ts ON queue(ts DESC); CREATE INDEX queue_step_pk ON queue_steps(queue_id, step); ALTER TABLE queue_steps ADD CONSTRAINT queue_fk FOREIGN KEY ( queue_id ) REFERENCES queue(queue_id); CREATE FUNCTION add_queue_item ( ncat int ) RETURNS INT LANGUAGE plpgsql AS $f$ DECLARE qid INT; BEGIN INSERT INTO queue ( ts, cat, status, current_step ) VALUES ( now(), ncat, 'new', 1 ) RETURNING queue_id INTO qid; INSERT INTO queue_steps ( queue_id, step, status, payload ) SELECT qid, gs.i, 'pending', md5(random()::text) FROM generate_series(1,3) as gs(i); RETURN qid; END; $f$; CREATE OR REPLACE FUNCTION queue_step ( ncat int ) RETURNS INT LANGUAGE plpgsql AS $f$ DECLARE qid INT; stnum INT; BEGIN SELECT queue_id INTO qid FROM queue WHERE status = 'working' AND cat = ncat ORDER BY ts LIMIT 1 FOR UPDATE; IF qid IS NULL THEN SELECT queue_id INTO qid FROM queue WHERE status = 'new' AND cat = ncat ORDER BY ts LIMIT 1 FOR UPDATE; END IF; SELECT step FROM queue_steps INTO stnum WHERE queue_id = qid AND status = 'pending' ORDER BY step LIMIT 1; IF stnum = 1 THEN UPDATE queue SET status = 'working' WHERE queue_id = qid; END IF; UPDATE queue_steps SET status = 'working' WHERE queue_id = qid AND step = stnum; PERFORM pg_sleep(0.002); UPDATE queue_steps SET status = 'done', done = now() WHERE queue_id = qid AND step = stnum; IF stnum = 3 THEN UPDATE queue SET status = 'done' WHERE queue_id = qid; ELSE UPDATE queue SET current_step = stnum + 1 WHERE queue_id = qid; END IF; RETURN qid; END; $f$; ANALYZE;
\setrandom rcat 0 3 \setrandom wtime 1 3 BEGIN; SELECT add_queue_item(:rcat); END; \sleep :wtime ms
\setrandom rcat 0 3 \setrandom wtime 1 3 BEGIN; SELECT queue_step(:rcat); \sleep :wtime ms END;
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers