From: | Robins Tharakan <robins(dot)tharakan(at)comodo(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Cc: | Shridhar Polas <shridharpolas(at)gmail(dot)com> |
Subject: | Re: Deadlock like scenario |
Date: | 2011-11-11 04:30:15 |
Message-ID: | 4EBCA4D7.1030305@comodo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Karuna Karpe | 2011-11-11 05:10:37 | Re: error log, tablespace, wal files |
Previous Message | Craig Ringer | 2011-11-11 03:06:38 | Re: lost tablespace |