Re: Delete rule does not prevent truncate - Mailing list pgsql-general

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.klaver@aklaver.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@gmail.com
>>> <mailto:melvin6925@gmail.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@gmail.com
>>>      <mailto:randomdev4+postgres@gmail.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@postgresql.org
>>> <mailto:pgsql-general@postgresql.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.klaver@aklaver.com
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com