Обсуждение: Re: cursors with prepared statements
I found this email from Peter Eisentraut:
I have developed a patch that allows declaring cursors over prepared statements... This is an SQL standard feature. ECPG already supports it (with different internals). Internally, this just connects existing functionality in different ways, so it doesn't really introduce anything new.
I tried this in Version 15.2 (avoiding the question of how to bind actual arguments to placeholders):
prepare series as select s.v from generate_series(1, 10) as s(v);
start transaction;
declare cur scroll cursor for execute series;
start transaction;
declare cur scroll cursor for execute series;
It caused the 42601 error, « syntax error at or near “execute” ». So it looks like Peter’s patch hasn’t yet been adopted. What is the likelihood that it will be adopted in a future version?
p.s. The section doc "PL/pgSQL under the Hood” explains that expressions and static SQL statements in a PL/pgSQL source text are effectively prepared when first encountered at run time in a session. But simple tests show that there’s no evidence if this in the “pg_prepared_statements” catalog view. Is it possible that the subquery that is used to declare a cursor (using ordinary top level SQL or the PL/pgSQL API) is also invisibly prepared under the hood?
On Sat, Apr 15, 2023 at 2:15 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
I found this email from Peter Eisentraut:It caused the 42601 error, « syntax error at or near “execute” ». So it looks like Peter’s patch hasn’t yet been adopted. What is the likelihood that it will be adopted in a future version?
Closer to zero than one I'd say, given how that thread ended and not subsequent activity on the feature in the five years since.
David J.
> david.g.johnston@gmail.com wrote: > >> bryn@yugabyte.com wrote: >> >> I found this email from Peter Eisentraut: >> https://www.postgresql.org/message-id/762cc764-74f0-13fb-77ed-16f91c90f40d%402ndquadrant.com >> >> It caused the 42601 error, « syntax error at or near “execute” ». So it looks like Peter’s patch hasn’t yet been adopted.What is the likelihood that it will be adopted in a future version? > > Closer to zero than one I'd say, given how that thread ended and not subsequent activity on the feature in the five yearssince. Thanks. Shall I assume, too, that there’s no under-the-hood functionality for cursors, analogous to what happens with a PL/pgSQLprogram at run-time, that does the moral equivalent of on-demand prepare for a cursor’s defining subquery?
PL/pgSQL's "open cur for execute" provides workarouned... Re: cursors with prepared statements
От
Bryn Llewellyn
Дата:
bryn@yugabyte.com wrote:david.g.johnston@gmail.com wrote:bryn@yugabyte.com wrote:
I found this email from Peter Eisentraut:
https://www.postgresql.org/message-id/762cc764-74f0-13fb-77ed-16f91c90f40d%402ndquadrant.com
It caused the 42601 error, « syntax error at or near “execute” ». So it looks like Peter’s patch hasn’t yet been adopted. What is the likelihood that it will be adopted in a future version?
Closer to zero than one I'd say, given how that thread ended and not subsequent activity on the feature in the five years since.
Thanks. Shall I assume, too, that there’s no under-the-hood functionality for cursors, analogous to what happens with a PL/pgSQL program at run-time, that does the moral equivalent of on-demand prepare for a cursor’s defining subquery?
I tried this test.
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
declare
c0 constant refcursor not null := 'c0';
c1 constant refcursor not null := 'c1';
c2 constant refcursor not null := 'c2';
r int;
r0 int[];
r1 int[];
r2 int[];
begin
execute 'declare c0 scroll cursor for select s.v from generate_series(1, 10) as s(v)';
open c1 for execute 'select s.v from generate_series(1, $1) as s(v)' using n;
open c2 for execute format('execute series(%s)', n);
for j in 1.. 100 loop
fetch c0 into r; r0[j] := r;
exit when not found;
end loop;
for j in 1.. 100 loop
fetch c1 into r; r1[j] := r;
exit when not found;
end loop;
for j in 1.. 100 loop
fetch c2 into r; r2[j] := r;
exit when not found;
end loop;
assert (r1 = r0) and (r2 = r1);
end;
$body$;
prepare series(int) as select s.v from generate_series(1, $1) as s(v);
start transaction;
call s.p(10);
select name, statement from pg_cursors order by name;
rollback;
I expected to get an error from the PL/pgSQL statement that, after "format()" has been consumed, boils down to this:
open c2 for execute 'execute series(10)';
And I wondered how this would be reported. But it ran without error. The loop over each cursor showed that the result sets from the three alternatives are identical. And this is what the "pg_cursors" query showed:
name | statement
------+-----------------------------------------------------------------------------
c0 | declare c0 scroll cursor for select s.v from generate_series(1, 10) as s(v)
c1 | select s.v from generate_series(1, $1) as s(v)
c2 | execute series(10)
c9 | execute series(10)
------+-----------------------------------------------------------------------------
c0 | declare c0 scroll cursor for select s.v from generate_series(1, 10) as s(v)
c1 | select s.v from generate_series(1, $1) as s(v)
c2 | execute series(10)
c9 | execute series(10)
The difference in the value of "pg_cursors.statement" for two cursors based on the identical subquery where one is created with the SQL "declare" and the other is created with the PL/pgSQL "open" has puzzled me from the first time that I noticed it.
It seems that there must be different underlying mechanisms at work and that this explains why creating a cursor using SQL to execute a prepared statement fails but doing this using PL/pgSQL succeeds.
What's going on under the covers?
On 4/16/23 11:02, Bryn Llewellyn wrote:> statement fails but doing this using PL/pgSQL succeeds. > > What's going on under the covers? Pretty sure: https://www.postgresql.org/docs/current/spi.html -- Adrian Klaver adrian.klaver@aklaver.com
> adrian.klaver@aklaver.com wrote: > >> bryn@yugabyte.com wrote: >> >> It seems that there must be different underlying mechanisms at work and that this explains why creating a cursor usingSQL to execute a prepared statement fails but doing this using PL/pgSQL succeeds. What's going on under the covers? > > Pretty sure: > > www.postgresql.org/docs/current/spi.html I think that I see what you mean, Adrian. I had read the "PL/pgSQL Under the Hood" section to mean that, at run time, ordinarySQL calls were invariably made whenever the point of execution reached anything that implied SQL functionality (including,famously, expression evaluation). I'd assumed, therefore, that when the PL/pgSQL has an "open" statement, andwhen this is encountered at run time, the ordinary SQL "declare" statement was invoked. But it seems, rather, that the SQL "declare" and the PL/pgSQL "open" each as its own implementation by lower-level prinitives—andthat these differ in their details and in their power of expression. That would explain why the "pg_cursors.statement"text differs for cursors with identical properties (like scrollability), and the identically spelledsubquery, like I showed in my earlier email. It seems odd that the two approaches each has its own limitation(s). — You can't create a cursor to execute a prepared statement using the SQL API; and you can't create a holdable cursor usingthe (static) PL/pgSQL API but can work around this with dynamic SQL. —You have to dive down to a lower-level API like "Embedded SQL in C" (a.k.a. ECPG) to create a holdable cursor to executea prepared statement. But I appreciate that this comes with the territory and that anyone who feels strongly about this, and who knows how to doit, can develop their own patch and submit it for consideration.