| 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
| 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 |