Why does VACUUM FULL pg_class sometimes wait for ShareLock on another transaction after getting AccessExclusiveLock on pg_class?

From: Marcin Barczynski <mbarczynski(at)starfishstorage(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Why does VACUUM FULL pg_class sometimes wait for ShareLock on another transaction after getting AccessExclusiveLock on pg_class?
Date: 2021-07-22 10:55:58
Message-ID: CAOhG4wdr+qxugYwj7kQ5Xi+wS5rSDkR_t+YbQBhyTo1pk1gujQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

There was a long-running transaction consisting of two queries:

CREATE TEMPORARY TABLE abc AS SELECT * FROM def_view;
INSERT INTO xyz_table SELECT * FROM abc;

When I ran VACUUM FULL pg_class, it waited for ShareLock on that
transaction:

postgres=# select * from pg_locks where pid = 29563;
locktype | database | relation | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction | pid |
mode | granted | fastpath
---------------+----------+------------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------
virtualxid | | | | | 414/5739 |
| | | | 414/5739 | 29563 |
ExclusiveLock | t | t
relation | 16517 | 1259 | | | |
| | | | 414/5739 | 29563 |
ShareLock | t | f
relation | 16517 | 1259 | | | |
| | | | 414/5739 | 29563 |
AccessExclusiveLock | t | f
relation | 16517 | 1325035831 | | | |
| | | | 414/5739 | 29563 |
AccessExclusiveLock | t | f
transactionid | | | | | |
113559773 | | | | 414/5739 | 29563 |
ExclusiveLock | t | f
transactionid | | | | | |
113551212 | | | | 414/5739 | 29563 |
ShareLock | f | f
relation | 16517 | 2662 | | | |
| | | | 414/5739 | 29563 |
AccessExclusiveLock | t | f
(7 rows)

Why?
What's more interesting is that from time to time vacuum succeeded despite
the fact that the long-running transaction was still running.

I tried to reproduce it by simulating the long-running transaction, and
running VACUUM FULL pg_class in another transaction, but to no avail:

psql #1:
q=# CREATE TABLE demo AS SELECT generate_series(1, 1000);
SELECT 1000
q=# CREATE VIEW demo_view AS SELECT * FROM demo;
CREATE VIEW
q=# CREATE TABLE result (val BIGINT);
CREATE TABLE

q=# BEGIN;
BEGIN
q=*# CREATE TEMP TABLE abc AS SELECT * FROM demo_view;
SELECT 1000
q=*# INSERT INTO result SELECT * FROM abc;
INSERT 0 1000
q=*#

psql #2:
q=# VACUUM FULL pg_class;
VACUUM

--
Regards,
Marcin Barczynski

Responses

Browse pgsql-general by date

  From Date Subject
Next Message WR 2021-07-22 11:41:55 Re: Obsolete or dead serverconnections after reboot
Previous Message Ganesh Korde 2021-07-22 10:27:46 Re: Doubt on pgbouncer