Thread: Deadlock like scenario
Hi,
I am experience the problem where locks acquired by postgres are NOT getting released.
The use case is something like:-
· There is a table let’s say “A” and the trigger is created on this table let say “A_TRIGGER”.
· The trigger captures the data change happens in table A into table B.
· There would be a huge insert, update, delete on table A, the side effect of it table B also get updated very frequently.
· There is periodic job that runs every midnight to do full vacuum and re-indexing on table B (but NOT on table A).
· It has been found that the queries are hanging and never completes when there are some operation on table A.
Can someone please help me out to resolve this problem. If someone need more information on this then please let me know.
Thanks in advance,
Shridhar
Hi Shridhar, Probably you should read a bit about how PostgreSQL locks and what operation requires which locks. More importantly what happens when these locks are not available. ISTM, your trigger operation tries a bulk operation (INSERT/DELETE/UPDATE) on table B but the nightly reindex acquires a lock because of which your bulk operations get stuck. Have you tried reindexing CONCURRENTLY ? It doesn't solve all problems as Greg rightly points out but could help in this situation. http://www.postgresql.org/docs/8.4/static/explicit-locking.html#TABLE-LOCK-COMPATIBILITY http://www.postgresql.org/docs/8.4/static/sql-reindex.html -- Robins Tharakan On 11/11/2011 08:34 AM, Shridhar Polas wrote: > Hi, > > I am experience the problem where locks acquired by postgres are NOT > getting released. > > The use case is something like:- > > ·There is a table let’s say “A” and the trigger is created on this table > let say “A_TRIGGER”. > > ·The trigger captures the data change happens in table A into table B. > > ·There would be a huge insert, update, delete on table A, the side > effect of it table B also get updated very frequently. > > ·There is periodic job that runs every midnight to do full vacuum and > re-indexing on table B (but NOT on table A). > > ·It has been found that the queries are hanging and never completes when > there are some operation on table A. > > Can someone please help me out to resolve this problem. If someone need > more information on this then please let me know. > > Thanks in advance, > > Shridhar
The use case is something like:-
·There is a table let’s say “A” and the trigger is created on this table
let say “A_TRIGGER”.
·The trigger captures the data change happens in table A into table B.
·There would be a huge insert, update, delete on table A, the side
effect of it table B also get updated very frequently.
AS
$$
BEGIN
UPDATE PARENT SET A=NEW.A;
RETURN NEW;
END;
$$
LANGUAGE PLPGSQL;