MIME-Version: 1.0
References: 
 <CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw@mail.gmail.com>
 <CALj2ACXcmYsoJXTYtuGhkxNVXV5jtTNfL-vB+sQq-jE9__N5AA@mail.gmail.com>
In-Reply-To: 
 <CALj2ACXcmYsoJXTYtuGhkxNVXV5jtTNfL-vB+sQq-jE9__N5AA@mail.gmail.com>
From: Sami Imseih <samimseih@gmail.com>
Date: Sat, 28 Mar 2026 12:53:52 -0500
Message-ID: 
 <CAA5RZ0surzz41exF5QwecuFU8NqZVRR5aDnC6MObeEcsXhfu4Q@mail.gmail.com>
Subject: Re: Add pg_stat_autovacuum_priority
To: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>,
 satyanarlapuram@gmail.com
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAA5RZ0surzz41exF5QwecuFU8NqZVRR5aDnC6MObeEcsXhfu4Q%40mail.gmail.com>
Precedence: bulk

Hi,

Thank you all for the review and comments!

> 1. Please add CFI in the function pg_stat_get_autovacuum_priority, as the list of tables can be very long
good catch. Will add.


> 3. Please add tests for tables with autovacuum = off
Yes, I will add this test as well.


> 4. Is the view intended to be exposed to PUBLIC without any ACL restrictions?

> 2/ Do we need to revoke permissions on pg_stat_get_autovacuum_priority
> for all and grant them to pg_monitor or similar? Especially since this
> function loops over all the relations in a database, we may not want
> everyone to be able to do this.

I think you're correct there. While the data is not sensitive, it
should have more controlled usage. It's only taking an AccessShareLock,
but you would not want anyone to be able to run this since it's
doing real computation. I think requiring pg_read_all_stats
is a good idea. Will do.


> 5. Catalog version number needs to be increased
This will be left to the committer at the time.

> 2. Should we add filtering? The current approach pg_stat_get_autovacuum_priority
> does a full catalog scan without any filters and can be expensive.

> Can we have the per-relation prioritization computation function in C
> and provide a per-database computation function as a SQL function over
> this per-relation function in system_functions.sql?

Yes, perhaps we should do this. So we can have a function called
pg_stat_get_autovacuum_priority() that either takes a NULL or an OID
to either return all the tables or just a single table.
This is a similar usage pattern as pg_stat_get_subscription or
pg_stat_get_activity.

pg_stat_autovacuum_priority will be a view that wraps around the NULL
variant of the function.

The case where the OID is passed we just do a SearchSysCache1(RELOID,...)
whereas the other case will do the full catalog scan.

What do you think?

--
Sami