Re: serialization failure why?

Indeed Bill, if drop the foreign key constraint on stuff_ext table
there are no failures at all...

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(at)potentialtech(dot)com>
wrote:
> On Tue, 16 Jun 2015 13:33:12 +0001
> Filipe Pina <filipe(dot)pina(at)impactzero(dot)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(at)potentialtech(dot)com>