Re: Delete rule does not prevent truncate

From: Tim Smith <randomdev4(at)gmail(dot)com>
To: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Delete rule does not prevent truncate
Date: 2015-07-23 11:57:20
Message-ID: CA+HuS5GOeeT8BQ13L7VtbOcOd19Cg=E=YRTh3NHrXDZoaUjxWw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

On Thursday, 23 July 2015, Andrew Sullivan <ajs(at)crankycanuck(dot)ca> wrote:

> On Thu, Jul 23, 2015 at 08:06:19AM +0100, Tim Smith wrote:
> > What exactly is was the design decision that lead to TRUNCATE being
> > supported by triggers but not by rules ?
>
> There are two things. First, probably the design decision was, "I
> care about triggers." TRUNCATE was added (I believe) in version 7.0,
> and even then there was some caution indicated about the use of rules.
> See for instance
> http://www.postgresql.org/docs/7.0/static/rules19784.htm. So you
> might be partly right.
>
> But second, it isn't clear what it would mean for TRUNCATE to be
> supported by rules. Rules do query parse tree rewriting. That is,
> they rewrite the query on the way through the system before they can
> possibly have any effect, changing one SQL statement into
> (effectively) a different one by the time it executes. There is only
> one possible effect from TRUNCATE, and that is to eliminate all the
> data in the table. I don't know what rewriting such a query would
> mean.
>
> Best regards,
>
> A
>
> --
> Andrew Sullivan
> ajs(at)crankycanuck(dot)ca <javascript:;>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org
> <javascript:;>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrew Sullivan 2015-07-23 12:29:31 Re: Delete rule does not prevent truncate
Previous Message Heikki Linnakangas 2015-07-23 11:36:14 Re: Lots of stuck queries after upgrade to 9.4