Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux