Re: RI

Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com> writes:
> Interesting question. When modifying the parent record, Oracle RDBMS
> locks the entire child table in shared mode, unless an index on the
> child table is present. What does Postgres do in that situation? Can
> Postgres somehow locate the corresponding child record(s) without an
> index?

Sure ... it'll just seqscan the child table. Obviously, this will be
horridly slow --- but as stated, if it's something you very rarely do,
you might not want to pay the overhead of an extra index on the child
table in order to make it faster. It's a tradeoff, you pays your money
and you takes your choice.

> This feature of Oracle RDBMS was a source of countless deadlocks
> during my 20+ years as an Oracle professional. When I come to think of
> it, Postgres probably does the same thing to prevent an update of the
> child table while the update of the parent table is going on. I confess
> not having time to try. Can you elaborate a bit on that?

No, we don't lock the whole table. The way the anti-race-condition
interlock works is that an insert into the child table attempts to
share-lock the referenced (parent) row. If successful, that prevents a
delete of the referenced row until the child insert has committed.
(After it's committed, no lock is needed because any attempted delete of
the parent row will be able to see that there's a child row.) You can
get some deadlocks that way too, of course, but they're different from
what you're saying Oracle does.

regards, tom lane