pg_maintenance for VACUUM, ANALYZE,
CHECKPOINT.
---
doc/src/sgml/ref/analyze.sgml | 14 +++---
doc/src/sgml/ref/checkpoint.sgml | 3 ++-
doc/src/sgml/ref/vacuum.sgml | 14 +++---
src/backend/commands/vacuum.c | 12 +++-
src/backend/tcop/utility.c| 5
Greetings,
* Andres Freund (and...@anarazel.de) wrote:
> On 2021-11-05 08:42:58 -0400, Robert Haas wrote:
> > On Thu, Nov 4, 2021 at 7:38 PM Jeff Davis wrote:
> > > It seems like this specific approach has been mostly shot down already.
> > > But out of curiosity, are you intending to run CHECKP
Greetings,
* Andres Freund (and...@anarazel.de) wrote:
> On 2021-11-05 08:54:37 -0400, Robert Haas wrote:
> > On Thu, Nov 4, 2021 at 6:46 PM Andres Freund wrote:
> > > What about extending GRANT to allow to grant rights on commands? Yes,
> > > it'd be
> > > a bit of work to make that work in the
Hi,
On 2021-11-08 12:23:18 -0500, Stephen Frost wrote:
> If we're actually worried about catalog corruption (and, frankly, I've
> got some serious doubts that jumping in and running CHECKPOINT; by hand
> is a great idea if there's such active corruption)
I've been there when recovering from corru
Greetings,
* Alvaro Herrera (alvhe...@alvh.no-ip.org) wrote:
> On 2021-Nov-04, Jeff Davis wrote:
> > But I don't see it generalizing to a lot of commands, either. I looked
> > at the list, and it's taking some creativity to think of more than a
> > couple other commands where it makes sense. Maybe
On 2021-Nov-08, Stephen Frost wrote:
> * Alvaro Herrera (alvhe...@alvh.no-ip.org) wrote:
> > That said, if the list is short, then additional predefined roles seem
> > preferrable to having a ton of infrastructure code that might be much
> > more clutter than what seems a short list of additional
Greetings,
* Andres Freund (and...@anarazel.de) wrote:
> On 2021-11-08 12:23:18 -0500, Stephen Frost wrote:
> > though I continue to feel like the function based approach is better.
>
> I think it's a somewhat ugly hack.
I suppose we'll just have to disagree on that. :)
I don't feel as strongly
Greetings,
* Alvaro Herrera (alvhe...@alvh.no-ip.org) wrote:
> On 2021-Nov-08, Stephen Frost wrote:
>
> > * Alvaro Herrera (alvhe...@alvh.no-ip.org) wrote:
>
> > > That said, if the list is short, then additional predefined roles seem
> > > preferrable to having a ton of infrastructure code that
Greetings,
* Isaac Morland (isaac.morl...@gmail.com) wrote:
> On Tue, 2 Nov 2021 at 19:00, Vik Fearing wrote:
> > On 11/2/21 11:14 PM, Vik Fearing wrote:
> >
> > > This would be nice, but there is nothing to hang our hat on:
> > >
> > > GRANT CHECKPOINT TO username;
> >
> > Thinking about thi
On Mon, 2021-11-08 at 12:47 -0500, Stephen Frost wrote:
>
> I don't feel as strongly as others apparently do on this point
> though,
> and I'd rather have non-superusers able to run CHECKPOINT *somehow*
> than not, so if the others feel like a predefined role is a better
> approach then I'm alrigh
Greetings,
* Jeff Davis (pg...@j-davis.com) wrote:
> On Mon, 2021-11-08 at 12:47 -0500, Stephen Frost wrote:
> >
> > I don't feel as strongly as others apparently do on this point
> > though,
> > and I'd rather have non-superusers able to run CHECKPOINT *somehow*
> > than not, so if the others fe
On Sun, Oct 24, 2021 at 3:15 AM Jeff Davis wrote:
>
> Add new predefined role pg_maintenance, which can issue VACUUM,
> ANALYZE, CHECKPOINT.
>
> Patch attached.
At this point, the idea of having a new role for maintenance work
looks good. With this patch and Mark Dilger's patch introducing a
bunc
On Sun, Oct 24, 2021 at 7:49 AM Bharath Rupireddy <
bharath.rupireddyforpostg...@gmail.com> wrote:
> On Sun, Oct 24, 2021 at 3:15 AM Jeff Davis wrote:
> >
> > Add new predefined role pg_maintenance, which can issue VACUUM,
> > ANALYZE, CHECKPOINT.
>
>
> Are there any other database activities tha
On Sun, 2021-10-24 at 20:19 +0530, Bharath Rupireddy wrote:
> At this point, the idea of having a new role for maintenance work
> looks good. With this patch and Mark Dilger's patch introducing a
> bunch of new predefined roles, one concern is that we might reach to
> a
> state where we will have p
On 10/24/21, 10:20 AM, "Jeff Davis" wrote:
> On Sun, 2021-10-24 at 20:19 +0530, Bharath Rupireddy wrote:
>> Are there any other database activities that fall under the
>> "maintenance" category? How about CLUSTER, REINDEX? I didn't check
>> the
>> code for their permissions.
>
> I looked around an
On Sun, 2021-10-24 at 21:32 +, Bossart, Nathan wrote:
> My initial reaction was that members of pg_maintenance should be able
> to do all of these things (VACUUM, ANALYZE, CLUSTER, REINDEX, and
> CHECKPOINT).
What about REFRESH MATERIALIZED VIEW? That seems more specific to a
workload, but it'
On 10/24/21, 11:13 PM, "Jeff Davis" wrote:
> On Sun, 2021-10-24 at 21:32 +, Bossart, Nathan wrote:
>> My initial reaction was that members of pg_maintenance should be able
>> to do all of these things (VACUUM, ANALYZE, CLUSTER, REINDEX, and
>> CHECKPOINT).
>
> What about REFRESH MATERIALIZED V
Greetings,
* Bharath Rupireddy (bharath.rupireddyforpostg...@gmail.com) wrote:
> On Sun, Oct 24, 2021 at 3:15 AM Jeff Davis wrote:
> > Add new predefined role pg_maintenance, which can issue VACUUM,
> > ANALYZE, CHECKPOINT.
> >
> > Patch attached.
>
> At this point, the idea of having a new role
Stephen Frost writes:
> Independent of other things, getting to the point where everything can
> be done in the database without the need for superuser is absolutely a
> good goal to be striving for, not something to be avoiding.
> I don't think that makes superuser become 'dummy', but perhaps the
Greetings,
* Jeff Davis (pg...@j-davis.com) wrote:
> On Sun, 2021-10-24 at 21:32 +, Bossart, Nathan wrote:
> > My initial reaction was that members of pg_maintenance should be able
> > to do all of these things (VACUUM, ANALYZE, CLUSTER, REINDEX, and
> > CHECKPOINT).
>
> What about REFRESH MA
Greetings,
* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Stephen Frost writes:
> > Independent of other things, getting to the point where everything can
> > be done in the database without the need for superuser is absolutely a
> > good goal to be striving for, not something to be avoiding.
> > I don
On Mon, 2021-10-25 at 13:54 -0400, Stephen Frost wrote:
> Let's not forget that there are already existing non-superusers who
> can
> run things like REFRESH MATERIALIZED VIEW- the owner.
Right, that's one reason why I don't see a particular use case there.
But CHECKPOINT right now has an explici
On 2021-Oct-25, Jeff Davis wrote:
> But CHECKPOINT right now has an explicit superuser check, and it would
> be nice to be able to avoid that.
>
> It's pretty normal to issue a CHECKPOINT right after a data load and
> before running a performance test, right? Shouldn't there be some way
> to do t
> On Oct 24, 2021, at 7:49 AM, Bharath Rupireddy
> wrote:
>
> At this point, the idea of having a new role for maintenance work
> looks good. With this patch and Mark Dilger's patch introducing a
> bunch of new predefined roles, one concern is that we might reach to a
> state where we will ha
On Mon, 2021-10-25 at 17:55 -0300, Alvaro Herrera wrote:
> Maybe you just need pg_checkpointer.
Fair enough. Attached simpler patch that only covers checkpoint, and
calls the role pg_checkpointer.
Regards,
Jeff Davis
diff --git a/doc/src/sgml/ref/checkpoint.sgml b/doc/src/sgml/ref/checkp
On 10/25/21, 4:40 PM, "Jeff Davis" wrote:
> On Mon, 2021-10-25 at 17:55 -0300, Alvaro Herrera wrote:
>> Maybe you just need pg_checkpointer.
>
> Fair enough. Attached simpler patch that only covers checkpoint, and
> calls the role pg_checkpointer.
It feels a bit excessive to introduce a new prede
On Tue, 2021-10-26 at 00:07 +, Bossart, Nathan wrote:
> It feels a bit excessive to introduce a new predefined role just for
> this. Perhaps this could be accomplished with a new function that
> could be granted.
It would be nice if the syntax could be used, since it's pretty
widespread. I gu
On 10/25/21, 6:48 PM, "Jeff Davis" wrote:
> On Tue, 2021-10-26 at 00:07 +, Bossart, Nathan wrote:
>> It feels a bit excessive to introduce a new predefined role just for
>> this. Perhaps this could be accomplished with a new function that
>> could be granted.
>
> It would be nice if the synta
Greetings,
* Jeff Davis (pg...@j-davis.com) wrote:
> On Tue, 2021-10-26 at 00:07 +, Bossart, Nathan wrote:
> > It feels a bit excessive to introduce a new predefined role just for
> > this. Perhaps this could be accomplished with a new function that
> > could be granted.
>
> It would be nice
On Tue, 2021-10-26 at 16:02 -0400, Stephen Frost wrote:
> We're talking about benchmarking tools
What I had in mind was something much less formal, like a self-
contained repro case of a performance problem.
... simple schema
... data load
... maybe build some indexes
... maybe set hints
On 10/26/21, 2:04 PM, "Jeff Davis" wrote:
> Should we just add a builtin function pg_checkpoint(), and deprecate
> the syntax?
That seems reasonable to me.
Nathan
On Wed, Oct 27, 2021 at 3:18 AM Bossart, Nathan wrote:
>
> On 10/26/21, 2:04 PM, "Jeff Davis" 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_checkpoin
On Sat, 2021-10-30 at 13:24 +0530, Bharath Rupireddy wrote:
> 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
On 2021-Oct-30, Jeff Davis wrote:
> I tend to agree with all of this. The CHECKPOINT command is already
> there and people already use it. If we are already chipping away at the
> need for superuser elsewhere, we should offer a way to use CHECKPOINT
> without being superuser.
+1
> If the purpose
On 10/30/21, 11:14 AM, "Jeff Davis" wrote:
> On Sat, 2021-10-30 at 13:24 +0530, Bharath Rupireddy wrote:
>> 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 wer
Greetings,
* Bossart, Nathan (bossa...@amazon.com) wrote:
> On 10/30/21, 11:14 AM, "Jeff Davis" wrote:
> > On Sat, 2021-10-30 at 13:24 +0530, Bharath Rupireddy wrote:
> >> IMHO, moving away from SQL command "CHECKPOINT" to function
> >> "pg_checkpoint()" isn't nice as the SQL command has been the
On 11/1/21, 9:51 AM, "Stephen Frost" wrote:
> I don't really buy off on the "because it's been around a long time" as
> a reason to invent a predefined role for an individual command that
> doesn't take any options and could certainly just be a function.
> Applications developed to run as a superu
Greetings,
* Bossart, Nathan (bossa...@amazon.com) wrote:
> On 11/1/21, 9:51 AM, "Stephen Frost" wrote:
> > All that said, I wonder if we can have our cake and eat it too. I
> > haven't looked into this at all yet and perhaps it's foolish on its
> > face, but, could we make CHECKPOINT; basically
On 11/1/21, 10:43 AM, "Stephen Frost" wrote:
> Folks playing around in the catalog can break lots of things, I don't
> really see this as an argument against the idea.
>
> I do wonder if we should put a bit more effort into preventing people
> from messing with functions and such in pg_catalog. B
On Sat, Oct 23, 2021 at 5:45 PM Jeff Davis wrote:
> Add new predefined role pg_maintenance, which can issue VACUUM,
> ANALYZE, CHECKPOINT.
Just as a sort of general comment on this endeavor, I suspect that any
attempt to lump things together that seem closely related is doomed to
backfire. There'
On Mon, 2021-11-01 at 12:50 -0400, Stephen Frost wrote:
> All that said, I wonder if we can have our cake and eat it too. I
> haven't looked into this at all yet and perhaps it's foolish on its
> face, but, could we make CHECKPOINT; basically turn around and just
> run
> select pg_checkpoint(); wi
On Tue, 2021-11-02 at 11:06 -0400, Robert Haas wrote:
> Just as a sort of general comment on this endeavor, I suspect that
> any
> attempt to lump things together that seem closely related is doomed
> to
> backfire.
Agreed, I think that is apparent from the different opinions in this
thread.
Robe
On 11/2/21, 10:29 AM, "Jeff Davis" wrote:
> Great idea! Patch attached.
>
> This feels like a good pattern that we might want to use elsewhere, if
> the need arises.
The approach in the patch looks alright to me, but another one could
be to build a SelectStmt when parsing CHECKPOINT. I think tha
Greetings,
* Jeff Davis (pg...@j-davis.com) wrote:
> On Tue, 2021-11-02 at 11:06 -0400, Robert Haas wrote:
> > Just as a sort of general comment on this endeavor, I suspect that
> > any
> > attempt to lump things together that seem closely related is doomed
> > to
> > backfire.
>
> Agreed, I thin
On 11/2/21, 11:27 AM, "Stephen Frost" wrote:
> * Bossart, Nathan (bossa...@amazon.com) wrote:
>> The approach in the patch looks alright to me, but another one could
>> be to build a SelectStmt when parsing CHECKPOINT. I think that'd
>> simplify the standard_ProcessUtility() changes.
>
> For my 2
On 11/2/21 4:06 PM, Robert Haas wrote:
> There's bound to be somebody who wants to grant some of
> these permissions and not others, or who wants to grant the ability to
> run those commands on some tables but not others.
Is there anything stopping us from adding syntax like this?
GRANT VACUUM
On Tue, Nov 2, 2021 at 3:14 PM Vik Fearing wrote:
> On 11/2/21 4:06 PM, Robert Haas wrote:
> > There's bound to be somebody who wants to grant some of
> > these permissions and not others, or who wants to grant the ability to
> > run those commands on some tables but not others.
> Is there anythi
On Tue, 2 Nov 2021 at 18:14, Vik Fearing wrote:
> On 11/2/21 4:06 PM, Robert Haas wrote:
> > There's bound to be somebody who wants to grant some of
> > these permissions and not others, or who wants to grant the ability to
> > run those commands on some tables but not others.
> Is there anything
On 11/2/21 11:14 PM, Vik Fearing wrote:
> This would be nice, but there is nothing to hang our hat on:
>
> GRANT CHECKPOINT TO username;
Thinking about this more, why don't we just add CHECKPOINT and
NOCHECKPOINT attributes to roles?
ALTER ROLE username WITH CHECKPOINT;
--
Vik Fearing
On Tue, 2 Nov 2021 at 19:00, Vik Fearing wrote:
> On 11/2/21 11:14 PM, Vik Fearing wrote:
>
> > This would be nice, but there is nothing to hang our hat on:
> >
> > GRANT CHECKPOINT TO username;
>
> Thinking about this more, why don't we just add CHECKPOINT and
> NOCHECKPOINT attributes to ro
> On 2 Nov 2021, at 19:26, Stephen Frost wrote:
>> Otherwise, I see a couple of warnings when compiling:
>>xlogfuncs.c:54: warning: implicit declaration of function
>> ‘RequestCheckpoint’
>>xlogfuncs.c:56: warning: control reaches end of non-void function
>
> Yeah, such things w
Greetings,
* Bossart, Nathan (bossa...@amazon.com) wrote:
> On 11/2/21, 11:27 AM, "Stephen Frost" wrote:
> > * Bossart, Nathan (bossa...@amazon.com) wrote:
> >> The approach in the patch looks alright to me, but another one could
> >> be to build a SelectStmt when parsing CHECKPOINT. I think tha
On Tue, 2021-11-02 at 14:26 -0400, Stephen Frost wrote:
> Think you meant 'Stephen' there. ;)
Yes ;-)
> > The approach in the patch looks alright to me, but another one
> > could
> > be to build a SelectStmt when parsing CHECKPOINT. I think that'd
> > simplify the standard_ProcessUtility() chang
On Thu, Nov 4, 2021 at 12:03 PM Jeff Davis wrote:
> The approach of using a function's ACL to represent the ACL of a
> higher-level command (as in this patch) does feel right to me. It feels
> like something we might extend to similar situations in the future; and
> even if we don't, it seems like
On Thu, 2021-11-04 at 12:37 -0400, Robert Haas wrote:
> I don't have anything specific to propose, which I realize is kind of
> unhelpful ... but I don't like this, either.
We can go back to having a pg_checkpoint predefined role that is only
used for the CHECKPOINT command.
The only real argumen
Hi,
On 2021-11-02 10:28:39 -0700, Jeff Davis wrote:
> On Mon, 2021-11-01 at 12:50 -0400, Stephen Frost wrote:
> > All that said, I wonder if we can have our cake and eat it too. I
> > haven't looked into this at all yet and perhaps it's foolish on its
> > face, but, could we make CHECKPOINT; basi
Hi,
On 2021-11-04 14:25:54 -0700, Jeff Davis wrote:
> On Thu, 2021-11-04 at 12:37 -0400, Robert Haas wrote:
> > I don't have anything specific to propose, which I realize is kind of
> > unhelpful ... but I don't like this, either.
>
> We can go back to having a pg_checkpoint predefined role that
On Thu, 2021-11-04 at 15:46 -0700, Andres Freund wrote:
> What about extending GRANT to allow to grant rights on commands? Yes,
> it'd be
> a bit of work to make that work in the catalogs, but it doesn't seem
> too hard
> to tackle.
You mean for the CHECKPOINT command specifically, or for many com
On Thu, 2021-11-04 at 15:42 -0700, Andres Freund wrote:
> I don't like this. This turns the checkpoint command which previously
> didn't
> rely on the catalog in the happy path etc into something that
> requires most of
> the backend to be happily up to work.
It seems like this specific approach h
On Thu, Nov 4, 2021 at 5:25 PM Jeff Davis wrote:
> On Thu, 2021-11-04 at 12:37 -0400, Robert Haas wrote:
> > I don't have anything specific to propose, which I realize is kind of
> > unhelpful ... but I don't like this, either.
>
> We can go back to having a pg_checkpoint predefined role that is o
On Thu, Nov 4, 2021 at 7:38 PM Jeff Davis wrote:
> It seems like this specific approach has been mostly shot down already.
> But out of curiosity, are you intending to run CHECKPOINT during
> bootstrap or something?
Imagine a system with corruption in pg_proc. Right now, that won't
prevent you f
On Thu, Nov 4, 2021 at 6:46 PM Andres Freund wrote:
> What about extending GRANT to allow to grant rights on commands? Yes, it'd be
> a bit of work to make that work in the catalogs, but it doesn't seem too hard
> to tackle.
I think that there aren't too many commands where the question is just
w
On 2021-Nov-04, Jeff Davis wrote:
> But I don't see it generalizing to a lot of commands, either. I looked
> at the list, and it's taking some creativity to think of more than a
> couple other commands where it makes sense. Maybe LISTEN/NOTIFY? But
> even then, there are three related commands: LI
On 2021-11-05 08:42:58 -0400, Robert Haas wrote:
> On Thu, Nov 4, 2021 at 7:38 PM Jeff Davis wrote:
> > It seems like this specific approach has been mostly shot down already.
> > But out of curiosity, are you intending to run CHECKPOINT during
> > bootstrap or something?
>
> Imagine a system wi
Hi,
On 2021-11-05 08:54:37 -0400, Robert Haas wrote:
> On Thu, Nov 4, 2021 at 6:46 PM Andres Freund wrote:
> > What about extending GRANT to allow to grant rights on commands? Yes, it'd
> > be
> > a bit of work to make that work in the catalogs, but it doesn't seem too
> > hard
> > to tackle.
>
65 matches
Mail list logo
