Thread: Regression in PL/PGSQL code using RETURN QUERY with Postgres 14
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.
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
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.
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
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&a=commitdiff&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,On Thu, Oct 7, 2021 at 3:05 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
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&a=commitdiff&h=e0eba586b
regards, tom lane