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 <wmo...@potentialtech.com>
wrote:
On Tue, 16 Jun 2015 13:33:12 +0001
Filipe Pina <filipe.p...@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 <wmo...@potentialtech.com>
