Search Postgresql Archives

serialization failure why?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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!

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux