Re: Delete rule does not prevent truncate

[Melvin Davidson <melvin6925@xxxxxxxxx>]

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.