po 2. 11. 2026 v 12:35 odesílatel Jeevan Chalke < [email protected]> napsal:
> > > On Fri, Oct 30, 2026 at 6:57 PM Pavel Stehule <[email protected]> > wrote: > >> Hi >> >> pá 30. 10. 2026 v 12:43 odesílatel Jeevan Chalke < >> [email protected]> 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 > 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> >
