Re: Deadlock of REINDEX and SELECT queries in PostgresSQL 7.4

On Thursday, September 29, 2011 8:49:07 am Tendulker, Shivanand G Prabhu (SSTL)
wrote:
> Hello
>
> We are facing a deadlock kind of issue in PostgresSQL 7.4
>
> We have 2 databases with 3 tables each. DB contains about 250 records. We
> observed deadlock when 2 different clients are performing REINDEX and
> SELECT start their operations near simultaneously.
>
> Client 1 performs following operations in a tight loop:-
> VACCUM, ANALYZE of each table, REINDEX of each table'
>
> Client 2 performs SELECT in one of the table in a tight loop.
>
> Upon looking at the postgres locks, it seems like all the locks are granted
> to 'REINDEX' operation and SELECT is waiting. REINDEX never returns in
> this scenario. This problem occurs when Client1 and Client 2 are running
> simultaneously. Both in a tight loop. Once this deadlock is reached all
> the subsequent SELECT, RENDEX operations go into 'waiting' mode.
>
> Is this a known issue? Is the REINDEX and SELECT transactions directed at
> postgres at same time not a valid combination?

Yes, see here:
http://www.postgresql.org/docs/7.4/static/explicit-locking.html

>
> Please provide help in resolving this issue.
>
> Thanks and Regards
> Shiv

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com