Re: using deferred initially deferred to solve foreign key checking issues

Philip de Nier <philip(dot)denier(at)rd(dot)bbc(dot)co(dot)uk> writes:
> The solutions I could find in the mailing lists were either to upgrade to
> version 8.1 which uses "SELECT ... FOR SHARE" (I'm currently using 8.0),
> stop using foreign keys or add "DEFERRABLE INITIALLY DEFERRED" to the
> constraints. For now I'd prefer to use the last option.

> Does using "DEFERRABLE INITIALLY DEFERRED" completely solve the problem of
> transactions waiting for each other to release locks resulting from "SELECT
> ... FOR UPDATE"?

It doesn't eliminate the problem, but it does narrow the window in which
the lock is held by quite a bit, by postponing the FK checks until just
before transaction commit.

> How are the deferred foreign key constraints checked - are they checked one
> at a time?

Yeah.

regards, tom lane