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

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 <
mbarczynski(at)starfishstorage(dot)com> wrote:

> On Thu, Jul 22, 2021 at 5:08 PM Marcin Barczynski <
> mbarczynski(at)starfishstorage(dot)com> wrote:
>
>> On Thu, Jul 22, 2021 at 3:51 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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
>