Re: Inconsistency in vacuum behavior - Mailing list pgsql-hackers

From Nikita Malakhov
Subject Re: Inconsistency in vacuum behavior
Date
Msg-id CAN-LCVMO9Hi1FULxOP1vb+pLjRF5fu0ZgobQ73X0mxg=BRRRvQ@mail.gmail.com
Whole thread Raw
In response to Inconsistency in vacuum behavior  (Alexander Pyhalov <a.pyhalov@postgrespro.ru>)
Responses Re: Inconsistency in vacuum behavior
List pgsql-hackers
Hi!

I've checked this expand_vacuum_rel() and made a quick fix for this.Here's the result of the test:

postgres@postgres=# set role regress_vacuum_conflict;
SET
Time: 0.369 ms
postgres@postgres=> vacuum vacuum_tab;
WARNING:  permission denied to vacuum "vacuum_tab", skipping it
WARNING:  permission denied to vacuum "vacuum_tab_1", skipping it
WARNING:  permission denied to vacuum "vacuum_tab_2", skipping it
VACUUM
Time: 0.936 ms
postgres@postgres=>

Looks like it's a subject for a patch.

On Mon, Jan 16, 2023 at 11:18 AM Alexander Pyhalov <a.pyhalov@postgrespro.ru> wrote:
Hi.

We've run regress isolation tests on partitioned tables and found
interesting VACUUM behavior. I'm not sure, if it's intended.

In the following example, partitioned tables and regular tables behave
differently:

CREATE TABLE vacuum_tab (a int) PARTITION BY HASH (a);
CREATE TABLE vacuum_tab_1 PARTITION OF vacuum_tab FOR VALUES WITH
(MODULUS 2, REMAINDER 0);
CREATE TABLE vacuum_tab_2 PARTITION OF vacuum_tab FOR VALUES WITH
(MODULUS 2, REMAINDER 1);
CREATE ROLE regress_vacuum_conflict;

In the first session:

begin;
  LOCK vacuum_tab IN SHARE UPDATE EXCLUSIVE MODE;

In the second:
SET ROLE regress_vacuum_conflict;
  VACUUM vacuum_tab;
  WARNING:  permission denied to vacuum "vacuum_tab", skipping it <----
hangs here, trying to lock vacuum_tab_1

In non-partitioned case second session exits after emitting warning. In
partitioned case, it hangs, trying to get locks.
This is due to the fact that in expand_vacuum_rel() we skip parent table
if vacuum_is_permitted_for_relation(), but don't perform such check for
its child.
The check will be performed later in vacuum_rel(), but after
vacuum_open_relation(), which leads to hang in the second session.

Is it intended? Why don't we perform vacuum_is_permitted_for_relation()
check for inheritors in expand_vacuum_rel()?

--
Best regards,
Alexander Pyhalov,
Postgres Professional




--
Regards,
Nikita Malakhov
Postgres Professional 

pgsql-hackers by date:

Previous
From: Jim Jones
Date:
Subject: Re: Make EXPLAIN generate a generic plan for a parameterized query
Next
From: Peter Eisentraut
Date:
Subject: Re: Generating code for query jumbling through gen_node_support.pl