Re: BUG #16040: PL/PGSQL RETURN QUERY statement never uses a parallel plan

Hi,

First congrats to the postgres 14 release 👏

I’ve just started testing with it and I found some unexpected behavior with some plpgsql function.
A function that inserts data and tries to return with a table now results in the error `query is not a SELECT`.
In previous versions that query succeeded.

While the message got updated in https://www.postgresql.org/message-id/flat/1914708.1629474624%40sss.pgh.pa.us, the changes here might cause the actual issue.
Here’s a quite simplified version to reproduce the issue.
Is this some new expected behavior that’s not documented or mentioned in the change log?

CREATE TABLE t (value text);
CREATE FUNCTION t_insert(v text)
RETURNS SETOF t
AS '
BEGIN
  RETURN QUERY
    INSERT INTO t ("value")
    VALUES (v)
    RETURNING *;
END
' LANGUAGE plpgsql;

SELECT * FROM t_insert('foo’);

ERROR:  query is not a SELECT


While a CTE query is working:

CREATE OR REPLACE FUNCTION t_insert(v text) RETURNS SETOF t
AS '
BEGIN
  RETURN QUERY
  WITH q AS (INSERT INTO t ("value") VALUES (v) RETURNING *)
  SELECT * FROM q;
END
' LANGUAGE plpgsql;

SELECT * FROM t_insert('foo’);

value
--------
foo



> On 12 Jun 2020, at 20:13, Robert Haas <robertmhaas@gmail.com> wrote:
> 
> On Sat, Mar 21, 2020 at 11:23 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I think that the latter restriction is probably sane, because we don't
>> want to suspend execution of a parallel query while we've got worker
>> processes waiting.
> 
> Right.
> 
>> And there might be some implementation restrictions
>> lurking under it too --- that's not a part of the code I know in any
>> detail.
> 
> There are. When you EnterParallelMode(), various normally-permissible
> options are restricted and will error out (e.g. updating your snapshot
> or command ID). Parallel query's not safe unless you remain in
> parallel mode from start to finish, but that means you can't let
> control escape into code that might do arbitrary things. That in a
> nutshell is why the cursor restriction is there.
> 
> This is a heck of a nice improvement. Thanks for working on it.
> 
> -- 
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
> 
> 
> 
>