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