Should TRUNCATE fire DDL triggers

On Tuesday, July 8, 2025, Hari Krishna Sunder <hari.db.pg@gmail.com> wrote:

First of all, is TRUNCATE a DDL or a DML? This
<https://www.postgresql.org/docs/current/mvcc-caveats.html&gt; doc refers to
it as a DDL, whereas other docs like this
<https://www.postgresql.org/docs/17/runtime-config-logging.html#GUC-LOG-STATEMENT&gt;
and this
<https://www.postgresql.org/docs/17/hot-standby.html#HOT-STANDBY-USERS&gt; treat
it as a DML, so which one is it?

Neither…classification systems are often imperfect…but it sure quacks like
DML to my ears. I’d probably remove the term “DDL” from that first link
and avoid the grey area. Listing the two commands suffices.

A lot of other SQL databases treat TRUNCATE as a DDL, so assuming that is
true, can we add it to the command tags supported by "ddl_command_start"
and "ddl_command_end" triggers?

Seems worthy of consideration regardless of how one answers the prior
question; for much the same reason.

David J.

On Tue, 2025-07-08 at 21:23 -0700, David G. Johnston wrote:

On Tuesday, July 8, 2025, Hari Krishna Sunder <hari.db.pg@gmail.com> wrote:

First of all, is TRUNCATE a DDL or a DML? This doc refers to it as a DDL,
whereas other docs like this and this treat it as a DML, so which one is it?

Neither…classification systems are often imperfect…but it sure quacks like

DML to my ears.  I’d probably remove the term “DDL” from that first link and
avoid the grey area.  Listing the two commands suffices.

I agree with that.
 

A lot of other SQL databases treat TRUNCATE as a DDL, so assuming that is
true, can we add it to the command tags supported by "ddl_command_start"
and "ddl_command_end" triggers?

Seems worthy of consideration regardless of how one answers the prior
question; for much the same reason.

I disagree here. There are regular ON TRUNCATE triggers on tables, so I don't
see the need. You can define a trigger with the same trigger function on
several tables.

Yours,
Laurenz Albe

I disagree here. There are regular ON TRUNCATE triggers on tables, so I

don't

see the need. You can define a trigger with the same trigger function on
several tables.

You have to create a trigger for each table, and drop them before you drop
the table. The DDL trigger will just work more seamlessly.
If we say this is not a DDL, then how about supporting a wildcard as the
table_name in the BEFORE/ALTER TRUNCATE trigger?

----
Hari Krishna Sunder

On Tue, Jul 8, 2025 at 11:39 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Wednesday, July 9, 2025, Hari Krishna Sunder <hari.db.pg@gmail.com>
wrote:

I disagree here. There are regular ON TRUNCATE triggers on tables, so I

don't

see the need. You can define a trigger with the same trigger function on
several tables.

You have to create a trigger for each table, and drop them before you drop
the table. The DDL trigger will just work more seamlessly.
If we say this is not a DDL, then how about supporting a wildcard as the
table_name in the BEFORE/ALTER TRUNCATE trigger?

We avoid top-posting replies here.

I’d probably go with adding truncate_start and truncate_end (or maybe just
start…) events for event triggers if we don’t want to include them under
DDL events. I’d be strongly disinclined to touch regular triggers to
accommodate whatever use case you have that would benefit from this
capability. Stating what that is helps to get agreement, or just spending
time helping, making such changes.

David J.