From: | Filipe Pina <filipe(dot)pina(at)impactzero(dot)pt> |
---|---|
To: | Postgresql General <pgsql-general(at)postgresql(dot)org> |
Subject: | serialization failure why? |
Date: | 2015-06-16 13:32:12 |
Message-ID: | 1434461532.3040.1@smtp.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have these 2 tables:
CREATE TABLE "stuff" ("number" integer NOT NULL PRIMARY KEY, "title"
varchar(40) NOT NULL);
CREATE TABLE "stuff_ext" ("stuff_ptr_id" integer NOT NULL PRIMARY KEY,
"extra" integer NOT NULL);
ALTER TABLE "stuff_ext" ADD CONSTRAINT
"stuff_ext_stuff_ptr_id_5a4ee8edae53404b" FOREIGN KEY ("stuff_ptr_id")
REFERENCES "stuff" ("number") DEFERRABLE INITIALLY DEFERRED;
CREATE SEQUENCE stuff_seq;
And then the function:
CREATE OR REPLACE FUNCTION create_stuff(number integer, title text)
RETURNS integer AS $$
DECLARE
a1 stuff;
a2 stuff_ext;
BEGIN
IF number IS NULL THEN
number := nextval('stuff_seq');
END IF;
a1.number := number;
a1.title := title;
a2.stuff_ptr_id := a1.number;
INSERT INTO stuff VALUES (a1.*);
INSERT INTO stuff_ext VALUES (a2.*);
RETURN number;
END
$$
LANGUAGE plpgsql;
The DB is configured for SERIALIZABLE transaction mode.
Now, if I can the function without passing number, such as:
select create_stuff(NULL,'title');
in 10 forked processes in a loop with a few iterations in each, I get
quite a few SERIALIZATON FAILURE (sqlstate 40001).
If I comment out the "INSERT INTO stuff_ext" line, I don't get any.
How is the second insert causing serialize dependencies...?
The specific error messages vary between
ERROR: could not serialize access due to read/write dependencies among
transactions
DETAIL: Reason code: Canceled on identification as a pivot, during
commit attempt.
HINT: The transaction might succeed if retried.
and
ERROR: could not serialize access due to read/write dependencies among
transactions
DETAIL: Reason code: Canceled on commit attempt with conflict in from
prepared pivot.
HINT: The transaction might succeed if retried.
Thanks!
From | Date | Subject | |
---|---|---|---|
Next Message | Xavier 12 | 2015-06-16 13:55:42 | Re: pg_xlog on a hot_stanby slave |
Previous Message | John Lumby | 2015-06-16 13:21:50 | Re: RegisterBackgroundWorker does not actually start a bg worker process in 9.4.4 |