INSERT locking order
In an INSERT, are the rows guaranteed to be inserted in the order that
the source query returns its rows, for locking purposes?
For example, if have
CREATE TABLE foo (
foo_id INTEGER,
PRIMARY KEY (foo_id)
);
and I run two concurrent copies of
INSERT INTO foo (SELECT n FROM ... ORDER BY n);
is there any guarantee that I'll get a unique constraint violation
rather than a deadlock?
-M-
Matthew Woodcraft <matthew@woodcraft.me.uk> writes:
In an INSERT, are the rows guaranteed to be inserted in the order that
the source query returns its rows, for locking purposes?
I dunno that we "guarantee" that, but it's hard to see why the
code would behave any differently, at present.
and I run two concurrent copies of
INSERT INTO foo (SELECT n FROM ... ORDER BY n);
is there any guarantee that I'll get a unique constraint violation
rather than a deadlock?
Well, the issue there is not about the physical insertion order
but the order in which the uniqueness checks happen. I think
you'd be all right with a traditional-style PG index, but maybe
not with a deferrable unique constraint.
regards, tom lane