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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
