Re: Delete rule does not prevent truncate

Adrian,

It still doesn't make much sense, especially as given the rather
obscure and questionable design decision of allowing triggers to refer
to truncate ops, but not allowing rules to refer to truncate ops !!!

Surely either you say "look, truncate is truncate, its there for one
purpose and one purpose only". Or otherwise, you should handle it
consistently across the database, i.e. if you're going to allow
triggers interact with truncates, then you should allow rules to
interact with truncates. It really doesn't make much sense to adopt
a pick and choose mentality !

On 22 July 2015 at 14:19, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
> On 07/22/2015 06:13 AM, Tim Smith 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 ?!?
>
>
> If you go further down into the Notes section you find:
>
> "TRUNCATE will not fire any ON DELETE triggers that might exist for the
> tables. But it will fire ON TRUNCATE triggers. If ON TRUNCATE triggers are
> defined for any of the tables, then all BEFORE TRUNCATE triggers are fired
> before any truncation happens, and all AFTER TRUNCATE triggers are fired
> after the last truncation is performed and any sequences are reset. The
> triggers will fire in the order that the tables are to be processed (first
> those listed in the command, and then any that were added due to cascading).
> Warning
>
> TRUNCATE is not MVCC-safe (see Chapter 13 for general information about
> MVCC). After truncation, the table will appear empty to all concurrent
> transactions, even if they are using a snapshot taken before the truncation
> occurred. This will only be an issue for a transaction that did not access
> the truncated table before the truncation happened — any transaction that
> has done so would hold at least an ACCESS SHARE lock, which would block
> TRUNCATE until that transaction completes. So truncation will not cause any
> apparent inconsistency in the table contents for successive queries on the
> same table, but it could cause visible inconsistency between the contents of
> the truncated table and other tables in the database.
>
> "
>
>
> TRUNCATE is when you want fast over safety.
>
>>
>> On 22 July 2015 at 14:03, Melvin Davidson <melvin6925(at)gmail(dot)com
>> <mailto: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 [ OFcolumn_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
>> <mailto: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
>> <mailto: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.
>>
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com