Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mX0gm-00039P-Oe for pgsql-bugs@arkaria.postgresql.org; Sun, 03 Oct 2021 12:33:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mX0gl-0004k9-Jz for pgsql-bugs@arkaria.postgresql.org; Sun, 03 Oct 2021 12:33:55 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mWr71-0003Y0-V5 for pgsql-bugs@lists.postgresql.org; Sun, 03 Oct 2021 02:20:24 +0000 Received: from mail-wr1-x434.google.com ([2a00:1450:4864:20::434]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mWr6z-0006LI-2t for pgsql-bugs@lists.postgresql.org; Sun, 03 Oct 2021 02:20:22 +0000 Received: by mail-wr1-x434.google.com with SMTP id r10so6280138wra.12 for ; Sat, 02 Oct 2021 19:20:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=LteGOLTO9HbNNL/oZQaDvD/oTRof1epX3IP/mAdpPA4=; b=OLOCwifYtCYOCplTPGmbaeJhFqOr4ptkzto5APldJK5hWzOOumLwvLK2n7h5iyjs2w jUykLESKc0mlLSkLap3jg1XlFmqipj/+9lLc4ZRq/wEpJNpJ39rjivlRZ5qd1OCO6NVj wGGfpML7UjftWCerYtdRkrnm/6nJ8hEsdbqwqruMkN8mgu4rh401L3GjZhcY4f9C5/Aw CAvD+pCuhKcTn4LCKtHvAadkCTqG2gvl/j1Veg9Dez+d+wdQga9/S2YosunZcz+gGQZG Yz6XGeMJug4ym7Osb+ihMBCLXaH2BFJcmjyfNzhykMqEC+ZUf/rCrqIuVuDbHC3K3I/a h0OA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=LteGOLTO9HbNNL/oZQaDvD/oTRof1epX3IP/mAdpPA4=; b=baj94mKh0PV52aM+X7d7DPBZpkJ9sk9Cd1hKyZP1XaWu8aXDOssU6P9so4IzO/Smyw IjC4CEAoNvr0KolnjunlrdYSeDV+U1HM4nC+dsCjTbshbz6ofTqoOBxarcB82N3BEQwO F9lycKC9EQIjkC3wzMzj5ZCAK/8u7BdYgQ3mMM+mGMfm9bs7jkv/1RMDijotEj8v6kC4 +1l4w9/dBydwQpyjUbnCyz4WFAx3y+nuP6pxsJ4Vzg1eEZ7Rv2RheeV5tWJjJxVZ0rgE sli1QxW4c7+Eb5glacIg2PXCUC9nsLd3ClyO+esUPuKoo5r9eYZzDdp/ep+vzH6bxlm0 1z4g== X-Gm-Message-State: AOAM533uCZE+SQTKrDEzlG8AzkWpi+9hSNKEPeK1jBZVCZS9Q+fMmkX6 hLY8cd7Jf3VX82MN+Dgsbf0= X-Google-Smtp-Source: ABdhPJwLoxEc6qIdOZlKyeSey4dUvPnecMWtOShA1jiE5eHF6gkprR7xCmKhTJZuMulKL8LdQfwWpg== X-Received: by 2002:a5d:4cca:: with SMTP id c10mr3201952wrt.188.1633227618846; Sat, 02 Oct 2021 19:20:18 -0700 (PDT) Received: from smtpclient.apple ([212.51.155.174]) by smtp.gmail.com with ESMTPSA id p10sm10342741wmq.40.2021.10.02.19.20.18 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sat, 02 Oct 2021 19:20:18 -0700 (PDT) From: Marc Bachmann Message-Id: <1F2F75F0-27DF-406F-848D-8B50C7EEF06A@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_4E7F07FC-AB28-4590-B1CE-06F2AD26A60C" Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.120.0.1.13\)) Subject: Re: BUG #16040: PL/PGSQL RETURN QUERY statement never uses a parallel plan Date: Sun, 3 Oct 2021 04:20:17 +0200 In-Reply-To: Cc: Robert Haas , jeremy@musicsmith.net, PostgreSQL mailing lists To: Tom Lane References: <16040-eaacad11fecfb198@postgresql.org> <1741.1584847383@sss.pgh.pa.us> X-Mailer: Apple Mail (2.3654.120.0.1.13) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_4E7F07FC-AB28-4590-B1CE-06F2AD26A60C Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hi, First congrats to the postgres 14 release =F0=9F=91=8F I=E2=80=99ve 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=E2=80=99s a quite simplified version to reproduce the issue. Is this some new expected behavior that=E2=80=99s 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=E2=80=99); 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=E2=80=99); value -------- foo > On 12 Jun 2020, at 20:13, Robert Haas wrote: >=20 > On Sat, Mar 21, 2020 at 11:23 PM Tom Lane 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. >=20 > Right. >=20 >> And there might be some implementation restrictions >> lurking under it too --- that's not a part of the code I know in any >> detail. >=20 > 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. >=20 > This is a heck of a nice improvement. Thanks for working on it. >=20 > --=20 > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >=20 >=20 >=20 >=20 --Apple-Mail=_4E7F07FC-AB28-4590-B1CE-06F2AD26A60C Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Hi,

First = congrats to the postgres 14 release =F0=9F=91=8F

I=E2=80=99ve 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%4= 0sss.pgh.pa.us, the changes here might cause the actual = issue.
Here=E2=80=99s a quite = simplified version to reproduce the issue.
Is this = some new expected behavior that=E2=80=99s 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=E2=80=99);

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=E2=80=99);

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





= --Apple-Mail=_4E7F07FC-AB28-4590-B1CE-06F2AD26A60C--