Re: Delete rule does not prevent truncate

On 07/23/2015 11:15 AM, Rob Sargent wrote:
> On 07/23/2015 12:09 PM, Adrian Klaver wrote:
>> On 07/23/2015 04:57 AM, Tim Smith wrote:
>>> Andrew,
>>>
>>> From the manual:
>>>
>>> It is important to realize that a rule is really a command
>>> transformation mechanism, or command macro. The transformation happens
>>> before the execution of the command starts. If you actually want an
>>> operation that fires independently for each physical row, you probably
>>> want to use a trigger, not a rule
>>>
>>>
>>> Thus, I should not have to use a trigger for TRUNCATE because the "each
>>> row" concept does not apply. Plus it makes perfect sense to want to
>>> transform the truncate command and transform into ignore
>>>
>>
>> Just in case it has not been made obvious yet, rules are silently
>> deprecated. They still exist because views depend on them, but it is
>> generally considered best practices to not use them outside that
>> realm. So if you want the rule behavior to change for TRUNCATE(if that
>> is even possible) you are fighting an uphill battle. You may pursue
>> that fight of course, but I would think you will get a quicker return
>> on your time if you just forget about using a RULE and stick to a
>> TRIGGER instead.
>>
> Or change to using delete instead of truncate?
>

Well Tim has an ON DELETE rule:

http://www.postgresql.org/message-id/CA+HuS5G2bZYYOGTJrw+VosjUPO298swxuU=JOrFAv54UT7vniQ@mail.gmail.com

His expectation was that would also catch a TRUNCATE based on this:

"... It has the same effect as an unqualified DELETE on each table, ..."

from here:

http://www.postgresql.org/docs/9.4/interactive/sql-truncate.html

It was then explained that while TRUNCATE had the same end result as
'DELETE FROM some_table' it was actually a separate command and action.
Tim wants to catch a TRUNCATE and turn it into an ignore.

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