Indeed Bill, if drop the foreign key constraint on stuff_ext table I get 0 failures...
But, since I can't remove the foreign key, how can I tell postgres that he can "trust" it then?
Because it's obvious (for the code reader at least), that the other transaction will not be responsible for creating the foreign record, it's that very same transaction that creates it (the previous insert). If the first had failed (because it already existed or due to some other failure), the second wouldn't even be executed, so the second shouldn't fail for serialization on the FK at least..
Is there something that can be annotated or passed in the insert to workaround this? I really need to reduce the retries I'm performing and this is one of the common cases I have (in different functions, FK serialization failures that shouldn't be a problem)..
On Qua, Jun 17, 2015 at 2:45 , Bill Moran <wmoran@potentialtech.com> wrote:
On Tue, 16 Jun 2015 13:33:12 +0001 Filipe Pina <filipe.pina@impactzero.pt> wrote:
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...?
I'm not sure this is correct, but I have a theory. Essentially, PostgreSQL can't be sure that the foreign key will be valid if the other transaction rolls back. i.e., what if the foreign key is valid becuase the other transaction created the matching row, and that other transaction then rolls back? In other isolation modes, it can just wait for the appropriate lock to free up, then see what happens. But in serializable mode it hits a condition where it can't ensure serializability.
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!
--
Bill Moran <wmoran@potentialtech.com>