Re: EXPLAIN ANALYZE CREATE TABLE AS EXECUTE runs non-SELECT prepared statements

From: Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com>
To: Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: EXPLAIN ANALYZE CREATE TABLE AS EXECUTE runs non-SELECT prepared statements
Date: 2026-11-20 22:47:23
Message-ID: CAN4CZFNKbTD-LzdPX4t44t-x=sm7NdYbtH+sJkbXG0H+kDJ12w@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Fri, 20 Nov 2026, Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> wrote:
> I found something out of scope of this patch. When I tested this patch by copying your repro statements, my db happened to have a table named “t2” already, thus I got:
> ```
> evantest=# EXPLAIN ANALYZE CREATE TABLE t2 AS EXECUTE ins;
> ERROR: relation "t2" already exists
> ```
>
> From user’s perspective, as EXPLAN doesn’t really create a table, the statement should more be reasonable to fail with "prepared statement is not a SELECT”.
>
> Then digging further, I found that if the EXECUTE statement contains a volatile function, even if "prepared statement is not a SELECT” is raised, the volatile function has been executed, which also feels unreasonable.
>
> Anything, those are different problems that should not block this patch.

There are multiple issues in this area.

I actually found the bug you mentioned originally, or more
specifically a consequence of it: while it creates the table, it
doesn't fire event triggers [1].
And shortly after I discovered even more issues with EXPLAIN ANALYZE
MATERIALIZED VIEWS, and a thread discussing it from 2 years ago [2].
There it seemed like an accepted feature or not a bug, so I ended up
submitting a patch to fix it, and also for the event trigger issue.

And today I realized that it also behaves incorrectly with read only
transactions (it creates the table even in read only transactions),
and after I looked into that, I found a few other cases of issues with
that. I didn't submit a patch about that yet, but I plan to next week.

I agree that most likely this shouldn't work, and should be simply
fixed as a bug, but that didn't seem to be in the consensus in [2] and
even if we do fix it, I don't think it could be backported. On the
other hand, some of the bugfixes for the above issues should be, so
we'll need them either way.

[1]: https://postgr.es/m/CAN4CZFOycbakF5swg0i_hH2dZTkMP2%2BoA%3DnoU0VFFFe3687ojw%40mail.gmail.com
[2]: https://postgr.es/m/20444c382e6cb5e21e93c94d679d0198b0dba4dd.camel%40j-davis.com

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2026-11-20 22:50:26 Re: pg_waldump: WAL in pg_wal.old/ inside a tar archive is read as WAL
Previous Message T, Adesh 2026-11-20 22:46:07 Postmaster PANIC on a standby with slot synchronization enabled