From: Marc Bachmann <marc.brookman@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: jeremy@musicsmith.net, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #16040: PL/PGSQL RETURN QUERY statement never uses a parallel plan
Date: Sun, 3 Oct 2021 04:20:17 +0200
Message-ID: <1F2F75F0-27DF-406F-848D-8B50C7EEF06A@gmail.com> (raw)
In-Reply-To: <CA+TgmoZvR9SP5mzH1k7MYrv3y1z4dWoJGz2z2SU-Qo1B7LrdgA@mail.gmail.com>
References: <16040-eaacad11fecfb198@postgresql.org>
<1741.1584847383@sss.pgh.pa.us>
<CA+TgmoZvR9SP5mzH1k7MYrv3y1z4dWoJGz2z2SU-Qo1B7LrdgA@mail.gmail.com>
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
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: marc.brookman@gmail.com, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16040: PL/PGSQL RETURN QUERY statement never uses a parallel plan
In-Reply-To: <1F2F75F0-27DF-406F-848D-8B50C7EEF06A@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox