Triggers on TRUNCATE?

Daniel Baldoni <pgsql@lcds.com.au> writes:

Forgive this if it's totally "out there" but I was wondering why there's no
support for triggers on a TRUNCATE operation.

Because the entire point of TRUNCATE is not to scan the table contents.

If you want to fire triggers, just use "DELETE FROM foo".

regards, tom lane

On E, 2004-07-26 at 17:59, Tom Lane wrote:

Daniel Baldoni <pgsql@lcds.com.au> writes:

Forgive this if it's totally "out there" but I was wondering why there's no
support for triggers on a TRUNCATE operation.

Because the entire point of TRUNCATE is not to scan the table contents.

Maybe he was after statement level triggers ?

We don't support them, and one also can't define a RULE for TRUNCATE -
so the advice would be to use TRUNCATE very sparingly ;)

Btw, does the right to TRUNCATE come with GRANT DELETE, or must one be a
superuser to TRUNCATE ?

---------------
Hannu

Hannu Krosing <hannu@tm.ee> writes:

Btw, does the right to TRUNCATE come with GRANT DELETE, or must one be a
superuser to TRUNCATE ?

You have to own the table (or be superuser of course). This implies the
right to drop its triggers, so TRUNCATE could be seen as dropping
triggers, DELETE, recreate triggers.

regards, tom lane

G'day folks,

Tom Lane wrote:

Daniel Baldoni <pgsql@lcds.com.au> writes:

Forgive this if it's totally "out there" but I was wondering why
there's no support for triggers on a TRUNCATE operation.

Because the entire point of TRUNCATE is not to scan the table contents.

If you want to fire triggers, just use "DELETE FROM foo".

Personally, I've never used TRUNCATE within a production system (I can't
even recall using an unqualified DELETE).

How do you then audit a TRUNCATE performed by somebody else (who, for
"political" reasons, has superuser access)? Such actions aren't limited to
attacks - but may simply be the result of "I thought it was a good idea at
the time". :-(

Ciao.

--
-------------------------------------------------------+---------------------
Daniel Baldoni BAppSc, PGradDipCompSci | Technical Director
require 'std/disclaimer.pl' | LcdS Pty. Ltd.
-------------------------------------------------------+ 856B Canning Hwy
Phone/FAX: +61-8-9364-8171 | Applecross
Mobile: 041-888-9794 | WA 6153
URL: http://www.lcds.com.au/ | Australia
-------------------------------------------------------+---------------------
"Any time there's something so ridiculous that no rational systems programmer
would even consider trying it, they send for me."; paraphrased from "King Of
The Murgos" by David Eddings. (I'm not good, just crazy)

How do you then audit a TRUNCATE performed by somebody else (who, for
"political" reasons, has superuser access)? Such actions aren't limited to
attacks - but may simply be the result of "I thought it was a good idea at
the time". :-(

Easily enough, have the logs record the pid, connection startup,
timestamps, statement,etc. That should give everything required to track
down a unique user who performed random actions.