Postgres Pro
Downloads
Home   >   mailing lists

Re: Delete rule does not prevent truncate - Mailing list pgsql-general

From Melvin Davidson
Subject Re: Delete rule does not prevent truncate
Date
Msg-id CANu8FiyWW_5BimnyZLkrDNcq2z-Jxt5+isBzSwnXpay77tnk=w@mail.gmail.com
Whole thread Raw
In response to Delete rule does not prevent truncate  (Tim Smith <randomdev4+postgres@gmail.com>)
Responses Re: Delete rule does not prevent truncate  (Tim Smith <randomdev4+postgres@gmail.com>)
List pgsql-general
Tree view
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.

pgsql-general by date:

Previous
From: Tim Smith
Date:
Subject: Delete rule does not prevent truncate
Next
From: Tim Smith
Date:
Subject: Re: Delete rule does not prevent truncate