EXPLAIN ANALYZE CREATE TABLE AS EXECUTE runs non-SELECT prepared statements
Hello
CREATE TABLE AS EXECUTE only accepts a prepared SELECT, rejecting
anything else with "prepared statement is not a SELECT". EXPLAIN
ANALYZE doesn't have the same check, so it runs whatever the prepared
statement is.
This is another issue I found while working on EXPLAIN ANALYZE CTAS
event triggers [1]/messages/by-id/CAN4CZFOycbakF5swg0i_hH2dZTkMP2+oA=noU0VFFFe3687ojw@mail.gmail.com.
Simple repro:
CREATE TABLE src (a int);
PREPARE ins AS INSERT INTO src VALUES (1) RETURNING *;
CREATE TABLE t1 AS EXECUTE ins; -- ERROR: prepared statement is not a SELECT
EXPLAIN ANALYZE CREATE TABLE t2 AS EXECUTE ins; -- inserts, creates t2
with the RETURNING rows
It seems clearly wrong, and the plain EXPLAIN variant also happily
shows the plan of something CREATE TABLE AS would reject.
The attached patch adds the same check to ExplainExecuteQuery. It goes
back to 9dbf2b7d75d, and the fix is small enough to backpatch.
[1]: /messages/by-id/CAN4CZFOycbakF5swg0i_hH2dZTkMP2+oA=noU0VFFFe3687ojw@mail.gmail.com
On Nov 19, 2026, at 17:51, Zsolt Parragi <zsolt.parragi@percona.com> wrote:
Hello
CREATE TABLE AS EXECUTE only accepts a prepared SELECT, rejecting
anything else with "prepared statement is not a SELECT". EXPLAIN
ANALYZE doesn't have the same check, so it runs whatever the prepared
statement is.This is another issue I found while working on EXPLAIN ANALYZE CTAS
event triggers [1].Simple repro:
CREATE TABLE src (a int);
PREPARE ins AS INSERT INTO src VALUES (1) RETURNING *;
CREATE TABLE t1 AS EXECUTE ins; -- ERROR: prepared statement is not a SELECT
EXPLAIN ANALYZE CREATE TABLE t2 AS EXECUTE ins; -- inserts, creates t2
with the RETURNING rowsIt seems clearly wrong, and the plain EXPLAIN variant also happily
shows the plan of something CREATE TABLE AS would reject.
Agreed.
The attached patch adds the same check to ExplainExecuteQuery. It goes
back to 9dbf2b7d75d, and the fix is small enough to backpatch.[1]: /messages/by-id/CAN4CZFOycbakF5swg0i_hH2dZTkMP2+oA=noU0VFFFe3687ojw@mail.gmail.com
<v1-0001-Reject-non-SELECT-prepared-statements-in-EXPLAIN-.patch>
V1 looks good to me.
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.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
On Thu, Nov 19, 2026 at 5:52 PM Zsolt Parragi <zsolt.parragi@percona.com>
wrote:
Hello
CREATE TABLE AS EXECUTE only accepts a prepared SELECT, rejecting
anything else with "prepared statement is not a SELECT". EXPLAIN
ANALYZE doesn't have the same check, so it runs whatever the prepared
statement is.
Confirmed on current master. EXPLAIN ANALYZE CREATE TABLE AS EXECUTE ins
inserts into src and creates t2 from the RETURNING row, while CREATE TABLE
AS EXECUTE ins is correctly rejected. Plain EXPLAIN shows the Insert plan.
v1 adds the same check ExecuteQuery() already uses... if there's an INTO
clause the prepared plan list must be a single CMD_SELECT.
I applied it and retried the same cases; they now all fail with "prepared
statement is not a SELECT", and the INSERT is not executed. A prepared
SELECT still works with CREATE TABLE AS EXECUTE, with or without EXPLAIN
ANALYZE. The new select_into
tests pass, as do the rest of the regular regression tests.
This is just closing the EXPLAIN hole, which seems right. The comment in
ExecuteQuery() still leaves INSERT ... RETURNING as a separate question.
LGTM.
Roberto
Show quoted text
Hi,
On Thu, Nov 19, 2026 at 09:51:55AM +0000, Zsolt Parragi wrote:
Hello
CREATE TABLE AS EXECUTE only accepts a prepared SELECT, rejecting
anything else with "prepared statement is not a SELECT". EXPLAIN
ANALYZE doesn't have the same check, so it runs whatever the prepared
statement is.This is another issue I found while working on EXPLAIN ANALYZE CTAS
event triggers [1].Simple repro:
CREATE TABLE src (a int);
PREPARE ins AS INSERT INTO src VALUES (1) RETURNING *;
CREATE TABLE t1 AS EXECUTE ins; -- ERROR: prepared statement is not a SELECT
EXPLAIN ANALYZE CREATE TABLE t2 AS EXECUTE ins; -- inserts, creates t2
with the RETURNING rowsIt seems clearly wrong, and the plain EXPLAIN variant also happily
shows the plan of something CREATE TABLE AS would reject.The attached patch adds the same check to ExplainExecuteQuery. It goes
back to 9dbf2b7d75d, and the fix is small enough to backpatch.
Agreed, that's an oversight in 9dbf2b7d75d. The fix is pretty straightforward
and looks good to me.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
On Fri, 20 Nov 2026, Chao Li <li.evan.chao@gmail.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]/messages/by-id/CAN4CZFOycbakF5swg0i_hH2dZTkMP2+oA=noU0VFFFe3687ojw@mail.gmail.com.
And shortly after I discovered even more issues with EXPLAIN ANALYZE MATERIALIZED VIEWS, and a thread discussing it from 2 years ago [2]/messages/by-id/20444c382e6cb5e21e93c94d679d0198b0dba4dd.camel@j-davis.com. 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]/messages/by-id/20444c382e6cb5e21e93c94d679d0198b0dba4dd.camel@j-davis.com 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]: /messages/by-id/CAN4CZFOycbakF5swg0i_hH2dZTkMP2+oA=noU0VFFFe3687ojw@mail.gmail.com
[2]: /messages/by-id/20444c382e6cb5e21e93c94d679d0198b0dba4dd.camel@j-davis.com