Regression in PL/PGSQL code using RETURN QUERY with Postgres 14

On 10/7/21 11:38 AM, Gavin Roy wrote:

Hi All,

My team was testing against Postgres 14 to ensure we could cleanly
upgrade and we ran across a regression in our PL/PGSQL code related to
the updates to RETURN QUERY.

Our code which works in previous versions of Postgres uses UPDATE
RETURNING and INSERT RETURNING in combination with RETURN QUERY. It
appears that in the parallelism updates, RETURN QUERY now only accepts
SELECT queries.

I'm pretty sure folks are going to want to see an example of the code
and the errors thrown in version 14.

Was this an intentional change in behavior? We can easily refactor our
PL/PGSQL functions to deal with the change, but if it was intentional,
perhaps it should be documented.

Regards,

Gavin

--
Adrian Klaver
adrian.klaver@aklaver.com

Gavin Roy <gavinr@aweber.com> writes:

Our code which works in previous versions of Postgres uses UPDATE RETURNING
and INSERT RETURNING in combination with RETURN QUERY. It appears that in
the parallelism updates, RETURN QUERY now only accepts SELECT queries.

Yeah, that's a mistake, previously reported and fixed at

https://git.postgresql.org/gitweb/?p=postgresql.git&amp;a=commitdiff&amp;h=e0eba586b

regards, tom lane

On Thu, Oct 7, 2021 at 2:54 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/7/21 11:38 AM, Gavin Roy wrote:

Hi All,

My team was testing against Postgres 14 to ensure we could cleanly
upgrade and we ran across a regression in our PL/PGSQL code related to
the updates to RETURN QUERY.

Our code which works in previous versions of Postgres uses UPDATE
RETURNING and INSERT RETURNING in combination with RETURN QUERY. It
appears that in the parallelism updates, RETURN QUERY now only accepts
SELECT queries.

I'm pretty sure folks are going to want to see an example of the code
and the errors thrown in version 14.

Sorry, I thought that was pretty clear. As an example, this worked prior to
14 and no longer works:

CREATE TABLE foo (
bar SERIAL PRIMARY KEY,
baz TEXT
);

CREATE FUNCTION update_foo(in_bar INT4, in_baz TEXT) RETURNS SETOF foo AS $$
BEGIN
RETURN QUERY UPDATE foo SET baz = in_baz WHERE bar = in_bar RETURNING
bar, baz;
END;
$$ LANGUAGE PLPGSQL;

postgres=# SELECT * FROM update_foo(1, 'baz?');
ERROR: query is not a SELECT
CONTEXT: query: UPDATE foo SET baz = in_baz WHERE bar = in_bar RETURNING
bar, baz
PL/pgSQL function update_foo(integer,text) line 3 at RETURN QUERY

Thanks so much Tom!

Regards,

Gavin

On Thu, Oct 7, 2021 at 3:05 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: