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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
