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
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 |