Re: [GENERAL] Delete rule does not prevent truncate
Just in case it has not been made obvious yet, rules are silently deprecated. They still exist because views depend on them, but it is generally considered best practices to not use them outside that realm. Well, the manual doesn't seem to reflect that fact. If that's how the developers feel about rules, i.e they can't be bothered to make any changes to the rules code any more, no matter how minor (e.g. this TRUNCATE issue), then you should explicitly state in the manual that they are depreciated, and stop making stupid statements like so if many rows are affected in one statement, a rule issuing one extra command is likely to be faster than a trigger that encourage people to use rules ! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
On 23 July 2015 at 19:25, Scott Marlowe scott.marl...@gmail.com wrote: stick to triggers, they're faster Erm, not according to your beloved manual !!! 38.7. Rules Versus Triggers a rule issuing one extra command is likely to be faster than a trigger The summary is, rules will only be significantly slower than triggers if their actions result in large and badly qualified joins, a situation where the planner fails. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
For crying out loud GET OVER IT! You've been given a very reasonable and quick solution to your problem. You can either 1. Keep crying and moaning until someone changes the rules. 2. Give up and port to another database. 3. Write the triggers and solve your problem! On Fri, Jul 24, 2015 at 5:27 AM, Tim Smith randomd...@gmail.com wrote: On 23 July 2015 at 19:25, Scott Marlowe scott.marl...@gmail.com wrote: stick to triggers, they're faster Erm, not according to your beloved manual !!! 38.7. Rules Versus Triggers a rule issuing one extra command is likely to be faster than a trigger The summary is, rules will only be significantly slower than triggers if their actions result in large and badly qualified joins, a situation where the planner fails. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] Delete rule does not prevent truncate
On 2015-07-24 10:29:21 +0100, Tim Smith wrote: That's not the point. Backups are important, but so is the concept of various layers of anti-fat-finger protection. Restoring off backups should be last resort, not first. Oh, comeon. Install a TRUNCATE trigger and let this thread die. Andres -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
On 24 July 2015 at 01:37, Rob Sargent robjsarg...@gmail.com wrote: Fair enough but both blackhats and the authorized are just as likely to drop the database as truncate something (intentionally or not) and backups stashed everywhere is the first order of business. That's not the point. Backups are important, but so is the concept of various layers of anti-fat-finger protection. Restoring off backups should be last resort, not first. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
On Fri, Jul 24, 2015 at 10:24:55AM +0100, Tim Smith wrote: If that's how the developers feel about rules, i.e they can't be bothered to make any changes to the rules code any more, no matter how minor (e.g. this TRUNCATE issue) Who is this they? As I think I suggested in another mail, if you think this is trivial and easy then I think you should propose the patch to solve it. I understand what you're saying; I think the solution is self-evident (add a statement trigger that captures TRUNCATE and DO INSTEAD NOTHING), so I just wouldn't be bothered to fix this. But I suspect things are the way they are partly because nobody proposed or implemented a patch for this behaviour before. The manual is also quite clear in what statements you can write rules about; by implication, other statements are not covered, so I'm not actually sure why you think the manual is misleading. Best regards, A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
On Fri, Jul 24, 2015 at 3:27 AM, Tim Smith randomd...@gmail.com wrote: On 23 July 2015 at 19:25, Scott Marlowe scott.marl...@gmail.com wrote: stick to triggers, they're faster Erm, not according to your beloved manual !!! 38.7. Rules Versus Triggers a rule issuing one extra command is likely to be faster than a trigger The summary is, rules will only be significantly slower than triggers if their actions result in large and badly qualified joins, a situation where the planner fails. So, that's the sum total of what you took away from my post? Nevermind, I'll leave you alone. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
On 07/24/2015 02:32 AM, Andres Freund wrote: On 2015-07-24 10:29:21 +0100, Tim Smith wrote: That's not the point. Backups are important, but so is the concept of various layers of anti-fat-finger protection. Restoring off backups should be last resort, not first. Oh, comeon. Install a TRUNCATE trigger and let this thread die. Andres Please god, +1. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
On 07/24/2015 02:24 AM, Tim Smith wrote: Just in case it has not been made obvious yet, rules are silently deprecated. They still exist because views depend on them, but it is generally considered best practices to not use them outside that realm. Well, the manual doesn't seem to reflect that fact. If that's how the developers feel about rules, i.e they can't be bothered to make any changes to the rules code any more, no matter how minor (e.g. this TRUNCATE issue), then you should explicitly state in the manual that they are depreciated, and stop making stupid statements like so if many rows are affected in one statement, a rule issuing one extra command is likely to be faster than a trigger that encourage people to use rules ! Seems that section needs editing to make it clearer that this depends on what type of trigger you use. Or as it states here: http://www.postgresql.org/docs/9.4/interactive/sql-createtrigger.html A trigger that is marked FOR EACH ROW is called once for every row that the operation modifies. For example, a DELETE that affects 10 rows will cause any ON DELETE triggers on the target relation to be called 10 separate times, once for each deleted row. In contrast, a trigger that is marked FOR EACH STATEMENT only executes once for any given operation, regardless of how many rows it modifies (in particular, an operation that modifies zero rows will still result in the execution of any applicable FOR EACH STATEMENT triggers). At any rate you know what the situation is. Getting increasingly belligerent is not going to help that situation and the energy would seem to me better spent on adapting to reality and moving on. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
On 07/23/2015 04:15 PM, Karsten Hilbert wrote: On Thu, Jul 23, 2015 at 12:28:32PM -0600, Rob Sargent wrote: I'm suggesting OP might find changing truncate statements to deletes (without a where clause) a simpler solution. Something has to change. Well, OP isn't looking for a solution to delete all rows but rather to _prevent_ deletion. Tim can't go forth and tell Blackhats to please use DELETE rather than TRUNCATE, right ? AFAICT it'd be more useful to advise OP to revoke TRUNCATE rights on tables. Karsten Not sure about Tim and the Blackhats (there's a band name in there somewhere) but Wouldn't OP have exact same code to fix, one way or another?
Re: [GENERAL] Delete rule does not prevent truncate
On 07/23/2015 05:08 PM, Rob Sargent wrote: On 07/23/2015 04:15 PM, Karsten Hilbert wrote: On Thu, Jul 23, 2015 at 12:28:32PM -0600, Rob Sargent wrote: I'm suggesting OP might find changing truncate statements to deletes (without a where clause) a simpler solution. Something has to change. Well, OP isn't looking for a solution to delete all rows but rather to _prevent_ deletion. Tim can't go forth and tell Blackhats to please use DELETE rather than TRUNCATE, right ? AFAICT it'd be more useful to advise OP to revoke TRUNCATE rights on tables. Karsten Not sure about Tim and the Blackhats (there's a band name in there somewhere) but Wouldn't OP have exact same code to fix, one way or another? I think the point was, the OP(Tim) might not have access to the code that is trying to TRUNCATE. This could be because it is coming from authorized users who are writing their own code or unauthorized users(Blackhats) who are trying to sneak code in. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
On Thu, Jul 23, 2015 at 12:57:20PM +0100, Tim Smith wrote: It is important to realize that a rule is really a command transformation mechanism, or command macro. The transformation happens before the execution of the command starts. If you actually want an operation that fires independently for each physical row, you probably want to use a trigger, not a rule Well, yes, but the discussion of the rules system in earlier manuals was actually, I thought, somewhat more detailed; and it outlined what rules really did, which was alter the command at the parse tree. That's what I think the above is saying also, but it may not be quite as plain. So it's rather more like a statement-level trigger. Thus, I should not have to use a trigger for TRUNCATE because the each row concept does not apply. Plus it makes perfect sense to want to transform the truncate command and transform into ignore Well, yes, but really in this case you want a per-statement trigger, and there's not the same distinction in rules, either. I can't believe that people would reject a patch (though you should ask on -hackers, not here); but you asked what was behind the design decision and I told you. But in general, the experience seems to be that triggers are easier to get right (novice or no, _pace_ section 38.7). Best regards, A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
On 07/23/2015 05:37 PM, Rob Sargent wrote: On 07/23/2015 06:27 PM, Adrian Klaver wrote: On 07/23/2015 05:08 PM, Rob Sargent wrote: On 07/23/2015 04:15 PM, Karsten Hilbert wrote: On Thu, Jul 23, 2015 at 12:28:32PM -0600, Rob Sargent wrote: I'm suggesting OP might find changing truncate statements to deletes (without a where clause) a simpler solution. Something has to change. Well, OP isn't looking for a solution to delete all rows but rather to _prevent_ deletion. Tim can't go forth and tell Blackhats to please use DELETE rather than TRUNCATE, right ? AFAICT it'd be more useful to advise OP to revoke TRUNCATE rights on tables. Karsten Not sure about Tim and the Blackhats (there's a band name in there somewhere) but Wouldn't OP have exact same code to fix, one way or another? I think the point was, the OP(Tim) might not have access to the code that is trying to TRUNCATE. This could be because it is coming from authorized users who are writing their own code or unauthorized users(Blackhats) who are trying to sneak code in. Fair enough but both blackhats and the authorized are just as likely to drop the database as truncate something (intentionally or not) and backups stashed everywhere is the first order of business. Well that is a different crisis and not covered by rules or triggers:) -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
On 07/23/2015 06:27 PM, Adrian Klaver wrote: On 07/23/2015 05:08 PM, Rob Sargent wrote: On 07/23/2015 04:15 PM, Karsten Hilbert wrote: On Thu, Jul 23, 2015 at 12:28:32PM -0600, Rob Sargent wrote: I'm suggesting OP might find changing truncate statements to deletes (without a where clause) a simpler solution. Something has to change. Well, OP isn't looking for a solution to delete all rows but rather to _prevent_ deletion. Tim can't go forth and tell Blackhats to please use DELETE rather than TRUNCATE, right ? AFAICT it'd be more useful to advise OP to revoke TRUNCATE rights on tables. Karsten Not sure about Tim and the Blackhats (there's a band name in there somewhere) but Wouldn't OP have exact same code to fix, one way or another? I think the point was, the OP(Tim) might not have access to the code that is trying to TRUNCATE. This could be because it is coming from authorized users who are writing their own code or unauthorized users(Blackhats) who are trying to sneak code in. Fair enough but both blackhats and the authorized are just as likely to drop the database as truncate something (intentionally or not) and backups stashed everywhere is the first order of business.
Re: [GENERAL] Delete rule does not prevent truncate
On Thu, Jul 23, 2015 at 12:28:32PM -0600, Rob Sargent wrote: I'm suggesting OP might find changing truncate statements to deletes (without a where clause) a simpler solution. Something has to change. Well, OP isn't looking for a solution to delete all rows but rather to _prevent_ deletion. Tim can't go forth and tell Blackhats to please use DELETE rather than TRUNCATE, right ? AFAICT it'd be more useful to advise OP to revoke TRUNCATE rights on tables. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
On 07/23/2015 11:15 AM, Rob Sargent wrote: On 07/23/2015 12:09 PM, Adrian Klaver wrote: On 07/23/2015 04:57 AM, Tim Smith wrote: Andrew, From the manual: It is important to realize that a rule is really a command transformation mechanism, or command macro. The transformation happens before the execution of the command starts. If you actually want an operation that fires independently for each physical row, you probably want to use a trigger, not a rule Thus, I should not have to use a trigger for TRUNCATE because the each row concept does not apply. Plus it makes perfect sense to want to transform the truncate command and transform into ignore Just in case it has not been made obvious yet, rules are silently deprecated. They still exist because views depend on them, but it is generally considered best practices to not use them outside that realm. So if you want the rule behavior to change for TRUNCATE(if that is even possible) you are fighting an uphill battle. You may pursue that fight of course, but I would think you will get a quicker return on your time if you just forget about using a RULE and stick to a TRIGGER instead. Or change to using delete instead of truncate? Well Tim has an ON DELETE rule: http://www.postgresql.org/message-id/CA+HuS5G2bZYYOGTJrw+VosjUPO298swxuU=jorfav54ut7v...@mail.gmail.com His expectation was that would also catch a TRUNCATE based on this: ... It has the same effect as an unqualified DELETE on each table, ... from here: http://www.postgresql.org/docs/9.4/interactive/sql-truncate.html It was then explained that while TRUNCATE had the same end result as 'DELETE FROM some_table' it was actually a separate command and action. Tim wants to catch a TRUNCATE and turn it into an ignore. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
On Thu, Jul 23, 2015 at 12:09 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/23/2015 04:57 AM, Tim Smith wrote: Andrew, From the manual: It is important to realize that a rule is really a command transformation mechanism, or command macro. The transformation happens before the execution of the command starts. If you actually want an operation that fires independently for each physical row, you probably want to use a trigger, not a rule Thus, I should not have to use a trigger for TRUNCATE because the each row concept does not apply. Plus it makes perfect sense to want to transform the truncate command and transform into ignore Just in case it has not been made obvious yet, rules are silently deprecated. They still exist because views depend on them, but it is generally considered best practices to not use them outside that realm. So if you want the rule behavior to change for TRUNCATE(if that is even possible) you are fighting an uphill battle. You may pursue that fight of course, but I would think you will get a quicker return on your time if you just forget about using a RULE and stick to a TRIGGER instead. Also OP needs to know that COPY commands are ignored by rules as well. I agree, stick to triggers, they're faster and less error prone. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
On 07/23/2015 12:25 PM, Adrian Klaver wrote: On 07/23/2015 11:15 AM, Rob Sargent wrote: On 07/23/2015 12:09 PM, Adrian Klaver wrote: On 07/23/2015 04:57 AM, Tim Smith wrote: Andrew, From the manual: It is important to realize that a rule is really a command transformation mechanism, or command macro. The transformation happens before the execution of the command starts. If you actually want an operation that fires independently for each physical row, you probably want to use a trigger, not a rule Thus, I should not have to use a trigger for TRUNCATE because the each row concept does not apply. Plus it makes perfect sense to want to transform the truncate command and transform into ignore Just in case it has not been made obvious yet, rules are silently deprecated. They still exist because views depend on them, but it is generally considered best practices to not use them outside that realm. So if you want the rule behavior to change for TRUNCATE(if that is even possible) you are fighting an uphill battle. You may pursue that fight of course, but I would think you will get a quicker return on your time if you just forget about using a RULE and stick to a TRIGGER instead. Or change to using delete instead of truncate? Well Tim has an ON DELETE rule: http://www.postgresql.org/message-id/CA+HuS5G2bZYYOGTJrw+VosjUPO298swxuU=jorfav54ut7v...@mail.gmail.com His expectation was that would also catch a TRUNCATE based on this: ... It has the same effect as an unqualified DELETE on each table, ... from here: http://www.postgresql.org/docs/9.4/interactive/sql-truncate.html It was then explained that while TRUNCATE had the same end result as 'DELETE FROM some_table' it was actually a separate command and action. Tim wants to catch a TRUNCATE and turn it into an ignore. I'm suggesting OP might find changing truncate statements to deletes (without a where clause) a simpler solution. Something has to change.
Re: [GENERAL] Delete rule does not prevent truncate
On 2015-07-23 12:57:20 +0100, Tim Smith wrote: Thus, I should not have to use a trigger for TRUNCATE because the each row concept does not apply. Plus it makes perfect sense to want to transform the truncate command and transform into ignore That'd entirely defeat the point of TRUNCATE being fast. Either way, this isn't going to change, so it seems a bit pointless to continue arguing around it circles. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
On 07/23/2015 04:57 AM, Tim Smith wrote: Andrew, From the manual: It is important to realize that a rule is really a command transformation mechanism, or command macro. The transformation happens before the execution of the command starts. If you actually want an operation that fires independently for each physical row, you probably want to use a trigger, not a rule Thus, I should not have to use a trigger for TRUNCATE because the each row concept does not apply. Plus it makes perfect sense to want to transform the truncate command and transform into ignore Just in case it has not been made obvious yet, rules are silently deprecated. They still exist because views depend on them, but it is generally considered best practices to not use them outside that realm. So if you want the rule behavior to change for TRUNCATE(if that is even possible) you are fighting an uphill battle. You may pursue that fight of course, but I would think you will get a quicker return on your time if you just forget about using a RULE and stick to a TRIGGER instead. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
On 07/23/2015 12:09 PM, Adrian Klaver wrote: On 07/23/2015 04:57 AM, Tim Smith wrote: Andrew, From the manual: It is important to realize that a rule is really a command transformation mechanism, or command macro. The transformation happens before the execution of the command starts. If you actually want an operation that fires independently for each physical row, you probably want to use a trigger, not a rule Thus, I should not have to use a trigger for TRUNCATE because the each row concept does not apply. Plus it makes perfect sense to want to transform the truncate command and transform into ignore Just in case it has not been made obvious yet, rules are silently deprecated. They still exist because views depend on them, but it is generally considered best practices to not use them outside that realm. So if you want the rule behavior to change for TRUNCATE(if that is even possible) you are fighting an uphill battle. You may pursue that fight of course, but I would think you will get a quicker return on your time if you just forget about using a RULE and stick to a TRIGGER instead. Or change to using delete instead of truncate?
Re: [GENERAL] Delete rule does not prevent truncate
Andrew, From the manual: It is important to realize that a rule is really a command transformation mechanism, or command macro. The transformation happens before the execution of the command starts. If you actually want an operation that fires independently for each physical row, you probably want to use a trigger, not a rule Thus, I should not have to use a trigger for TRUNCATE because the each row concept does not apply. Plus it makes perfect sense to want to transform the truncate command and transform into ignore On Thursday, 23 July 2015, Andrew Sullivan a...@crankycanuck.ca wrote: On Thu, Jul 23, 2015 at 08:06:19AM +0100, Tim Smith wrote: What exactly is was the design decision that lead to TRUNCATE being supported by triggers but not by rules ? There are two things. First, probably the design decision was, I care about triggers. TRUNCATE was added (I believe) in version 7.0, and even then there was some caution indicated about the use of rules. See for instance http://www.postgresql.org/docs/7.0/static/rules19784.htm. So you might be partly right. But second, it isn't clear what it would mean for TRUNCATE to be supported by rules. Rules do query parse tree rewriting. That is, they rewrite the query on the way through the system before they can possibly have any effect, changing one SQL statement into (effectively) a different one by the time it executes. There is only one possible effect from TRUNCATE, and that is to eliminate all the data in the table. I don't know what rewriting such a query would mean. Best regards, A -- Andrew Sullivan a...@crankycanuck.ca javascript:; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org javascript:;) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
So tell me guys, instead of bashing away at the fact I only quoted half a sentence or whatever, how about you answer the following : What exactly is was the design decision that lead to TRUNCATE being supported by triggers but not by rules ? I suspect that TRUNCATE was added to triggers because some dev thought it would be a neat idea, and it was never implemented in rules as a result of an accidental omission for whatever reason rather than a deliberate design constraint. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
2015-07-23 9:06 GMT+02:00 Tim Smith randomdev4+postg...@gmail.com: So tell me guys, instead of bashing away at the fact I only quoted half a sentence or whatever, how about you answer the following : What exactly is was the design decision that lead to TRUNCATE being supported by triggers but not by rules ? Someone had time to implement it for triggers, no-one had time for rules. I suspect that TRUNCATE was added to triggers because some dev thought it would be a neat idea, and it was never implemented in rules as a result of an accidental omission for whatever reason rather than a deliberate design constraint. It is a neat idea for tiggers. Slony uses that to replicate TRUNCATE on slaves of a Slony cluster. It wouldn't be such a neat idea for rules as, IIRC, rules are only supported because views are based on them. Without that, they would probably be ripped out of the code. -- Guillaume. http://blog.guillaume.lelarge.info http://www.dalibo.com
Re: [GENERAL] Delete rule does not prevent truncate
On Thu, Jul 23, 2015 at 08:06:19AM +0100, Tim Smith wrote: What exactly is was the design decision that lead to TRUNCATE being supported by triggers but not by rules ? There are two things. First, probably the design decision was, I care about triggers. TRUNCATE was added (I believe) in version 7.0, and even then there was some caution indicated about the use of rules. See for instance http://www.postgresql.org/docs/7.0/static/rules19784.htm. So you might be partly right. But second, it isn't clear what it would mean for TRUNCATE to be supported by rules. Rules do query parse tree rewriting. That is, they rewrite the query on the way through the system before they can possibly have any effect, changing one SQL statement into (effectively) a different one by the time it executes. There is only one possible effect from TRUNCATE, and that is to eliminate all the data in the table. I don't know what rewriting such a query would mean. Best regards, A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
On 07/22/2015 06:24 AM, Tim Smith wrote: Adrian, It still doesn't make much sense, especially as given the rather obscure and questionable design decision of allowing triggers to refer to truncate ops, but not allowing rules to refer to truncate ops !!! Actually it makes perfect sense because rules are a feature for compatibility (at this point) more than anything else. They are slower than triggers, less flexible and widely considered something you only use in very rare circumstances. That and of course, patches are accepted if you feel it is a feature worth having. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
On 22 July 2015 at 16:32, Joshua D. Drake j...@commandprompt.com wrote: This is actually wrong. The end result is the same but it does not in any way have the same effect. in any way? I'd say in the primary way it has the same effect: all rows are removed from the table. And I will submit a patch. As long as the patch is , although your attention should be brought to the caveats listed below, since you're obviously incapable of realis ing that there's a page and a half of information beneath this sentence? TRUNCATE is NOT DELETE. I don't think anyone is suggesting that it is. Otherwise there wouldn't be much point having it. Geoff
Re: [GENERAL] Delete rule does not prevent truncate
On 22 July 2015 at 16:55, Joshua D. Drake j...@commandprompt.com wrote: On 07/22/2015 08:42 AM, Geoff Winkless wrote: On 22 July 2015 at 16:32, Joshua D. Drake j...@commandprompt.com mailto:j...@commandprompt.comwrote: This is actually wrong. The end result is the same but it does not in any way have the same effect. in any way? I'd say in the primary way it has the same effect: all rows are removed from the table. Thus the end result is the same as I said but the in practice effect is quite different from a visibility, maintenance and programmability perspective. But to say it does not in any way have the same effect explicitly excludes that any effect of the two things might be the same. In actual fact, in the simple case (no triggers) the effect *is *the same. FWIW, the difference between in practice effect and end result is pretty esoteric, frankly - I'm not really sure what you're driving at there. The end result would surely include the behaviour of any triggers that might be fired. TRUNCATE is NOT DELETE. I don't think anyone is suggesting that it is. Except Tim Smith who started this thread. I don't believe he suggested that at all: he made an assumption that his DELETE triggers would fire on TRUNCATE, and when it was pointed out that he was mistaken he posted *half* a sentence from the documentation that supported his view, ignoring the second half of that sentence that makes it clear that the behaviour of the two commands is different, and ignoring the explicit statement *on the very same page *of the documentation viz: TRUNCATE will not fire any ON DELETE triggers that might exist for the tables . Geoff
Re: [GENERAL] Delete rule does not prevent truncate
On 07/22/2015 06:13 AM, Tim Smith wrote: Melvin, May I point out that the manual states : TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table This is actually wrong. The end result is the same but it does not in any way have the same effect. And I will submit a patch. TRUNCATE is NOT DELETE. Sincerely, JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
On 07/22/2015 08:42 AM, Geoff Winkless wrote: On 22 July 2015 at 16:32, Joshua D. Drake j...@commandprompt.com mailto:j...@commandprompt.comwrote: This is actually wrong. The end result is the same but it does not in any way have the same effect. in any way? I'd say in the primary way it has the same effect: all rows are removed from the table. Thus the end result is the same as I said but the in practice effect is quite different from a visibility, maintenance and programmability perspective. TRUNCATE is NOT DELETE. I don't think anyone is suggesting that it is. Except Tim Smith who started this thread. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
No, I am saying if you CREATE PROCEDURE do_nothing() RETURNS VOID $BODY$ BEGIN RETURN; END LANGUAGE plpgsql; CREATE TRIGGER no_trunc INSTEAD OF TRUNCATE ON your_table EXECUTE PROCEDURE do_nothing; Then you can handle the problem. You should also create a TRIGGER for DELETE to do the same. On Wed, Jul 22, 2015 at 9:13 AM, Tim Smith randomdev4+postg...@gmail.com wrote: Melvin, May I point out that the manual states : TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table Thus, if you are telling me to effectively think of TRUNCATE as an alias to DELETE, then I would think its not entirely unreasonable of me to expect a rule preventing DELETE to also cover truncate, since the rule would no doubt prevent an unqualified DELETE, would it not ?!? On 22 July 2015 at 14:03, Melvin Davidson melvin6...@gmail.com wrote: Actually, if you use a TRIGGER instead of rule, you can handle this. The manual states event can be: INSERT UPDATE [ OF column_name [, ... ] ] DELETE*TRUNCATE -* http://www.postgresql.org/docs/9.4/interactive/sql-createtrigger.html I suggest you review carefully. On Wed, Jul 22, 2015 at 8:53 AM, Tim Smith randomdev4+postg...@gmail.com wrote: Hi, I very much hope this is an accidental bug rather than a deliberate feature ! PostgreSQL 9.4.4 create rule no_auditupd as on update to app_security.app_audit do instead nothing; create rule no_auditdel as on delete to app_security.app_audit do instead nothing; \d+ app_security.app_audit snip Rules: no_auditdel AS ON DELETE TO app_security.app_audit DO INSTEAD NOTHING no_auditupd AS ON UPDATE TO app_security.app_audit DO INSTEAD NOTHING The truncate trashes the whole table ;-( According to the FabulousManual(TM) : event : The event is one of SELECT, INSERT, UPDATE, or DELETE. Thus I can't create a rule to do nothing on truncates, thus I am stuck ! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] Delete rule does not prevent truncate
On 07/22/2015 06:13 AM, Tim Smith wrote: Melvin, May I point out that the manual states : TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table Thus, if you are telling me to effectively think of TRUNCATE as an alias to DELETE, then I would think its not entirely unreasonable of me to expect a rule preventing DELETE to also cover truncate, since the rule would no doubt prevent an unqualified DELETE, would it not ?!? If you go further down into the Notes section you find: TRUNCATE will not fire any ON DELETE triggers that might exist for the tables. But it will fire ON TRUNCATE triggers. If ON TRUNCATE triggers are defined for any of the tables, then all BEFORE TRUNCATE triggers are fired before any truncation happens, and all AFTER TRUNCATE triggers are fired after the last truncation is performed and any sequences are reset. The triggers will fire in the order that the tables are to be processed (first those listed in the command, and then any that were added due to cascading). Warning TRUNCATE is not MVCC-safe (see Chapter 13 for general information about MVCC). After truncation, the table will appear empty to all concurrent transactions, even if they are using a snapshot taken before the truncation occurred. This will only be an issue for a transaction that did not access the truncated table before the truncation happened — any transaction that has done so would hold at least an ACCESS SHARE lock, which would block TRUNCATE until that transaction completes. So truncation will not cause any apparent inconsistency in the table contents for successive queries on the same table, but it could cause visible inconsistency between the contents of the truncated table and other tables in the database. TRUNCATE is when you want fast over safety. On 22 July 2015 at 14:03, Melvin Davidson melvin6...@gmail.com mailto:melvin6...@gmail.com wrote: Actually, if you use a TRIGGER instead of rule, you can handle this. The manual states event can be: INSERT UPDATE [ OFcolumn_name [, ... ] ] DELETE *TRUNCATE -* http://www.postgresql.org/docs/9.4/interactive/sql-createtrigger.html I suggest you review carefully. On Wed, Jul 22, 2015 at 8:53 AM, Tim Smith randomdev4+postg...@gmail.com mailto:randomdev4+postg...@gmail.com wrote: Hi, I very much hope this is an accidental bug rather than a deliberate feature ! PostgreSQL 9.4.4 create rule no_auditupd as on update to app_security.app_audit do instead nothing; create rule no_auditdel as on delete to app_security.app_audit do instead nothing; \d+ app_security.app_audit snip Rules: no_auditdel AS ON DELETE TO app_security.app_audit DO INSTEAD NOTHING no_auditupd AS ON UPDATE TO app_security.app_audit DO INSTEAD NOTHING The truncate trashes the whole table ;-( According to the FabulousManual(TM) : event : The event is one of SELECT, INSERT, UPDATE, or DELETE. Thus I can't create a rule to do nothing on truncates, thus I am stuck ! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
Adrian, It still doesn't make much sense, especially as given the rather obscure and questionable design decision of allowing triggers to refer to truncate ops, but not allowing rules to refer to truncate ops !!! Surely either you say look, truncate is truncate, its there for one purpose and one purpose only.Or otherwise, you should handle it consistently across the database, i.e. if you're going to allow triggers interact with truncates, then you should allow rules to interact with truncates.It really doesn't make much sense to adopt a pick and choose mentality ! On 22 July 2015 at 14:19, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/22/2015 06:13 AM, Tim Smith wrote: Melvin, May I point out that the manual states : TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table Thus, if you are telling me to effectively think of TRUNCATE as an alias to DELETE, then I would think its not entirely unreasonable of me to expect a rule preventing DELETE to also cover truncate, since the rule would no doubt prevent an unqualified DELETE, would it not ?!? If you go further down into the Notes section you find: TRUNCATE will not fire any ON DELETE triggers that might exist for the tables. But it will fire ON TRUNCATE triggers. If ON TRUNCATE triggers are defined for any of the tables, then all BEFORE TRUNCATE triggers are fired before any truncation happens, and all AFTER TRUNCATE triggers are fired after the last truncation is performed and any sequences are reset. The triggers will fire in the order that the tables are to be processed (first those listed in the command, and then any that were added due to cascading). Warning TRUNCATE is not MVCC-safe (see Chapter 13 for general information about MVCC). After truncation, the table will appear empty to all concurrent transactions, even if they are using a snapshot taken before the truncation occurred. This will only be an issue for a transaction that did not access the truncated table before the truncation happened — any transaction that has done so would hold at least an ACCESS SHARE lock, which would block TRUNCATE until that transaction completes. So truncation will not cause any apparent inconsistency in the table contents for successive queries on the same table, but it could cause visible inconsistency between the contents of the truncated table and other tables in the database. TRUNCATE is when you want fast over safety. On 22 July 2015 at 14:03, Melvin Davidson melvin6...@gmail.com mailto:melvin6...@gmail.com wrote: Actually, if you use a TRIGGER instead of rule, you can handle this. The manual states event can be: INSERT UPDATE [ OFcolumn_name [, ... ] ] DELETE *TRUNCATE -* http://www.postgresql.org/docs/9.4/interactive/sql-createtrigger.html I suggest you review carefully. On Wed, Jul 22, 2015 at 8:53 AM, Tim Smith randomdev4+postg...@gmail.com mailto:randomdev4+postg...@gmail.com wrote: Hi, I very much hope this is an accidental bug rather than a deliberate feature ! PostgreSQL 9.4.4 create rule no_auditupd as on update to app_security.app_audit do instead nothing; create rule no_auditdel as on delete to app_security.app_audit do instead nothing; \d+ app_security.app_audit snip Rules: no_auditdel AS ON DELETE TO app_security.app_audit DO INSTEAD NOTHING no_auditupd AS ON UPDATE TO app_security.app_audit DO INSTEAD NOTHING The truncate trashes the whole table ;-( According to the FabulousManual(TM) : event : The event is one of SELECT, INSERT, UPDATE, or DELETE. Thus I can't create a rule to do nothing on truncates, thus I am stuck ! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
On 07/22/2015 06:24 AM, Tim Smith wrote: Adrian, It still doesn't make much sense, especially as given the rather obscure and questionable design decision of allowing triggers to refer to truncate ops, but not allowing rules to refer to truncate ops !!! Surely either you say look, truncate is truncate, its there for one purpose and one purpose only.Or otherwise, you should handle it consistently across the database, i.e. if you're going to allow triggers interact with truncates, then you should allow rules to interact with truncates.It really doesn't make much sense to adopt a pick and choose mentality ! All I know is that TRUNCATE is a shortcut and RULEs do not understand it and TRIGGERs do. My guess is the answer somewhere in here: http://www.postgresql.org/docs/9.4/interactive/rules.html Word of advice, take two aspirin before reading above. At any rate, I have personally found using triggers results in less surprises then using rules. On 22 July 2015 at 14:19, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/22/2015 06:13 AM, Tim Smith wrote: Melvin, May I point out that the manual states : TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table Thus, if you are telling me to effectively think of TRUNCATE as an alias to DELETE, then I would think its not entirely unreasonable of me to expect a rule preventing DELETE to also cover truncate, since the rule would no doubt prevent an unqualified DELETE, would it not ?!? If you go further down into the Notes section you find: TRUNCATE will not fire any ON DELETE triggers that might exist for the tables. But it will fire ON TRUNCATE triggers. If ON TRUNCATE triggers are defined for any of the tables, then all BEFORE TRUNCATE triggers are fired before any truncation happens, and all AFTER TRUNCATE triggers are fired after the last truncation is performed and any sequences are reset. The triggers will fire in the order that the tables are to be processed (first those listed in the command, and then any that were added due to cascading). Warning TRUNCATE is not MVCC-safe (see Chapter 13 for general information about MVCC). After truncation, the table will appear empty to all concurrent transactions, even if they are using a snapshot taken before the truncation occurred. This will only be an issue for a transaction that did not access the truncated table before the truncation happened — any transaction that has done so would hold at least an ACCESS SHARE lock, which would block TRUNCATE until that transaction completes. So truncation will not cause any apparent inconsistency in the table contents for successive queries on the same table, but it could cause visible inconsistency between the contents of the truncated table and other tables in the database. TRUNCATE is when you want fast over safety. On 22 July 2015 at 14:03, Melvin Davidson melvin6...@gmail.com mailto:melvin6...@gmail.com wrote: Actually, if you use a TRIGGER instead of rule, you can handle this. The manual states event can be: INSERT UPDATE [ OFcolumn_name [, ... ] ] DELETE *TRUNCATE -* http://www.postgresql.org/docs/9.4/interactive/sql-createtrigger.html I suggest you review carefully. On Wed, Jul 22, 2015 at 8:53 AM, Tim Smith randomdev4+postg...@gmail.com mailto:randomdev4+postg...@gmail.com wrote: Hi, I very much hope this is an accidental bug rather than a deliberate feature ! PostgreSQL 9.4.4 create rule no_auditupd as on update to app_security.app_audit do instead nothing; create rule no_auditdel as on delete to app_security.app_audit do instead nothing; \d+ app_security.app_audit snip Rules: no_auditdel AS ON DELETE TO app_security.app_audit DO INSTEAD NOTHING no_auditupd AS ON UPDATE TO app_security.app_audit DO INSTEAD NOTHING The truncate trashes the whole table ;-( According to the FabulousManual(TM) : event : The event is one of SELECT, INSERT, UPDATE, or DELETE. Thus I can't create a rule to do nothing on truncates, thus I am stuck ! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you. -- Adrian Klaver adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete rule does not prevent truncate
Actually, if you use a TRIGGER instead of rule, you can handle this. The manual states event can be: INSERT UPDATE [ OF column_name [, ... ] ] DELETE*TRUNCATE -* http://www.postgresql.org/docs/9.4/interactive/sql-createtrigger.html I suggest you review carefully. On Wed, Jul 22, 2015 at 8:53 AM, Tim Smith randomdev4+postg...@gmail.com wrote: Hi, I very much hope this is an accidental bug rather than a deliberate feature ! PostgreSQL 9.4.4 create rule no_auditupd as on update to app_security.app_audit do instead nothing; create rule no_auditdel as on delete to app_security.app_audit do instead nothing; \d+ app_security.app_audit snip Rules: no_auditdel AS ON DELETE TO app_security.app_audit DO INSTEAD NOTHING no_auditupd AS ON UPDATE TO app_security.app_audit DO INSTEAD NOTHING The truncate trashes the whole table ;-( According to the FabulousManual(TM) : event : The event is one of SELECT, INSERT, UPDATE, or DELETE. Thus I can't create a rule to do nothing on truncates, thus I am stuck ! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] Delete rule does not prevent truncate
Melvin, May I point out that the manual states : TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table Thus, if you are telling me to effectively think of TRUNCATE as an alias to DELETE, then I would think its not entirely unreasonable of me to expect a rule preventing DELETE to also cover truncate, since the rule would no doubt prevent an unqualified DELETE, would it not ?!? On 22 July 2015 at 14:03, Melvin Davidson melvin6...@gmail.com wrote: Actually, if you use a TRIGGER instead of rule, you can handle this. The manual states event can be: INSERT UPDATE [ OF column_name [, ... ] ] DELETE*TRUNCATE -* http://www.postgresql.org/docs/9.4/interactive/sql-createtrigger.html I suggest you review carefully. On Wed, Jul 22, 2015 at 8:53 AM, Tim Smith randomdev4+postg...@gmail.com wrote: Hi, I very much hope this is an accidental bug rather than a deliberate feature ! PostgreSQL 9.4.4 create rule no_auditupd as on update to app_security.app_audit do instead nothing; create rule no_auditdel as on delete to app_security.app_audit do instead nothing; \d+ app_security.app_audit snip Rules: no_auditdel AS ON DELETE TO app_security.app_audit DO INSTEAD NOTHING no_auditupd AS ON UPDATE TO app_security.app_audit DO INSTEAD NOTHING The truncate trashes the whole table ;-( According to the FabulousManual(TM) : event : The event is one of SELECT, INSERT, UPDATE, or DELETE. Thus I can't create a rule to do nothing on truncates, thus I am stuck ! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
