Hi! I noticed that errors due to writable CTEs in read-only or non-volatile context say the offensive command is SELECT.
For example a writeable CTE in a IMMUTABLE function:
CREATE TABLE t (x INTEGER);
CREATE FUNCTION immutable_func()
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
AS $$
WITH x AS (
INSERT INTO t (x) VALUES (1) RETURNING x
) SELECT * FROM x;
$$;
SELECT immutable_func();
ERROR: SELECT is not allowed in a non-volatile function
Or a writeable CTE in read-only transaction:
START TRANSACTION READ ONLY;
WITH x AS (
INSERT INTO t (x) VALUES (1) RETURNING x
)
SELECT * FROM x;
ERROR: cannot execute SELECT in a read-only transaction
My first thought was that these error messages should mention INSERT, but after
looking into the source I’m not sure anymore. The name of the command is
obtained from CreateCommandName(). After briefly looking around it doesn’t seem
to be trivial to introduce something along the line of
CreateModifyingCommandName().
So I started by using a different error message at those places where I think
it should. I’ve attached a patch for reference, but I’m not happy with it. In
particular I’m unsure about the SPI stuff (how to test?) and if there are more
cases as those covered by the patch. Ultimately getting hold of the command
name might also be beneficial for a new error message.
A WITH clause containing a data-modifying statement is not allowed in a
read-only transaction
It wouldn’t make me sad if somebody who touches the code more often than once
every few years can take care of it.
-markus
WIP-writable_cte_error_message-001.patch
Description: Binary data
