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.chalke@enterprisedb.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

On Fri, Oct 30, 2026 at 6:57 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

pá 30. 10. 2026 v 12:43 odesílatel Jeevan Chalke <
jeevan.chalke@enterprisedb.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.

Hi Pavel,

Thanks for looking at this.

I confirmed the multi-statement case. With INTO, only the result of the
last statement is used, so even *STRICT* passes for:

do $$
declare r record;
begin
execute 'select 1 union select 2; select 2' into strict r;
raise notice '%', r;
end;
$$;
NOTICE: (2)

That behavior is independent of this patch, which counts the rows of the
statement whose result is actually used, so I'd prefer to keep this patch
scoped to that.

Seems like a separate feature altogether.

Detecting multiple statements would need a different check, since
exec_stmt_dynexecute() currently hands the query string straight to
SPI_execute_extended() and has no way to see how many statements it
contained.

Thanks

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&gt;*

--
*Jeevan Chalke*
*Senior Principal Engineer, Engineering Manager*
*Product Development*

enterprisedb.com <https://www.enterprisedb.com&gt;

--
*Jeevan Chalke*
*Senior Principal Engineer, Engineering Manager*
*Product Development*

enterprisedb.com <https://www.enterprisedb.com&gt;

po 2. 11. 2026 v 12:35 odesílatel Jeevan Chalke <
jeevan.chalke@enterprisedb.com> napsal:

On Fri, Oct 30, 2026 at 6:57 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

pá 30. 10. 2026 v 12:43 odesílatel Jeevan Chalke <
jeevan.chalke@enterprisedb.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.

Hi Pavel,

Thanks for looking at this.

I confirmed the multi-statement case. With INTO, only the result of the
last statement is used, so even *STRICT* passes for:

do $$
declare r record;
begin
execute 'select 1 union select 2; select 2' into strict r;
raise notice '%', r;
end;
$$;
NOTICE: (2)

That behavior is independent of this patch, which counts the rows of the
statement whose result is actually used, so I'd prefer to keep this patch
scoped to that.

Seems like a separate feature altogether.

Detecting multiple statements would need a different check, since
exec_stmt_dynexecute() currently hands the query string straight to
SPI_execute_extended() and has no way to see how many statements it
contained.

Yes, it is hard to say what is expected behaviour in the mentioned case.

Maybe it should be documented, so this case is not covered by checks.

Regards

Pavel