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