Обсуждение: Truncate Triggers
(for 8.4 ...) I'd like to introduce triggers that fire when we issue a truncate: CREATE TRIGGER name [BEFORE | AFTER ] TRUNCATE ON table FOR EACH STATEMENT EXECUTE PROCEDURE function (arguments); The truncate trigger would fire separately from a statement-level DELETE statement, to allow us to distinguish those two events. There would be nothing to stop both triggers executing the same function however, if desired. COPY already provides a model for how a utility command can execute triggers. tablecmds.c would invoke ExecBSTruncateTriggers() and ExecASTruncateTriggers(), both of which would live in triggers.c There doesn't seem much too difficult about the implementation or behaviour, but I want to make sure we have the discussion at least, to see if anybody has concerns or additional requirements. Notes: As the syntax shows, these would be statement-level triggers (only). Requesting row level triggers will cause an error. [As Chris Browne explained, if people really want, they can use these facilities to create a Before Statement trigger that executes a DELETE, which then fires row level calls.] I also plan to add a TRUNCATE privilege, though that will be a separate patch in a later post. That will widen the use case of TRUNCATE, which should be OK to do once we've covered the replication concerns. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
Simon Riggs <simon@2ndquadrant.com> writes:
> Notes: As the syntax shows, these would be statement-level triggers
> (only). Requesting row level triggers will cause an error. [As Chris
> Browne explained, if people really want, they can use these facilities
> to create a Before Statement trigger that executes a DELETE, which then
> fires row level calls.]
Is there a way for a BS trigger to return a flag "skip the statement",
as there is for BR?
> I also plan to add a TRUNCATE privilege, though that will be a separate
> patch in a later post. That will widen the use case of TRUNCATE, which
> should be OK to do once we've covered the replication concerns.
Considering it's not MVCC-safe, do we *want* to widen the use case?
There are way too many table privilege bits already; to add more you
need something a lot stronger than a "might be nice" argument.
regards, tom lane
On Fri, 2008-01-25 at 10:44 -0500, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.com> writes: > > Notes: As the syntax shows, these would be statement-level triggers > > (only). Requesting row level triggers will cause an error. [As Chris > > Browne explained, if people really want, they can use these facilities > > to create a Before Statement trigger that executes a DELETE, which then > > fires row level calls.] > > Is there a way for a BS trigger to return a flag "skip the statement", > as there is for BR? We can alter the API for triggers to do that. Or are you thinking of having the Truncate Trigger API be different? > > I also plan to add a TRUNCATE privilege, though that will be a separate > > patch in a later post. That will widen the use case of TRUNCATE, which > > should be OK to do once we've covered the replication concerns. > > Considering it's not MVCC-safe, do we *want* to widen the use case? > > There are way too many table privilege bits already; to add more you > need something a lot stronger than a "might be nice" argument. People use TRUNCATE whatever we say. If you force people to be table owners or superusers you merely restrict their security options. If you want to prevent wider use then perhaps a better explanation of what "MVCC-safe" means in the docs would do that. Most people don't understand that phrase, or its implications. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
Simon Riggs <simon@2ndquadrant.com> writes:
> On Fri, 2008-01-25 at 10:44 -0500, Tom Lane wrote:
>> There are way too many table privilege bits already; to add more you
>> need something a lot stronger than a "might be nice" argument.
> People use TRUNCATE whatever we say. If you force people to be table
> owners or superusers you merely restrict their security options.
By that argument you could justify a separate privilege bit for anything
at all, eg, each sub-variant of ALTER TABLE. Please present an actual
argument why TRUNCATE should get its own bit.
regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > On Fri, 2008-01-25 at 10:44 -0500, Tom Lane wrote:
> >> There are way too many table privilege bits already; to add more you
> >> need something a lot stronger than a "might be nice" argument.
>
> > People use TRUNCATE whatever we say. If you force people to be table
> > owners or superusers you merely restrict their security options.
>
> By that argument you could justify a separate privilege bit for anything
> at all, eg, each sub-variant of ALTER TABLE. Please present an actual
> argument why TRUNCATE should get its own bit.
I've done this already, and continue to feel that TRUNCATE should have
its own bit. There are many cases where you want a user to be able to
truncate a table but not alter its structure. TRUNCATE is not a
DDL-type statement, those can and should be reserved to the owner.
Thanks,
Stephen
On Fri, 2008-01-25 at 14:00 -0500, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.com> writes: > > On Fri, 2008-01-25 at 10:44 -0500, Tom Lane wrote: > >> There are way too many table privilege bits already; to add more you > >> need something a lot stronger than a "might be nice" argument. > > > People use TRUNCATE whatever we say. If you force people to be table > > owners or superusers you merely restrict their security options. > > By that argument you could justify a separate privilege bit for anything > at all, eg, each sub-variant of ALTER TABLE. I already made the argument that TRUNCATE is not similar to DDL in its effects or usage. http://archives.postgresql.org/pgsql-sql/2008-01/msg00093.php > Please present an actual > argument why TRUNCATE should get its own bit. Claiming my argument doesn't exist is itself a fairly weak defence... I only mentioned this for completeness, since its on the TODO list. You should probably go through the TODO and remove the items you disagree with. I didn't put it there, nor did I originally propose it. I do think it has merit; I came up against exactly that issue earlier this month. Perhaps we should be implementing "extended privileges" by using one additional bit to mean "has extended privilege list". We presumably want to implement column level privileges, plus you raise interesting thoughts about fine grained security access controls for certain operations, so an extension mechanism seems like the way to go. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
"Simon Riggs" <simon@2ndquadrant.com> writes: > On Fri, 2008-01-25 at 14:00 -0500, Tom Lane wrote: >> Simon Riggs <simon@2ndquadrant.com> writes: >> > On Fri, 2008-01-25 at 10:44 -0500, Tom Lane wrote: >> >> There are way too many table privilege bits already; to add more you >> >> need something a lot stronger than a "might be nice" argument. >> >> > People use TRUNCATE whatever we say. If you force people to be table >> > owners or superusers you merely restrict their security options. >> >> By that argument you could justify a separate privilege bit for anything >> at all, eg, each sub-variant of ALTER TABLE. > > I already made the argument that TRUNCATE is not similar to DDL in its > effects or usage. > http://archives.postgresql.org/pgsql-sql/2008-01/msg00093.php > >> Please present an actual >> argument why TRUNCATE should get its own bit. > > Claiming my argument doesn't exist is itself a fairly weak defence... Uhm, your argument seems to consist of: | 1. Commands that alter the rows in the table | e.g. UPDATE, DELETE, INSERT + TRUNCATE is clearly part of this group I don't think saying "clearly" constitutes an argument. The fact is that TRUNCATE is a big of a screw case. It can be seen either way. A lot of users use it as a "cheaper form of delete" which would make it DML. Except the whole reason it's cheaper is precisely because of the way it works which is by being a closer analogy to a DROP and CREATE sequence of DDL. This two-faced personality is just why we're facing this problem. It looks to users like DML but it under the hood it behaves just like DDL. I think there are two strategies here. Either we paper over the DDLishness by making it look as much as possible like DML. It'll never be perfect but we'll be as friendly as we can for users. That might just make the DDLishness pop up at all the more surprising moments and be all that much harder to explain though. Or we can just declare it DDL and put a warning in the documentation that while it may sound like it's DML it is in fact better thought of as a shortcut for doing DROP and CREATE and should be used as such. On the other hand perhaps it would be nice to have per-statement DDL triggers in general. So you could, for example, update a data dictionary automatically for simple cases like DROP TABLE. Per-statement DDL triggers might also provide a more extensible way to enforce unusual security policies. "Nobody's allowed to do any DDL except during a maintenance window" for example. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!
On Jan 26, 2008 8:14 AM, Gregory Stark <stark@enterprisedb.com> wrote: > This two-faced personality is just why we're facing this problem. It looks to > users like DML but it under the hood it behaves just like DDL. > Agreed that it looks like DML. Speaking as a user, I came away from the documentation thinking that "TRUNCATE foo;" was just a quicker (to type as well as to run) alternative to "DELETE * FROM foo;". Plus it has the handy quality of being able to empty multiple tables in the one command. > I think there are two strategies here. Either we paper over the DDLishness by > making it look as much as possible like DML. It'll never be perfect but we'll > be as friendly as we can for users. That might just make the DDLishness pop up > at all the more surprising moments and be all that much harder to explain > though. > > Or we can just declare it DDL and put a warning in the documentation that > while it may sound like it's DML it is in fact better thought of as a shortcut > for doing DROP and CREATE and should be used as such. > It's my humble opinion that the analogy to DROP + CREATE does need to be made more clear. Cheers BJ
* Simon Riggs (simon@2ndquadrant.com) wrote:
> Perhaps we should be implementing "extended privileges" by using one
> additional bit to mean "has extended privilege list". We presumably want
> to implement column level privileges, plus you raise interesting
> thoughts about fine grained security access controls for certain
> operations, so an extension mechanism seems like the way to go.
I disagree and feel that my now rather ancient proposal is still better-
split the "permission" bits and the "grantable" bits into two seperate
32bit integers. That has the advantage of doubleing the number of
available bits while also splitting up the frequently used bits
("permission" bits) from the much, much, much less frequently used bits
("grantable" bits).
Of course, the last time this went around the argument was that we
shouldn't add alot of extra code until we actually needed to, while at
the same time we shouldn't use up the few remaining bits we have. The
fact that this makes for an impossible situation seems to have been
lost.
Thanks,
Stephen
On Friday 25 January 2008 06:40, Simon Riggs wrote:
> Notes: As the syntax shows, these would be statement-level triggers
> (only). Requesting row level triggers will cause an error. [As Chris
> Browne explained, if people really want, they can use these facilities
> to create a Before Statement trigger that executes a DELETE, which then
> fires row level calls.]
>
This seems to completly hand-wave away the idea of implementing row level
visibility in statement level triggers, something I am hoping to see
implemented somewhere down the line. Am I missing something?
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
"Robert Treat" <xzilla@users.sourceforge.net> writes: > the idea of implementing row level visibility in statement level triggers Huh? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning
Robert Treat wrote: > On Friday 25 January 2008 06:40, Simon Riggs wrote: > >> Notes: As the syntax shows, these would be statement-level triggers >> (only). Requesting row level triggers will cause an error. [As Chris >> Browne explained, if people really want, they can use these facilities >> to create a Before Statement trigger that executes a DELETE, which then >> fires row level calls.] >> >> > > This seems to completly hand-wave away the idea of implementing row level > visibility in statement level triggers, something I am hoping to see > implemented somewhere down the line. Am I missing something? > > The rowset (not row) associated with the statement level trigger would be the whole table in case of a TRUNCATE trigger, so in this (corner) case it's not too helpful. Regards, Andreas
On Sat, Jan 26, 2008 at 04:33:53PM +0000, Gregory Stark wrote: > "Robert Treat" <xzilla@users.sourceforge.net> writes: > > > the idea of implementing row level visibility in statement level triggers > > Huh? I think he means that statement level triggers can see the rows that got affected, presumably by NEW representing a tuplestore. How that would work for BEFORE STATEMENT triggers I don't know. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy
Robert Treat <xzilla@users.sourceforge.net> writes:
> This seems to completly hand-wave away the idea of implementing row level
> visibility in statement level triggers, something I am hoping to see
> implemented somewhere down the line. Am I missing something?
That was discussed already --- we agreed that TRUNCATE triggers would
simply omit that functionality, when and if it's implemented for other
trigger types.
regards, tom lane
Stephen Frost <sfrost@snowman.net> writes:
> Of course, the last time this went around the argument was that we
> shouldn't add alot of extra code until we actually needed to, while at
> the same time we shouldn't use up the few remaining bits we have. The
> fact that this makes for an impossible situation seems to have been
> lost.
No, it hasn't been forgotten at all. Whenever we have to cross that
bridge, we'll do so. The questions being asked here are about whether
an adequate case has been made for adding *user-visible* complexity,
not about nitty little details of internal representation.
There are also some compatibility concerns involved. If we add
grantable privileges for TRUNCATE and/or DDL operations, then GRANT ALL
ON TABLE suddenly conveys a whole lot more privilege than it did before.
This could lead to unpleasant surprises in security-sensitive
operations. One could also put forward the argument that it's a direct
violation of the SQL spec, which after all does specify exactly what
privileges ALL is supposed to grant.
regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> There are also some compatibility concerns involved. If we add
> grantable privileges for TRUNCATE and/or DDL operations, then GRANT ALL
> ON TABLE suddenly conveys a whole lot more privilege than it did before.
> This could lead to unpleasant surprises in security-sensitive
> operations. One could also put forward the argument that it's a direct
> violation of the SQL spec, which after all does specify exactly what
> privileges ALL is supposed to grant.
iirc, the suggestion was to exclude the non-SQL-spec things from 'GRANT
ALL' to avoid just that issue. Having to grant TRUNCATE and/or DDL
operation permissions explicitly would be reasonable. This might create
a disconnect with what 'revoke all' does, since that should really
remove all of the perms, but I feel that's reasonable. A 'Default
secure' approach.
Thanks,
Stephen
Stephen Frost <sfrost@snowman.net> writes:
> iirc, the suggestion was to exclude the non-SQL-spec things from 'GRANT
> ALL' to avoid just that issue. Having to grant TRUNCATE and/or DDL
> operation permissions explicitly would be reasonable. This might create
> a disconnect with what 'revoke all' does, since that should really
> remove all of the perms, but I feel that's reasonable. A 'Default
> secure' approach.
More like "default impossibly confusing" :-(. "GRANT ALL" doesn't mean
grant all privileges? How the heck are you going to explain/justify
that to a newbie?
regards, tom lane
On Sat, 2008-01-26 at 11:19 -0500, Robert Treat wrote: > On Friday 25 January 2008 06:40, Simon Riggs wrote: > > Notes: As the syntax shows, these would be statement-level triggers > > (only). Requesting row level triggers will cause an error. [As Chris > > Browne explained, if people really want, they can use these facilities > > to create a Before Statement trigger that executes a DELETE, which then > > fires row level calls.] > > > > This seems to completly hand-wave away the idea of implementing row level > visibility in statement level triggers, something I am hoping to see > implemented somewhere down the line. Am I missing something? Not sure why you say that. We have a choice: i) TRUNCATE never has access to rows ii) TRUNCATE can have access, in which case it acts like a DELETE Forcing ii) in all cases would effectively negate truncate triggers, so we must have some way of providing both alternatives as options. As Chris explained, if we allow a BEFORE STATEMENT trigger on TRUNCATE to issue a DELETE instead, then we are OK to just allow i) and yet retain the ability to access rows for those that want it. There may be another of way of doing this also, but I'll leave that possibility to whoever tries to implement the feature you mention in the future. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > iirc, the suggestion was to exclude the non-SQL-spec things from 'GRANT
> > ALL' to avoid just that issue. Having to grant TRUNCATE and/or DDL
> > operation permissions explicitly would be reasonable. This might create
> > a disconnect with what 'revoke all' does, since that should really
> > remove all of the perms, but I feel that's reasonable. A 'Default
> > secure' approach.
>
> More like "default impossibly confusing" :-(. "GRANT ALL" doesn't mean
> grant all privileges? How the heck are you going to explain/justify
> that to a newbie?
"grant all" *already* doesn't mean grant all privileges. This isn't
really a change from that. Additionally, there's lots of places where
we follow the SQL spec because that's the right thing to do even though
it's not always the most intuitive thing to do. I certainly don't feel
this is 'impossibly confusing' any more than 'grant all' doesn't mean
you can truncate or alter the table today.
Thanks,
Stephen
On Fri, Jan 25, 2008 at 11:40:19AM +0000, Simon Riggs wrote: > (for 8.4 ...) > I'd like to introduce triggers that fire when we issue a truncate: Rather than focusing exclusively on TRUNCATE, how about "triggers" that fire whenever any kind of DDL operation is performed? (Ok, truncate is more DML than DDL, but still). The reason I put triggers in quotes is because I'm not suggesting that we actually put triggers on the catalog tables, since we all know that's hard/impossible. Instead this would have to tie into command processing, similar to what you're proposing for truncate. -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
Decibel! wrote: > On Fri, Jan 25, 2008 at 11:40:19AM +0000, Simon Riggs wrote: > > (for 8.4 ...) > > I'd like to introduce triggers that fire when we issue a truncate: > > Rather than focusing exclusively on TRUNCATE, how about "triggers" that > fire whenever any kind of DDL operation is performed? (Ok, truncate is > more DML than DDL, but still). I don't think it makes sense in general. For example, would we fire triggers on CLUSTER? Or on ALTER TABLE / SET STATISTICS? TRUNCATE seems a special case that needs it. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Fri, 2008-01-25 at 10:44 -0500, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.com> writes: > > Notes: As the syntax shows, these would be statement-level triggers > > (only). Requesting row level triggers will cause an error. [As Chris > > Browne explained, if people really want, they can use these facilities > > to create a Before Statement trigger that executes a DELETE, which then > > fires row level calls.] > > Is there a way for a BS trigger to return a flag "skip the statement", > as there is for BR? I've got a working version of truncate triggers now, posted to -patches shortly. Answering the above question is the last point of the implementation. ISTM it would be best to think of it as a separate and not-very related feature, implemented as a separate patch, if we decide we really do want that. It doesn't seem important to do that for replication, which was the main use case for truncate triggers. Currently, BS trigger functions return NULL. This is handled in various ways within each PL and is specifically tested for within main trigger exec code. Returning different information in some form or other would be required to signal "skip the main statement". FOR EACH ROW triggers return NULL when they want to skip the change for that row, so the current implementation is the wrong way round for BS triggers. I'm not sure how to handle that in a way that makes obvious sense for future trigger developers, so suggestions welcome. So allowing us to skip commands as a result of statement level triggers is as much work for INSERT, UPDATE, DELETE and TRUNCATE together as it is just for TRUNCATE. I also think that if we did do that for TRUNCATE it would be useful to do for the other commands anyway. SQLStandard doesn't say we *can't* do this. Having said that, some PLs simply ignore the return value from BS triggers. So interpreting return values in new ways might make existing trigger code break or behave differently. So if we did BS trigger skipping for all statement types then we would need to introduce that concept slowly over a couple of releases with a non-default, then default trigger behaviour parameter. I've written the truncate trigger handling in such a way that it would be straightforward to extend this to include statement skipping, should we do it in the future. Can we just skip statement skipping? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
On Mon, Jan 28, 2008 at 09:09:13PM -0300, Alvaro Herrera wrote: > Decibel! wrote: > > On Fri, Jan 25, 2008 at 11:40:19AM +0000, Simon Riggs wrote: > > > (for 8.4 ...) > > > I'd like to introduce triggers that fire when we issue a truncate: > > > > Rather than focusing exclusively on TRUNCATE, how about "triggers" that > > fire whenever any kind of DDL operation is performed? (Ok, truncate is > > more DML than DDL, but still). > > I don't think it makes sense in general. For example, would we fire > triggers on CLUSTER? Or on ALTER TABLE / SET STATISTICS? CLUSTER isn't DDL. Most forms of ALTER TABLE are. And CREATE blah, etc. My point is that people have been asking for triggers that fire when specific commands are executed for a long time; it would be short-sighted to come up with a solution that only works for TRUNCATE if we could instead come up with a more generic solution that works for a broader class of (or perhaps all) commands. -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
On Thu, 2008-01-31 at 01:12 -0600, Decibel! wrote: > On Mon, Jan 28, 2008 at 09:09:13PM -0300, Alvaro Herrera wrote: > > Decibel! wrote: > > > On Fri, Jan 25, 2008 at 11:40:19AM +0000, Simon Riggs wrote: > > > > (for 8.4 ...) > > > > I'd like to introduce triggers that fire when we issue a truncate: > > > > > > Rather than focusing exclusively on TRUNCATE, how about "triggers" that > > > fire whenever any kind of DDL operation is performed? (Ok, truncate is > > > more DML than DDL, but still). > > > > I don't think it makes sense in general. For example, would we fire > > triggers on CLUSTER? Or on ALTER TABLE / SET STATISTICS? > > CLUSTER isn't DDL. Most forms of ALTER TABLE are. And CREATE blah, etc. > > My point is that people have been asking for triggers that fire when > specific commands are executed for a long time; it would be > short-sighted to come up with a solution that only works for TRUNCATE if > we could instead come up with a more generic solution that works for a > broader class of (or perhaps all) commands. Seems a fair comment. What set of commands would you want to fire triggers for, and why? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
"Decibel!" <decibel@decibel.org> writes: > CLUSTER isn't DDL. Most forms of ALTER TABLE are. And CREATE blah, etc. Fwiw I would call CLUSTER DDL. Note that it does make a change that's visible in the table definition afterwards. There are plenty of DDL commands which modify data (CREATE INDEX, ATLER TABLE ALTER COLUMN TYPE). The defining characteristic of DDL is not that it doesn't modify the data but that it does modify the table definition. By that definition CLUSTER is DDL and TRUNCATE is DDL if you look at the implementation rather than the user-visible effects. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!
On Thu, 2008-01-31 at 10:22 +0000, Gregory Stark wrote: > "Decibel!" <decibel@decibel.org> writes: > > > CLUSTER isn't DDL. Most forms of ALTER TABLE are. And CREATE blah, etc. > > Fwiw I would call CLUSTER DDL. Note that it does make a change that's visible > in the table definition afterwards. Is it really ? What change does TRUNCATE make in table definition ? > > There are plenty of DDL commands which modify data (CREATE INDEX, ATLER TABLE > ALTER COLUMN TYPE). The defining characteristic of DDL is not that it doesn't > modify the data but that it does modify the table definition. > > By that definition CLUSTER is DDL and TRUNCATE is DDL if you look at the > implementation rather than the user-visible effects. >
On Thu, 2008-01-31 at 10:22 +0000, Gregory Stark wrote: > "Decibel!" <decibel@decibel.org> writes: > > > CLUSTER isn't DDL. Most forms of ALTER TABLE are. And CREATE blah, etc. > > Fwiw I would call CLUSTER DDL. Note that it does make a change that's visible > in the table definition afterwards. > > There are plenty of DDL commands which modify data (CREATE INDEX, ATLER TABLE > ALTER COLUMN TYPE). The defining characteristic of DDL is not that it doesn't > modify the data but that it does modify the table definition. > > By that definition CLUSTER is DDL and TRUNCATE is DDL if you look at the > implementation rather than the user-visible effects. Surely the question is more simple: do we want triggers on it? There's a clear case for TRUNCATE to have a triggers. Is there a clear case for any other statements (however you categorise them)? If so, lets hear it, please. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
On Thu, Jan 31, 2008 at 11:45:55AM +0000, Simon Riggs wrote: > On Thu, 2008-01-31 at 10:22 +0000, Gregory Stark wrote: > > "Decibel!" <decibel@decibel.org> writes: > > > > > CLUSTER isn't DDL. Most forms of ALTER TABLE are. And CREATE blah, etc. > > > > Fwiw I would call CLUSTER DDL. Note that it does make a change that's visible > > in the table definition afterwards. > > > > There are plenty of DDL commands which modify data (CREATE INDEX, ATLER TABLE > > ALTER COLUMN TYPE). The defining characteristic of DDL is not that it doesn't > > modify the data but that it does modify the table definition. > > > > By that definition CLUSTER is DDL and TRUNCATE is DDL if you look at the > > implementation rather than the user-visible effects. > > Surely the question is more simple: do we want triggers on it? > > There's a clear case for TRUNCATE to have a triggers. > > Is there a clear case for any other statements (however you categorise > them)? If so, lets hear it, please. Having "triggers" on ALTER table means that replication systems can replicate changes automatically. Having triggers on CREATE, ALTER, DROP mean that you can audit DDL, or if desired prevent it. Searching the mailing lists for "ddl trigger" for the last year turns up 87 hits. This is something that a lot of people have been asking for. -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
On Thu, Jan 31, 2008 at 10:22:42AM +0000, Gregory Stark wrote: > > "Decibel!" <decibel@decibel.org> writes: > > > CLUSTER isn't DDL. Most forms of ALTER TABLE are. And CREATE blah, > > etc. > > Fwiw I would call CLUSTER DDL. Note that it does make a change > that's visible in the table definition afterwards. > > There are plenty of DDL commands which modify data (CREATE INDEX, > ATLER TABLE ALTER COLUMN TYPE). The defining characteristic of DDL > is not that it doesn't modify the data but that it does modify the > table definition. Counter-example: ALTER TABLE ALTER COLUMN USING will almost certainly modify data, but it's DDL nonetheless. > By that definition CLUSTER is DDL and TRUNCATE is DDL if you look at > the implementation rather than the user-visible effects. I agree that both are more DDL-like than DML-like. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> On Thu, Jan 31, 2008 at 10:22:42AM +0000, Gregory Stark wrote:
>> There are plenty of DDL commands which modify data (CREATE INDEX,
>> ATLER TABLE ALTER COLUMN TYPE). The defining characteristic of DDL
>> is not that it doesn't modify the data but that it does modify the
>> table definition.
> Counter-example: ALTER TABLE ALTER COLUMN USING will almost certainly
> modify data, but it's DDL nonetheless.
Uh, how is that a counter-example to what Greg said?
I think the real question here is whether there is a defensible use-case
for a trigger. In the case of TRUNCATE it's pretty obvious what you
might want the trigger for: to do the same thing your per-row ON DELETE
trigger does, but across all rows. It's much less easy to envision a
scriptable-in-advance response to arbitrary ALTER TABLE operations.
Looked at in this light, TRUNCATE is clearly more nearly DML than DDL,
in the sense that its effects are predictable and involve only the data
not the schema (at least in the user's view).
regards, tom lane
On Sat, Feb 02, 2008 at 02:23:40PM -0500, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > On Thu, Jan 31, 2008 at 10:22:42AM +0000, Gregory Stark wrote: > >> There are plenty of DDL commands which modify data (CREATE INDEX, > >> ATLER TABLE ALTER COLUMN TYPE). The defining characteristic of DDL > >> is not that it doesn't modify the data but that it does modify the > >> table definition. > > > Counter-example: ALTER TABLE ALTER COLUMN USING will almost certainly > > modify data, but it's DDL nonetheless. > > Uh, how is that a counter-example to what Greg said? Oops. I misread. D'oh. > I think the real question here is whether there is a defensible > use-case for a trigger. In the case of TRUNCATE it's pretty obvious > what you might want the trigger for: to do the same thing your > per-row ON DELETE trigger does, but across all rows. It's much less > easy to envision a scriptable-in-advance response to arbitrary ALTER > TABLE operations. > > Looked at in this light, TRUNCATE is clearly more nearly DML than > DDL, in the sense that its effects are predictable and involve only > the data not the schema (at least in the user's view). It still involves DDL-like operations in the sense of changing (for the duration of the query) trigger behavior. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Feb 2, 2008 2:43 PM, David Fetter <david@fetter.org> wrote: > It still involves DDL-like operations in the sense of changing (for > the duration of the query) trigger behavior. But that "change of trigger behavior" is still more in the "DML" sense than "DDL sense." The point of TRUNCATE is that we have an optimization that's superior (in some important senses) to "DELETE FROM." The differences between TRUNCATE FOO and DELETE FROM FOO do not have to do with the one altering the schema for the table. The schema remains the same. I think it would be nice to be able to have more "trigger hooks" relating to DDL changes, but I also think that will represent some fundamentally more difficult problems being raised than is the case for a TRUNCATE trigger. -- http://linuxfinances.info/info/linuxdistributions.html "The definition of insanity is doing the same thing over and over and expecting different results." -- assortedly attributed to Albert Einstein, Benjamin Franklin, Rita Mae Brown, and Rudyard Kipling
On Sat, Feb 02, 2008 at 05:23:39PM -0500, Christopher Browne wrote: > On Feb 2, 2008 2:43 PM, David Fetter <david@fetter.org> wrote: > > It still involves DDL-like operations in the sense of changing > > (for the duration of the query) trigger behavior. > > But that "change of trigger behavior" is still more in the "DML" > sense than "DDL sense." > > The point of TRUNCATE is that we have an optimization that's > superior (in some important senses) to "DELETE FROM." > > The differences between TRUNCATE FOO and DELETE FROM FOO do not have > to do with the one altering the schema for the table. The schema > remains the same. In essence, the schema changes for the duration of the TRUNCATE. Any data integrity constraints that triggers used are therefore violated, and it's DDL-like. To put it another way, if you want to duplicate the trigger-suppression behavior, you have to issue DDL. > I think it would be nice to be able to have more "trigger hooks" > relating to DDL changes, but I also think that will represent some > fundamentally more difficult problems being raised than is the case > for a TRUNCATE trigger. Are they really? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> On Sat, Feb 02, 2008 at 05:23:39PM -0500, Christopher Browne wrote:
>> I think it would be nice to be able to have more "trigger hooks"
>> relating to DDL changes, but I also think that will represent some
>> fundamentally more difficult problems being raised than is the case
>> for a TRUNCATE trigger.
> Are they really?
One fairly obvious difficulty is how to pass the trigger any meaningful
information about what happened (or is about to happen). In the case of
TRUNCATE, pretty much everything you need to know is implicit in the
event type.
I suspect that triggers on DDL will also share some of the problems
we've identified in past discussions about supporting triggers on system
catalogs --- in fact, to a first approximation these might be the same
thing. It's not clear that we want to be firing random user-defined
code during the process of a catalog update, nor that it can be
guaranteed a consistent view of the system state. TRUNCATE triggers
are a narrow enough case to dodge that complexity, or much of it anyway
(in a multi-table TRUNCATE there'd still be some interesting questions
about just when the triggers fire).
regards, tom lane
On Sat, 2008-02-02 at 17:38 -0500, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > On Sat, Feb 02, 2008 at 05:23:39PM -0500, Christopher Browne wrote: > >> I think it would be nice to be able to have more "trigger hooks" > >> relating to DDL changes, but I also think that will represent some > >> fundamentally more difficult problems being raised than is the case > >> for a TRUNCATE trigger. > > > Are they really? > > One fairly obvious difficulty is how to pass the trigger any meaningful > information about what happened (or is about to happen). In the case of > TRUNCATE, pretty much everything you need to know is implicit in the > event type. That's my thought also. I've got a feeling we could do lots of work and DDL triggers still wouldn't pass all the information people might want. I'm not against DDL triggers at all, but I honestly can't see a use for them. Maybe there is a general case, or maybe just some specific cases. I take Jim's point that many people have asked for them, but I don't recall anybody explaining themselves in detail. I note that both Oracle and SQLServer support DDL triggers, but the best use case example I've seen is user configurable auditing. Do we need that? If we do, we need autonomous transactions first anyway... -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 Simon Riggs wrote: >> One fairly obvious difficulty is how to pass the trigger any meaningful >> information about what happened (or is about to happen). In the case of >> TRUNCATE, pretty much everything you need to know is implicit in the >> event type. > That's my thought also. I've got a feeling we could do lots of work and > DDL triggers still wouldn't pass all the information people might want. > > I'm not against DDL triggers at all, but I honestly can't see a use for > them. Maybe there is a general case, or maybe just some specific cases. > I take Jim's point that many people have asked for them, but I don't > recall anybody explaining themselves in detail. Bucardo would use them, but mostly in the same way that the proposed truncate trigger is being used - notification that a change has occurred, but without the details. For example, a trigger on a table being replicated would tell Bucardo to stop immediately the moment a change was made. We don't really need to know *what* the change was, just that something has changed that might affect replication. Perhaps the application might then compare its stored version of the schema to the actual one and figure out a change, but just a notice that *something* has changed would be a useful start. +1 and thanks for the truncate trigger, at any rate. :) - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200802032243 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFHpon3vJuQZxSWSsgRA28JAKDNV/qJcr2CqwWD0B1ly6tW0cCxXQCgo9TP jfDUpXcSi8ET3K6v82AEa44= =OkQq -----END PGP SIGNATURE-----
> There are also some compatibility concerns involved. If we add > grantable privileges for TRUNCATE and/or DDL operations, then GRANT ALL > ON TABLE suddenly conveys a whole lot more privilege than it did before. > This could lead to unpleasant surprises in security-sensitive > operations. One could also put forward the argument that it's a direct > violation of the SQL spec, which after all does specify exactly what > privileges ALL is supposed to grant. > > regards, tom lane What about separating privileges: "system privileges" for ddl statements and "object privileges" for dml statements in an"Oracle-like" way? Then you could implement TRUNCATE privileges like they do (roles must have DROP ANY TABLE system privileges).Is or was there a discussion over this hypothesis?