Re: Help with row locks on 7.4 to 8.0 migration

Kevin Barnard <kevin(dot)barnard(at)gmail(dot)com> writes:
> FATAL: terminating connection due to administrator command
> CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."client" x WHERE
> "division" = $1 FOR UPDATE OF x"

> That's what happens when I kill the SQL that was locking everything. The
> only problem is that's not the SQL statement that was being run.

It's a foreign-key trigger, and your problem is probably a quasi
deadlock on a foreign key's master row --- someone who has the row
locked is waiting for someone else who is just sitting on an open
transaction, and then everyone else trying to insert the same FK
value stacks up behind that lock.

I'm not sure why you are seeing this more in 8.0 than 7.4, though
possibly the different timing for firing AFTER triggers has something
to do with it. Do you use stored procedures to do your updates? If so,
8.0 will fire the FK triggers sooner (within the procedure rather than
after it exits), thus holding the locks longer, thus possibly causing
the problem. Perhaps making the problematic FKs DEFERRED would help.

FWIW, 8.1 should be a lot better on this because it will use shared row
locks for foreign key references.

regards, tom lane