Hi,
While testing another patch [1], I noticed that REPACK is blocked when a
temporary table is locked in another session. It also turns out that the
same behaviour occurs with VACUUM FULL and CLUSTER:
== session 1 ==
$ psql postgres
psql (19devel)
Type "help" for help.
postgres=# CREATE TEMPORARY TABLE tmp (id int);
CREATE TABLE
postgres=# BEGIN;
LOCK TABLE tmp IN SHARE MODE;
BEGIN
LOCK TABLE
postgres=*#
== session 2 ==
$ psql postgres
psql (19devel)
Type "help" for help.
postgres=# REPACK;
^CCancel request sent
ERROR: canceling statement due to user request
CONTEXT: waiting for AccessExclusiveLock on relation 38458 of database 5
postgres=# VACUUM FULL;
^CCancel request sent
ERROR: canceling statement due to user request
CONTEXT: waiting for AccessExclusiveLock on relation 38458 of database 5
Skipping temporary relations in get_tables_to_repack() and
get_all_vacuum_rels() before they're appended to the list seems to do
the trick -- see attached draft.
I can reproduce the same behaviour with CLUSTER and VACUUM FULL in
PG14-PG18. I took a quick look at the code in PG17 and PG18 and the fix
appears to be straightforward, but before I start working on it, I'd
like to hear your thoughts. Is it worth the effort?
Best, Jim
1 - https://www.postgresql.org/message-id/13637.1774342137%40localhost
