Re: BUG #10888: application is getting hanged in the poll() function of libpq.so.

I am not sure if it is a postgres issue or not, so need your help in that.
I have checked pg_stat and pg_lock, (see the output below). But is is not showing any conflicts.
--------------------------------------------------------------------------------------------------------------------------------------------------------
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtra
nsaction | pid | mode | granted
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+-----------
---------+-------+------------------+---------
transactionid | | | | | | 2650178 | | | | 2/2
| 20714 | ExclusiveLock | t
relation | 16384 | 16501 | | | | | | | | 2/2
| 20714 | RowExclusiveLock | t
virtualxid | | | | | 4/41 | | | | | 4/41
| 10626 | ExclusiveLock | t
relation | 16384 | 16400 | | | | | | | | 2/2
| 20714 | RowExclusiveLock | t
relation | 16384 | 11000 | | | | | | | | 4/41
| 10626 | AccessShareLock | t
relation | 16384 | 16400 | | | | | | | | 3/2
| 20819 | RowExclusiveLock | t
virtualxid | | | | | 2/2 | | | | | 2/2
| 20714 | ExclusiveLock | t
transactionid | | | | | | 2650179 | | | | 3/2
| 20819 | ExclusiveLock | t
virtualxid | | | | | 3/2 | | | | | 3/2
| 20819 | ExclusiveLock | t
relation | 16384 | 16501 | | | | | | | | 3/2
| 20819 | RowExclusiveLock | t
(10 rows)

---------------------------------------------------------------------------------------------------------------------

fm_db_CollectCCN=# select * from pg_stat_activity;
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
DEBUG: CommitTransactionCommand
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
datid | datname | procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port
| backend_start | xact_start | query_start | waiting |
current_query

-------+------------------+---------+----------+---------+------------------+-------------+-----------------+-------------
+-------------------------------+-------------------------------+-------------------------------+---------+---------------
--------------------------------------------------------------------------------------------------------------------------
----------------------------------------------
16384 | fm_db_CollectCCN | 20714 | 16391 | mmsuper | | ::1 | | 19540
| 2014-07-07 19:10:09.295565+02 | 2014-07-07 19:10:09.311701+02 | 2014-07-07 19:10:09.311701+02 | f | INSERT INTO ev
entLogEntry (object, method, bgwUser, time, realUser, host, application, tableIndex ) VALUES (E'Server', E'Start', E'bgw'
, E'20140512122404', NULL, NULL, NULL, 539 )
16384 | fm_db_CollectCCN | 20819 | 16391 | mmsuper | | ::1 | | 19602
| 2014-07-07 19:10:45.18688+02 | 2014-07-07 19:10:45.202903+02 | 2014-07-07 19:10:45.202903+02 | f | INSERT INTO ev
entLogEntry (object, method, bgwUser, time, realUser, host, application, tableIndex ) VALUES (E'Server', E'Start', E'bgw'
, E'20140512122404', NULL, NULL, NULL, 539 )
16384 | fm_db_CollectCCN | 10626 | 16391 | mmsuper | psql.bin | ::1 | | 29388
| 2014-07-07 19:29:13.560262+02 | 2014-07-07 19:32:05.993869+02 | 2014-07-07 19:32:05.993869+02 | f | select * from
pg_stat_activity;
(3 rows)

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Monday, July 07, 2014 10:57 PM
To: Mitu Verma
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #10888: application is getting hanged in the poll() function of libpq.so.

Mitu Verma <mitu(dot)verma(at)ericsson(dot)com> writes:
> I am running a single query at this moment.

> INSERT INTO eventLogEntry (object, method, bgwUser, time, realUser,
> host, application, tableIndex ) VALUES (E'Server', E'Start', E'bgw',
> E'20140512122404', NULL, NULL, NULL, 539 );

> So not able to understand whats wrong with the database that it is unable to process this simple insert query.
> At the same time if drop the index and recreate the table then this issue is resolved. But obviously this is not a feasible solution in live environment. Plz see if you can provide some help.

>> pstack 16950
> #0 0x00000000006dc1da in hash_search ()
> #1 0x000000000060e430 in LockAcquireExtended ()
> #2 0x000000000060c46e in XactLockTableWait ()
> #3 0x0000000000476bc6 in _bt_doinsert ()
> #4 0x0000000000479882 in btinsert ()

It'd appear that the INSERT is blocked because it's waiting to see if some other transaction that has inserted a conflicting key value into a unique index will commit or not. You could probably learn more about what's going on by looking into pg_locks and pg_stat_activity. But at this point I see no reason whatsoever to think there's a Postgres bug involved.

regards, tom lane