Re: Delete rule does not prevent truncate

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

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.