Re: BUG #17717: Regression in vacuumdb (15 is slower than 10/11 and possible memory issue)

On Thu, Dec 29, 2022 at 03:29:15PM -0500, Tom Lane wrote:
> Nathan Bossart <nathandbossart(at)gmail(dot)com> writes:
>> On Thu, Dec 29, 2022 at 12:22:58PM -0500, Tom Lane wrote:
>>> Justin Pryzby <pryzby(at)telsasoft(dot)com> writes:
>>>> VACUUM (UPDATE_DATABASE_STATS {yes,no,only})
>>>>> VACUUM (DATABASE_STATS {UPDATE,SKIP,ONLY})
>
>> +1 for only introducing one option. IMHO UPDATE_DATABASE_STATS fits a
>> little better since it states the action like most of the other options,
>> but I think both choices are sufficiently clear.
>
> I tried to make a patch along these lines, and soon hit a stumbling
> block: ONLY is a fully-reserved SQL keyword. I don't think this
> syntax is attractive enough to justify requiring people to
> double-quote the option, so we are back to square one. Anybody
> have a different suggestion?

Hm. I thought about using PreventInTransactionBlock() for the function,
but that probably won't work for a few reasons. AFAICT we'd need to
restrict it to only be callable via "SELECT update_database_stats()", which
feels a bit unnatural.

There was some discussion elsewhere [0] about adding a
PROCESS_MAIN_RELATION option or expanding PROCESS_TOAST to simplify
vacuuming the TOAST table directly. If such an option existed, you could
call

VACUUM (PROCESS_MAIN_RELATION FALSE, PROCESS_TOAST FALSE, UPDATE_DATABASE_STATES TRUE) pg_class;

to achieve roughly what we need. I'll admit this is hacky, though.

So, adding both SKIP_DATABASE_STATS and ONLY_DATABASE_STATS might be the
best bet.

[0] https://postgr.es/m/20221215191246.GA252861%40nathanxps13

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com