Re: Delete rule does not prevent truncate

[Tim Smith <randomdev4+postgres@xxxxxxxxx>]

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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
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.