Question 1 : What's a need to perform a VACUUM FULL operation on pg_class? Question 2 : Ideally, a VACUUM FULL operation seeks an exclusive lock on a table, while I can see it waiting for a shared lock here. Why? (Not asking you)
Additionally, The situation you have described should be termed a "deadlock". Do you see any transaction getting rolled back? Regards, Ninad Shah On Fri, 23 Jul 2021 at 11:39, Marcin Barczynski < mbarczyn...@starfishstorage.com> wrote: > On Thu, Jul 22, 2021 at 5:08 PM Marcin Barczynski < > mbarczyn...@starfishstorage.com> wrote: > >> On Thu, Jul 22, 2021 at 3:51 PM Laurenz Albe <laurenz.a...@cybertec.at> >> wrote: >> > >> > On Thu, 2021-07-22 at 12:55 +0200, Marcin Barczynski wrote: >> > > 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: >> > >> > There must have been something else using "pg_class", since the above >> > won't take any permament locks on "pg_class", nor should it block >> VACUUM. >> >> >> Thanks for your reply. I dugged a bit deeper, and it turned out that >> VACUUM FULL hung in heapam_index_build_range_scan. >> It's PostgreSQL 13.3. Comments around heapam_handler.c:1482: >> > > What's more, running VACUUM FULL pg_class sometimes causes a deadlock with > transactions using temp tables. For example: > > DETAIL: Process 6703 waits for ShareLock on transaction 108850229; > blocked by process 6591. > Process 6591 waits for AccessShareLock on relation 1259 of > database 16517; blocked by process 6703. > Process 6703: VACUUM (FULL, VERBOSE, ANALYZE) pg_class > Process 6591: SELECT * FROM stored_procedure() > > Isn't it a bug? > Is there any way to safely run VACUUM FULL pg_class? > My workload involves lots of temp tables, and I need to get rid of the > bloat regularly. > > -- > Regards, > Marcin Barczynski >
