Re: Delete rule does not prevent truncate

On 07/22/2015 06:24 AM, Tim Smith wrote:
> 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 !

All I know is that TRUNCATE is a shortcut and RULEs do not understand it
and TRIGGERs do. My guess is the answer somewhere in here:

http://www.postgresql.org/docs/9.4/interactive/rules.html

Word of advice, take two aspirin before reading above.

At any rate, I have personally found using triggers results in less
surprises then using rules.

>
> 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
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com