On Thu, Mar 08, 2018 at 01:57:06PM -0500, Tom Lane wrote: > Justin Pryzby <pryzby@xxxxxxxxxxxxx> 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 BY 1; 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'::regclass ORDER 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 WHERE pid=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 USING btree (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 BY row_n) FROM (SELECT Justin
