Re: Help with row locks on 7.4 to 8.0 migration - Mailing list pgsql-general

From Tom Lane
Subject Re: Help with row locks on 7.4 to 8.0 migration
Date
Msg-id 21080.1126044805@sss.pgh.pa.us
Whole thread Raw
In response to Help with row locks on 7.4 to 8.0 migration  (Kevin Barnard <kevin.barnard@gmail.com>)
List pgsql-general
Kevin Barnard <kevin.barnard@gmail.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

pgsql-general by date:

Previous
From: "Jim C. Nasby"
Date:
Subject: Re: Shared disk storage
Next
From: "Jim C. Nasby"
Date:
Subject: Re: How to write jobs in postgresql