Thread: feature proposal - triggers by semantics

I have a feature request, which at one level should require little code change, 
but at another level may require more.

Since Postgres 9.3 is going to be doing some significant feature additions for 
triggers, I'd like to see some more.

As they currently exist, triggers always fire based on certain SQL syntax used, 
rather than on the semantics of what is actually going on.

I would like to see a new class of triggers that fire when particular database 
operations happen regardless of what SQL syntax was used.

As a simple example, I'd like to be able to define a trigger like "AFTER DELETE 
ON foo FOR EACH ROW" and have that trigger be invoked not only by a DELETE on 
foo but also by a TRUNCATE on foo.  So I would like to do some auditing action 
when a row of foo is deleted, no matter how it happens.

The reason this particular example in particular is important is that TRUNCATE 
is documented as a data-manipulation action semantically equivalent to an 
unqualified DELETE in its effects, primarily.  As such, I would expect the same 
triggers to fire as would for an unqualified DELETE.

The reason I propose it be a new kind of trigger is so that then we also retain 
the ability to declare triggers that fire on DELETE and not on TRUNCATE.

Less important, but also nice at least from the ability to be less verbose, is 
that said trigger could also run when an UPDATE happens, optionally, since an 
UPDATE can be considered semantically a DELETE+INSERT.  But adding the TRUNCATE 
support is most important because it simply doesn't exist now, while UPDATE you 
can get just by adding "or update".

I suggest that the simplest way to add this feature is to just extend the 
existing syntax for defining a FOR EACH ROW so that TRUNCATE is also an option, 
besides INSERT/UPDATE/DELETE.

In that case, the semantics of the TRUNCATE statement could be altered as 
follows:  Iff "TRUNCATE foo" is invoked and foo has an "TRUNCATE FOR EACH ROW" 
trigger defined on it, then an unqualified "DELETE FROM foo" will be performed 
instead with its usual semantics.  If such a trigger is not defined on foo, then 
the old TRUNCATE semantics happen.

As such, this case of the feature can be added without breaking anything legacy.

So, I'm partly proposing a specific narrow new feature, "TRUNCATE FOR EACH ROW", 
but I'm also proposing the ability to generally define triggers based not on the 
syntax used but the actual action requested.

A tangential feature request is to provide a runtime config option that can 
cause TRUNCATE to always behave as unqualified DELETE FROM regardless of any 
triggers, as if it were just a syntactic shorthand.  Or alternately/also provide 
extra syntax to TRUNCATE itself where one can specify which behavior to have, 
and both options can be given explicitly to override any config option.

-- Darren Duncan

On 11/15/2012 03:44 PM, Darren Duncan wrote:
> As they currently exist, triggers always fire based on certain SQL
> syntax used, rather than on the semantics of what is actually going on.
>
That's not quite right. COPY fires INSERT triggers, despite never using
an explicit INSERT statement. There are probably other examples.
> As a simple example, I'd like to be able to define a trigger like
> "AFTER DELETE ON foo FOR EACH ROW" and have that trigger be invoked
> not only by a DELETE on foo but also by a TRUNCATE on foo.  So I would
> like to do some auditing action when a row of foo is deleted, no
> matter how it happens.
TRUNCATE triggers already exist.

If you wanted you could have a BEFORE TRUNCATE trigger SELECT each row
and invoke a function on each row, same as a FOR EACH ROW .. DELETE
trigger. So you can already achieve what you want to do with the
currently available features.

> The reason this particular example in particular is important is that
> TRUNCATE is documented as a data-manipulation action semantically
> equivalent to an unqualified DELETE in its effects, primarily.
I'd say we should just change the docs to note that "TRUNCATE does not
act on each row individually, so it does not fire DELETE triggers. There
is a TRUNCATE trigger you can use to monitor and control TRUNCATE
behaviour."

>
> So, I'm partly proposing a specific narrow new feature, "TRUNCATE FOR
> EACH ROW", but I'm also proposing the ability to generally define
> triggers based not on the syntax used but the actual action requested.
That already exists. Maybe you're using some old version?

regress=> \h CREATE TRIGGER
Command:     CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } {
event [ OR ... ] }   ON table_name
...
where event can be one of:
   INSERT   UPDATE [ OF column_name [, ... ] ]   DELETE   TRUNCATE


>
> A tangential feature request is to provide a runtime config option
> that can cause TRUNCATE to always behave as unqualified DELETE FROM
> regardless of any triggers, as if it were just a syntactic shorthand.
Please, no!

If you want to prevent TRUNCATE, deny the privilege or add a trigger
that aborts the command.

TRUNCATE should do what it says, not magically be changed to do
something else by a GUC.
> Or alternately/also provide extra syntax to TRUNCATE itself where one
> can specify which behavior to have, and both options can be given
> explicitly to override any config option.
What advantage would that have over using DELETE when you want DELETE,
and TRUNCATE when you want TRUNCATE?

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services

A row-level trigger for TRUNCATE does not really make sense, as it would mean that TRUNCATE needs to scan each tuple of
thetable it needs to interact with to fire its trigger, so it would more or less achieve the same performance as a
plain"DELETE FROM table;".<br /><br />TRUNCATE is performant because it only removes the tuples, and does not care
aboutscanning. I am also not sure it is meant for scanning (btw there are discussions about TRUNCATE these days so I
mightbe missing something).<br />So, what you are looking for can be simply solved with row-level triggers on DELETE,
sowhy not using that and avoid reinventing the wheel?<br />-- <br />Michael Paquier<br /><a
href="http://michael.otacoo.com"target="_blank">http://michael.otacoo.com</a><br /> 

On 11/15/2012 09:48 AM, Craig Ringer wrote:
> If you want to prevent TRUNCATE, deny the privilege or add a trigger
> that aborts the command.
You can abort the transaction but not skip action as currently it is only
possible to skip in ROW level triggers.

So I'd modify this request to allow BEFORE EACH STATEMENT triggers
to also be able to silently skip current action like BEFORE EACH ROW 
triggers can.

Then this request would simply be satisfied by a simple trigger which
rewrites TRUNCATE into DELETE .

Hannu

>
> TRUNCATE should do what it says, not magically be changed to do
> something else by a GUC.
>> Or alternately/also provide extra syntax to TRUNCATE itself where one
>> can specify which behavior to have, and both options can be given
>> explicitly to override any config option.
> What advantage would that have over using DELETE when you want DELETE,
> and TRUNCATE when you want TRUNCATE?
>

On 11/15/2012 06:25 PM, Hannu Krosing wrote:
> On 11/15/2012 09:48 AM, Craig Ringer wrote:
>> If you want to prevent TRUNCATE, deny the privilege or add a trigger
>> that aborts the command.
> You can abort the transaction but not skip action as currently it is only
> possible to skip in ROW level triggers.
>
> So I'd modify this request to allow BEFORE EACH STATEMENT triggers
> to also be able to silently skip current action like BEFORE EACH ROW
> triggers can.
>
> Then this request would simply be satisfied by a simple trigger which
> rewrites TRUNCATE into DELETE .
That seems sensible to me, too.

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services

Craig Ringer wrote:
> On 11/15/2012 06:25 PM, Hannu Krosing wrote:
>> On 11/15/2012 09:48 AM, Craig Ringer wrote:
>>> If you want to prevent TRUNCATE, deny the privilege or add a trigger
>>> that aborts the command.
>> You can abort the transaction but not skip action as currently it is only
>> possible to skip in ROW level triggers.
>>
>> So I'd modify this request to allow BEFORE EACH STATEMENT triggers
>> to also be able to silently skip current action like BEFORE EACH ROW
>> triggers can.
>>
>> Then this request would simply be satisfied by a simple trigger which
>> rewrites TRUNCATE into DELETE .
> That seems sensible to me, too.

To further explain ...

What I'm desiring here with respect to TRUNCATE is that users are allowed to use 
TRUNCATE syntax as an alternative to DELETE (they are GRANTed both) but that any 
triggers defined for DELETE can also be applied to TRUNCATE without too much 
difficulty.  So users can use different syntactic constructs that look similar 
without having to think about, is this going to be audited.

I understand that ROW level triggers don't exist yet for TRUNCATE (this is 
already clearly documented in the manual) and adding them would mean TRUNCATE 
would do a table scan in their presence.

I still think the syntax of TRUNCATE FOR EACH ROW would be useful, but if no one 
agrees, then I'll just make do with alternative solutions mentioned here.  That 
is, either a statement-level TRUNCATE trigger of some kind, or simply disallow 
TRUNCATE privileges.

Thank you.

-- Darren Duncan

Darren Duncan <darren@darrenduncan.net> writes:
> So, I'm partly proposing a specific narrow new feature, "TRUNCATE FOR EACH
> ROW"

Kevin has been proposing that we consider an alternative approach in
some other cases that I think would work better for you, too. Namely, to
have access to OLD and NEW in FOR EACH STATEMENT triggers, where they
would be relations rather than records.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

Dimitri Fontaine wrote:
> Darren Duncan <darren@darrenduncan.net> writes:
>> So, I'm partly proposing a specific narrow new feature, "TRUNCATE FOR EACH
>> ROW"
> 
> Kevin has been proposing that we consider an alternative approach in
> some other cases that I think would work better for you, too. Namely, to
> have access to OLD and NEW in FOR EACH STATEMENT triggers, where they
> would be relations rather than records.
> 
> Regards,

Yes, I believe that would work very well.  In fact, that would provide some 
power features.  I look forward to this, probably the best solution. -- Darren 
Duncan

On 11/16/2012 05:38 AM, Dimitri Fontaine wrote:
> Darren Duncan <darren@darrenduncan.net> writes:
>> So, I'm partly proposing a specific narrow new feature, "TRUNCATE FOR EACH
>> ROW"
> Kevin has been proposing that we consider an alternative approach in
> some other cases that I think would work better for you, too. Namely, to
> have access to OLD and NEW in FOR EACH STATEMENT triggers, where they
> would be relations rather than records.

That would be an exceedingly useful feature. On the project I was
working on earlier I'd immediately rewrite quite a few of the triggers
if it became available, with results I'd expect to be both faster and
simpler.

I can see using the same facility to optimise foreign key validity
checks in the absence of an index on the foreign key column, too.

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services

On 15 November 2012 05:25, Hannu Krosing <hannu@2ndquadrant.com> wrote:
> On 11/15/2012 09:48 AM, Craig Ringer wrote:
>>
>> If you want to prevent TRUNCATE, deny the privilege or add a trigger
>> that aborts the command.
>
> You can abort the transaction but not skip action as currently it is only
> possible to skip in ROW level triggers.
>
> So I'd modify this request to allow BEFORE EACH STATEMENT triggers
> to also be able to silently skip current action like BEFORE EACH ROW
> triggers can.

+1

> Then this request would simply be satisfied by a simple trigger which
> rewrites TRUNCATE into DELETE .

However, we don't need to do that to make this work.

Just create a BEFORE EACH STATEMENT trigger on TRUNCATE that issues a
DELETE from table.
Then have a EACH ROW trigger on DELETE.

That way each row gets logged.

The TRUNCATE still occurs, but semantically does nothing, though
physically deletes the space used by the already deleted rows.

-- Simon Riggs                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services