| From: | Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Fire event triggers for relations created by EXPLAIN ANALYZE (CTAS, SELECT INTO, MAT VIEW) |
| Date: | 2026-11-19 09:37:35 |
| Message-ID: | CAN4CZFOycbakF5swg0i_hH2dZTkMP2+oA=noU0VFFFe3687ojw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hello
EXPLAIN ANALYZE of CREATE TABLE AS, SELECT INTO or CREATE MATERIALIZED
VIEW creates the relation, but doesn't fire event triggers. This
likely isn't a realistic use case, I discovered this from an
LLM-report against pg_tde, and then I realized that it is a server
issue.
Simple repro:
CREATE FUNCTION et() RETURNS event_trigger LANGUAGE plpgsql AS
$$BEGIN RAISE NOTICE '% %', tg_event, tg_tag; END$$;
CREATE EVENT TRIGGER s ON ddl_command_start EXECUTE FUNCTION et();
CREATE EVENT TRIGGER e ON ddl_command_end EXECUTE FUNCTION et();
CREATE TABLE t1 AS SELECT 1 AS a; -- start + end notices
EXPLAIN ANALYZE CREATE TABLE t2 AS SELECT 1 AS a; -- nothing, but t2 exists
EXPLAIN is handled directly in standard_ProcessUtility(), so it never
reaches the event trigger calls in ProcessUtilitySlow(). The
documentation doesn't list this as an exception, and it already says
that ddl_command_start fires for SELECT INTO "since this is equivalent
to CREATE TABLE AS". Robert also wrote in the original command trigger
discussion [1] that:
> If there's some way of creating a table without firing the trigger, it's a bug.
The attached patch runs the same event trigger sequence around the
CREATE TABLE AS case in ExplainOneUtility when ANALYZE is used. Plain
EXPLAIN creates nothing and fires nothing.
The new ExplainState field is an ABI break, the patch cannot be
backpatched as is. I can try to figure out something for that, but a
process utility hook is an easily usable workaround for extensions.
There is a related issue with the elements of CREATE SCHEMA (CREATE
SCHEMA s CREATE TABLE t ...): only the CREATE SCHEMA itself fires
ddl_command_start, the inner CREATE TABLE doesn't. That was discussed
when event triggers were designed [2][3], I'll start a separate thread
about it.
[1]: https://www.postgresql.org/message-id/CA+TgmoYAUf6_nG5nTp23i38qpWNtdcG1HUtPWm=iubay5rrMXg@mail.gmail.com
[2]: https://www.postgresql.org/message-id/1323707987-sup-1264@alvh.no-ip.org
[3]: https://www.postgresql.org/message-id/19773.1358464930@sss.pgh.pa.us
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Fire-event-triggers-for-relations-created-by-EXPL.patch | application/octet-stream | 13.0 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Zsolt Parragi | 2026-11-19 09:44:00 | Re: Inconsistency with EXPLAIN ANALYZE CREATE MATERIALIZED VIEW |
| Previous Message | shveta malik | 2026-11-19 09:26:41 | Re: Initial table sync copies the whole table when it is no longer published |