Message-ID: <4EBCA4D7.1030305@comodo.com>
Date: Fri, 11 Nov 2011 10:00:15 +0530
From: Robins Tharakan <robins.tharakan@comodo.com>
Organization: Comodo
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.23) Gecko/20110922 Thunderbird/3.1.15
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
CC: Shridhar Polas <shridharpolas@gmail.com>
Subject: Re: Deadlock like scenario
References: <004b01cca01e$ae297f80$0a7c7e80$@gmail.com>
In-Reply-To: <004b01cca01e$ae297f80$0a7c7e80$@gmail.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

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