From: Robins Tharakan <robins.tharakan@comodo.com>
To: pgsql-admin@postgresql.org
Cc: Shridhar Polas <shridharpolas@gmail.com>
Subject: Re: Deadlock like scenario
Date: Fri, 11 Nov 2011 10:00:15 +0530
Message-ID: <4EBCA4D7.1030305@comodo.com> (raw)
In-Reply-To: <004b01cca01e$ae297f80$0a7c7e80$@gmail.com>
References: <004b01cca01e$ae297f80$0a7c7e80$@gmail.com>
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-COMPATIBILITYhttp://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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: robins.tharakan@comodo.com, shridharpolas@gmail.com
Subject: Re: Deadlock like scenario
In-Reply-To: <4EBCA4D7.1030305@comodo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox