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(at)gmail(dot)com> wrote:
>
> On Sat, Mar 21, 2020 at 11:23 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
>
>
>
>