Re: PL/pgSQL: apply the too_many_rows extra check to EXECUTE ... INTO

Hi

pá 30. 10. 2026 v 12:43 odesílatel Jeevan Chalke <
jeevan(dot)chalke(at)enterprisedb(dot)com> napsal:

> Hi,
>
> The documentation for plpgsql.extra_warnings / plpgsql.extra_errors says
> that the too_many_rows check will "*check if a given query returns more
> than one row when an INTO clause is used*" *[1]*. Only static SQL
> statements implement this (exec_stmt_execsql()). exec_stmt_dynexecute()
> only looks at STRICT, so EXECUTE ... INTO silently uses the first row and
> nothing is reported:
>
> set plpgsql.extra_warnings = 'too_many_rows';
> do $$ declare x int;
> begin
> execute 'select g from generate_series(1,2) g' into x;
> end $$;
> DO
>
> The same statement written as static SQL does warn (or fail, with
> extra_errors).
>
> The attached patch computes too_many_rows_level in exec_stmt_dynexecute()
> the same way exec_stmt_execsql() does, and reports at that level when
> more than one row comes back and STRICT was not specified. With it:
>
> WARNING: query returned more than one row
> HINT: Make sure the query returns a single row, or use LIMIT 1.
> DO
>
> and with plpgsql.extra_errors the same message is raised as an ERROR.
> Checking costs nothing extra here, since EXECUTE already fetches all the
> rows (no row limit is passed to SPI).
>
> While there, I added the "Make sure the query returns a single row, or
> use LIMIT 1." hint to the error raised for EXECUTE ... INTO STRICT, so
> that it matches the error for static SQL. This changes the expected output
> of three existing STRICT tests, which now show the HINT line.
>
> The new tests are in the "runtime extra checks" section of plpgsql.sql and
> cover EXECUTE ... INTO with both extra_warnings and extra_errors.
>
> This is a behavior change: someone who has too_many_rows in extra_errors
> may start getting errors from EXECUTE ... INTO statements that used to
> pass. So I think this should go to master only (or v19, as it is yet to
> release), but I'd like to hear what others think.
>
>
Yes, this is my omission.

The dynamic SQL is more problematic - it allows multiquery

(2026-10-30 14:24:06) postgres=# do $$
declare r record;
begin
execute 'select 1 union select 2; select 2' into strict r;
raise notice '%', r;
end;
$$;
NOTICE: (2)
DO

Maybe extra errors should handle this case too.

regards

Pavel

> Thanks
>
> *[1]
> https://www.postgresql.org/docs/current/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS
> <https://www.postgresql.org/docs/current/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS>*
>
> --
> *Jeevan Chalke*
> *Senior Principal Engineer, Engineering Manager*
> *Product Development*
>
> enterprisedb.com <https://www.enterprisedb.com>
>