On Thu, Mar 08, 2018 at 01:57:06PM -0500, Tom Lane wrote:
> Justin Pryzby <[email protected]> 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
