On Fri, Jul 23, 2021 at 4:46 PM Ninad Shah <nshah.postg...@gmail.com> wrote:
> Question 1 : What's a need to perform a VACUUM FULL operation on pg_class? > Workload requires many temp tables which cause huge bloat on pg_class - it sometimes has more than 2 GB. > 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) > That's my question. I expected that once VACUUM FULL gets exclusive lock, it can finish. But that's not the case with pg_class. > > 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 >> > -- *Marcin Barczyński* | *Senior Software Engineer * | mbarczyn...@starfishstorage.com | http://www.starfishstorage.com
