Wrong command name in writeable-CTE related error messages - Mailing list pgsql-hackers

From Markus Winand
Subject Wrong command name in writeable-CTE related error messages
Date
Msg-id 5ED91D2F-429D-47EF-8F4D-233CD9F8CBFB@winand.at
Whole thread Raw
Responses Re: Wrong command name in writeable-CTE related error messages
List pgsql-hackers
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
betrivial 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
casesas those covered by the patch. Ultimately getting hold of the command name might also be beneficial for a new
errormessage. 

  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

Attachment

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: memory leak in trigger handling (since PG12)
Next
From: Andres Freund
Date:
Subject: Re: memory leak in trigger handling (since PG12)