Thread: Should TRUNCATE fire DDL triggers
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?
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?
---
Hari Krishna Sunder
Hari Krishna Sunder
On Tuesday, July 8, 2025, Hari Krishna Sunder <hari.db.pg@gmail.com> wrote:
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 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
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.