Re: 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 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(at)gmail(dot)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(at)gmail(dot)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(at)postgresql(dot)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.
>