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.
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 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.comThe Enterprise PostgreSQL Company