From: | Marcin Barczynski <mbarczynski(at)starfishstorage(dot)com> |
---|---|
To: | Ninad Shah <nshah(dot)postgres(at)gmail(dot)com> |
Cc: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Why does VACUUM FULL pg_class sometimes wait for ShareLock on another transaction after getting AccessExclusiveLock on pg_class? |
Date: | 2021-07-26 15:41:44 |
Message-ID: | CAOhG4wetcDMXpWXx2=TMycTQTGGjKUDVa7KmK7rNt89aNDGfnA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Jul 23, 2021 at 4:46 PM Ninad Shah <nshah(dot)postgres(at)gmail(dot)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 <
> 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
>>
>
--
*Marcin Barczyński* | *Senior Software Engineer * |
mbarczynski(at)starfishstorage(dot)com | http://www.starfishstorage.com
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2021-07-26 15:43:03 | Re: Recursive Parent-Child Function Bottom Up |
Previous Message | Avi Weinberg | 2021-07-26 15:19:34 | Recursive Parent-Child Function Bottom Up |