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+postgres@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,

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 <melvin6925@gmail.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 <melvin6925@gmail.com
<mailto:melvin6925@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+postgres@gmail.com
<mailto:randomdev4+postgres@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.klaver@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

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.klaver@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 <melvin6925@gmail.com
<mailto:melvin6925@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+postgres@gmail.com
<mailto:randomdev4+postgres@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.klaver@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

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+postgres@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 <melvin6925@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+postgres@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.

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.klaver@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 <melvin6925@gmail.com
<mailto:melvin6925@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+postgres@gmail.com
<mailto:randomdev4+postgres@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.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@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

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

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

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

On 07/22/2015 08:42 AM, Geoff Winkless wrote:

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

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

On 22 July 2015 at 16:55, Joshua D. Drake <jd@commandprompt.com> wrote:

On 07/22/2015 08:42 AM, Geoff Winkless wrote:

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

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​

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

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

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
ajs@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

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 <ajs@crankycanuck.ca> wrote:

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
ajs@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

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

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.klaver@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

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?