Thread: Help with row locks on 7.4 to 8.0 migration

I've just upgraded a 7.4 install to 8.0.3 and we've suddenly run into lock issues that were not present in 7.4.  I'm look for help on this matter, because I'm a little confused.  Downgrading is really not an option at this point,  we really really need the 8.0 features on the DB and a dump/restore takes way to long.

Here is what happens I get a call from are call agents saying the system is slow.  Almost all of there queries are WAITING for a lock.  So I query pg_locks for not granted locks.  Everything is waiting on a single transaction.  OK fine I look at the transaction in question and look at it's query using pg_stat_activity.  I reconize the query, kill the process and boom everything is back to life.  OK this is great because I know this is the problem.  Simple enough but the error message I get is a tad bit confusing.

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. OK this is probably a trigger or something is my next though.  I've searched through the entire schema and don't find this query anywhere or anything that resembles it.  I've got to rewrite these query or what ever requires this query.  Locking the client table is bad because just about every query needs to reference this table.

The statement that is holding everything up is DELETE FROM ONLY demand_sum; INSERT INTO demand_sum (field1...;  This is a simple materialized view that is updated every 5 minutes.

Is the SELECT 1 statement a rewrite of something or is this a statement that the DELETE statement is waiting for (that wouldn't make sense because the delete has the lock granted)?  I am sure that I'm being an absolute idiot over this and missing the obvious.

Please copy me on this.  I've suppresed getting emails from the list.  (I check google groups, but a copy is a tad bit faster)

Thank you

--
Kevin Barnard

"Great Beauty, great strength, and great Riches,
are really and truly of no great Use;
a right Heart exceeds all." -- Benjamin Franklin

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