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.=
=20
I have checked pg_stat and pg_lock, (see the output below). But is is not s=
howing any conflicts.
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--
  locktype    | database | relation | page | tuple | virtualxid | transacti=
onid | classid | objid | objsubid | virtualtra
nsaction |  pid  |       mode       | granted
---------------+----------+----------+------+-------+------------+---------=
------+---------+-------+----------+-----------
---------+-------+------------------+---------
 transactionid |          |          |      |       |            |       26=
50178 |         |       |          | 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 |          |          |      |       |            |       26=
50179 |         |       |          | 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=3D# 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 |
                                                                      curre=
nt_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-0=
7 19:10:09.311701+02 | f       | INSERT INTO ev
entLogEntry (object, method, bgwUser, time, realUser, host, application, ta=
bleIndex  ) 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-0=
7 19:10:45.202903+02 | f       | INSERT INTO ev
entLogEntry (object, method, bgwUser, time, realUser, host, application, ta=
bleIndex  ) 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-0=
7 19:32:05.993869+02 | f       | select * from
pg_stat_activity;
(3 rows)

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

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

> INSERT INTO eventLogEntry (object, method, bgwUser, time, realUser,=20
> host, application, tableIndex  ) VALUES (E'Server', E'Start', E'bgw',=20
> 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 environ=
ment. 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 i=
ndex 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