Thread: 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