Re: circular wait not triggering deadlock ? - Mailing list pgsql-general

On Thu, Mar 08, 2018 at 01:57:06PM -0500, Tom Lane wrote:
> Justin Pryzby <pryzby@telsasoft.com> writes:
> > Running pg10.2, I have a handful of maintenance jobs run in the middle of the
> > night, which appear to have gotten stuck waiting on each other..
> 
> > ts=# SELECT granted, pid, mode, virtualtransaction, virtualxid FROM pg_locks WHERE virtualxid='22/4317099' ORDER BY
1,2,3;
> >  granted |  pid  |     mode      | virtualtransaction | virtualxid 
> > ---------+-------+---------------+--------------------+------------
> >  f       | 20488 | ShareLock     | 19/28401734        | 22/4317099
> >  t       |  6471 | ExclusiveLock | 22/4317099         | 22/4317099
> 
> PID 20488 is evidently waiting for PID 6471 to finish its transaction.
> What's that one doing?

Um, I thought I had kept track of all two pids but looks not..

query            | SELECT pg_export_snapshot();

So that explains that.
I already killed one proc, but..

ts=# SELECT granted, relation::regclass, pid, mode, virtualtransaction, virtualxid FROM pg_locks WHERE pid=17248 ORDER
BY1;
 
 granted |                relation                 |  pid  |      mode       | virtualtransaction | virtualxid
---------+-----------------------------------------+-------+-----------------+--------------------+------------
 f       | eric_enodeb_cell_metrics                | 17248 | AccessShareLock | 27/1755026         |

ts=# SELECT granted, relation::regclass, pid, mode, virtualtransaction, virtualxid FROM pg_locks WHERE
relation='eric_enodeb_cell_metrics'::regclassORDER BY 1; --pid=17248  virtualxid='22/4317099' ORDER BY 1,2,3;
 
 granted |         relation         |  pid  |           mode           | virtualtransaction | virtualxid
---------+--------------------------+-------+--------------------------+--------------------+------------
 f       | eric_enodeb_cell_metrics | 22961 | AccessShareLock          | 31/1337307         |
 f       | eric_enodeb_cell_metrics | 17248 | AccessShareLock          | 27/1755026         |
 f       | eric_enodeb_cell_metrics | 28357 | AccessShareLock          | 28/1118276         |
 f       | eric_enodeb_cell_metrics | 21846 | AccessExclusiveLock      | 29/830016          |
 f       | eric_enodeb_cell_metrics | 16592 | AccessExclusiveLock      | 18/38156962        |
 f       | eric_enodeb_cell_metrics | 18941 | AccessShareLock          | 32/838769          |
 t       | eric_enodeb_cell_metrics |   530 | ShareUpdateExclusiveLock | 16/38159763        |
(7 rows)

ts=# SELECT pid, state, left(query,199) FROM pg_stat_activity WHERE pid IN(530,17248);
   530 | active | ALTER TABLE eric_enodeb_cell_metrics* ALTER start_time SET STATISTICS 400
 17248 | active | SELECT c.tableoid, c.oid, c.relname, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT
acl,row_n FROM pg_catalog.unnest(coalesce(c.relacl,pg_catalog.acldefault(
 
CASE WHEN c.relkind = 'S' 

ts=# SELECT granted, pid, mode, virtualtransaction, virtualxid FROM pg_locks WHERE
relation='eric_enodeb_cell_20180304'::regclass;
 
 granted |  pid  |           mode           | virtualtransaction | virtualxid
---------+-------+--------------------------+--------------------+------------
 t       | 20488 | ShareUpdateExclusiveLock | 19/28401734        |
 f       |   530 | ShareUpdateExclusiveLock | 16/38159763        |

ts=# SELECT pid, state, left(query,199) FROM pg_stat_activity WHERE pid=20488;
  pid  | state  |                                                                   left
                                   
 

-------+--------+-------------------------------------------------------------------------------------------------------------------------------------------
 20488 | active | CREATE INDEX CONCURRENTLY index_1113966210 ON eric_enodeb_cell_20180304 USING btree (site_id) WITH
(fillfactor='100')TABLESPACE oldindex
 

ts=# SELECT granted, pid, relation::regclass, locktype, mode  virtualxid, virtualxid, virtualtransaction FROM pg_locks
WHEREpid=20488 ORDER BY 1;
 
 granted |  pid  |         relation          |  locktype  |        virtualxid        | virtualxid  | virtualtransaction


---------+-------+---------------------------+------------+--------------------------+-------------+--------------------
 f       | 20488 |                           | virtualxid | ShareLock                | 22/4317099  | 19/28401734

ts=# SELECT granted, pid, relation::regclass, locktype, mode  virtualxid, virtualtransaction FROM pg_locks WHERE
virtualxid='22/4317099'ORDER BY 1;
 
 granted |  pid  | relation |  locktype  |  virtualxid   | virtualtransaction 
---------+-------+----------+------------+---------------+--------------------
 f       | 20488 |          | virtualxid | ShareLock     | 19/28401734
 t       |  6471 |          | virtualxid | ExclusiveLock | 22/4317099
(2 rows)

So...I gather ALTER SET STATS is waiting on pg_dump which is waiting on CREATE
INDEX which is waiting on SELECT pg_export_snapshot(), which I take to mean
that I should avoid running pg_repack until the backup is finished..

ts=# SELECT pid, state, application_name app, left(query,99) FROM pg_stat_activity ORDER BY xact_start LIMIT 9;
  pid  |        state        |    app    |                                                left
                      
 

-------+---------------------+-----------+-----------------------------------------------------------------------------------------------------
  6471 | idle in transaction | psql      | SELECT pg_export_snapshot();
 20488 | active              | pg_repack | CREATE INDEX CONCURRENTLY index_1113966210 ON eric_enodeb_cell_20180304
USINGbtree (site_id) WITH 
 
   530 | active              | psql      | ALTER TABLE eric_enodeb_cell_metrics* ALTER start_time SET STATISTICS 400
 17248 | active              | pg_dump   | SELECT c.tableoid, c.oid, c.relname, (SELECT pg_catalog.array_agg(acl ORDER
BYrow_n) FROM (SELECT 
 

Justin