From: | Kevin Barnard <kevin(dot)barnard(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Help with row locks on 7.4 to 8.0 migration |
Date: | 2005-09-06 21:37:56 |
Message-ID: | b068057c0509061437bb5e913@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ian Harding | 2005-09-06 21:51:42 | Re: SLOOOOOOOW |
Previous Message | Guy Rouillier | 2005-09-06 21:29:31 | Re: How to write jobs in postgresql |