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

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

Re: [GENERAL] Delete rule does not prevent truncate

On 23 July 2015 at 19:25, Scott Marlowe 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 t

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 TRU

Re: [GENERAL] Delete rule does not prevent truncate

On Fri, Jul 24, 2015 at 3:27 AM, Tim Smith wrote: > On 23 July 2015 at 19:25, Scott Marlowe 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"

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

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,

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. Andre

Re: [GENERAL] Delete rule does not prevent truncate

On 24 July 2015 at 01:37, Rob Sargent 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,

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 statem

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 s

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. Wel

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 "delet

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.

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 comm

Re: [GENERAL] Delete rule does not prevent truncate

On Thu, Jul 23, 2015 at 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

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 t

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 a

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 independe

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

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 wa

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

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 ve

Re: [GENERAL] Delete rule does not prevent truncate

2015-07-23 9:06 GMT+02:00 Tim Smith : > 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 ? > > Some

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 s

Re: [GENERAL] Delete rule does not prevent truncate

On 22 July 2015 at 16:55, Joshua D. Drake wrote: > > On 07/22/2015 08:42 AM, Geoff Winkless wrote: > >> On 22 July 2015 at 16:32, Joshua D. Drake > >wrote: >> >> This is actually wrong. The end result is the same but it does not >> in any way have the same e

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 mailto: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 sam

Re: [GENERAL] Delete rule does not prevent truncate

On 22 July 2015 at 16:32, Joshua D. Drake 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. >

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 becaus

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

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

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

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

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, t

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 unreason

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