On Fri, Jul 23, 2021 at 4:46 PM Ninad Shah <nshah.postgres@xxxxxxxxx> 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 ShahOn Fri, 23 Jul 2021 at 11:39, Marcin Barczynski <mbarczynski@xxxxxxxxxxxxxxxxxxx> wrote:On Thu, Jul 22, 2021 at 5:08 PM Marcin Barczynski <mbarczynski@xxxxxxxxxxxxxxxxxxx> wrote:On Thu, Jul 22, 2021 at 3:51 PM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> 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 |![]()
mbarczynski@xxxxxxxxxxxxxxxxxxx | http://www.starfishstorage.com
