Re: serialization failure why?

Filipe Pina <filipe.pina@xxxxxxxxxxxxx> · Wed, 17 Jun 2015 11:30:20 +0001

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@xxxxxxxxxxxxxxxxx> wrote:

On Tue, 16 Jun 2015 13:33:12 +0001
Filipe Pina <filipe.pina@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxx>