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

Hi

po 2. 11. 2026 v 15:21 odesílatel Jeevan Chalke <
jeevan(dot)chalke(at)enterprisedb(dot)com> napsal:

>
>
> On Mon, Nov 2, 2026 at 5:23 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>>
>>
>> po 2. 11. 2026 v 12:35 odesílatel Jeevan Chalke <
>> jeevan(dot)chalke(at)enterprisedb(dot)com> napsal:
>>
>>>
>>>
>>> On Fri, Oct 30, 2026 at 6:57 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>>> wrote:
>>>
>>>> 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.
>>>>
>>>
>>> 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.
>>
>
> Hi Pavel,
>
> Thanks for the follow-up.
>
> I agree that it is hard to say what the expected behavior is for that
> case, so documenting it seems like the right thing to do for now.
>
> Attached is v2, which is v1 plus a short paragraph in the EXECUTE section
> of plpgsql.sgml saying that when the command string contains more than
> one SQL command, only the result of the last one is assigned to the INTO
> variable(s), and that STRICT and the too_many_rows extra check likewise
> look only at that result.
>
> There are no code changes compared to v1.
>

+1

I tested this patch, and I don't see any issue

all tests passed

I'll mark this patch as ready for committer

Regards

Pavel

>
> Thanks
>
>
>>
>> Regards
>>
>> Pavel
>>
>>
>>> 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>*
>>>>>
>>>>> --
>>>>> *Jeevan Chalke*
>>>>> *Senior Principal Engineer, Engineering Manager*
>>>>> *Product Development*
>>>>>
>>>>> enterprisedb.com <https://www.enterprisedb.com>
>>>>>
>>>>
>>>
>>> --
>>> *Jeevan Chalke*
>>> *Senior Principal Engineer, Engineering Manager*
>>> *Product Development*
>>>
>>> enterprisedb.com <https://www.enterprisedb.com>
>>>
>>
>
> --
> *Jeevan Chalke*
> *Senior Principal Engineer, Engineering Manager*
> *Product Development*
>
> enterprisedb.com <https://www.enterprisedb.com>
>