Re: Predefined role pg_maintenance for VACUUM, ANALYZE, CHECKPOINT.

On Wed, Oct 27, 2021 at 3:18 AM Bossart, Nathan <bossartn(at)amazon(dot)com> wrote:
>
> On 10/26/21, 2:04 PM, "Jeff Davis" <pgsql(at)j-davis(dot)com> wrote:
> > Should we just add a builtin function pg_checkpoint(), and deprecate
> > the syntax?
>
> That seems reasonable to me.

IMHO, moving away from SQL command "CHECKPOINT" to function
"pg_checkpoint()" isn't nice as the SQL command has been there for a
long time and all the applications or services that were/are being
built around the postgres ecosystem would have to adapt someday to the
new function (if at all we deprecate the command and onboard the
function). This isn't good at all given the CHECKPOINT is one of the
mostly used commands in the apps or services layer. Moreover, if we go
with the function pg_checkpoint(), we might see patches coming in for
pg_vacuum(), pg_reindex(), pg_cluster() and so on.

I suggest having a predefined role (pg_maintenance or
pg_checkpoint(although I'm not sure convinced to have a separate role
just for checkpoint) or some other) and let superuser and the users
with this new predefined role do checkpoint.

Regards,
Bharath Rupireddy.