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