Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1ptwj1-00052T-Qm
	for pgsql-bugs@arkaria.postgresql.org; Tue, 02 May 2023 20:35:51 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1ptwj0-00080t-Nl
	for pgsql-bugs@arkaria.postgresql.org; Tue, 02 May 2023 20:35:50 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1ptwj0-00080k-Fi
	for pgsql-bugs@lists.postgresql.org; Tue, 02 May 2023 20:35:50 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by makus.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1ptwix-000BPc-PM
	for pgsql-bugs@lists.postgresql.org; Tue, 02 May 2023 20:35:49 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 342KZjGc2829788;
	Tue, 2 May 2023 16:35:45 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Daniel Gustafsson <daniel@yesql.se>
cc: Kieran McCusker <kieran.mccusker@gmail.com>,
        pgsql-bugs@lists.postgresql.org
Subject: Re: plpython does not honour max-rows
In-reply-to: <2678018.1683036150@sss.pgh.pa.us>
References: 
 <CAGgUQ6H6qYScctOhktQ9HLFDDoafBKHyUgJbZ6q_dOApnzNTXg@mail.gmail.com>
 <711512C7-2BC2-4901-A368-6A5A23023F72@yesql.se>
 <CAGgUQ6GBpw-vkzxuuTk3au8Hq+YezG5sv3yR=YjE3CbOqp-a3Q@mail.gmail.com>
 <D2132F23-B3AF-4D98-9859-BC2B64A599EC@yesql.se>
 <CAGgUQ6FMfBrCTeu7DLUmmsSVn0zu02mx7xZ39etb26pAqTrM+w@mail.gmail.com>
 <2678018.1683036150@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Tue, 02 May 2023 10:02:30 -0400"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <2829768.1683059733.0@sss.pgh.pa.us>
Date: Tue, 02 May 2023 16:35:45 -0400
Message-ID: <2829787.1683059745@sss.pgh.pa.us>
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: 
 <https://www.postgresql.org/message-id/2829787.1683059745%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2829768.1683059733.1@sss.pgh.pa.us>

I wrote:
> Since that's stood for a few decades now, changing it seems impossible
> from the backwards-compatibility standpoint.  However, it does seem
> appropriate to repeat that material in the wrapper's documentation.
> I wonder whether the similar plperl and pltcl wrappers are also
> documentation-shy here.

Indeed so.  The underlying SPI documentation is solid enough on this
point, but the PLs are all misleading, in that they suggest the limit
arguments work like "LIMIT n" or "FETCH n", which isn't quite so.
I suggest the attached docs patch.

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/x-diff; name="fix-PL-limit-documentation.patch";
	charset="us-ascii"
Content-ID: <2829768.1683059733.2@sss.pgh.pa.us>
Content-Description: fix-PL-limit-documentation.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml
index 6c81ee8fbe..8ab9602249 100644
--- a/doc/src/sgml/plperl.sgml
+++ b/doc/src/sgml/plperl.sgml
@@ -441,7 +441,7 @@ use strict;
    <variablelist>
     <varlistentry>
      <term>
-      <literal><function>spi_exec_query</function>(<replaceable>query</re=
placeable> [, <replaceable>max-rows</replaceable>])</literal>
+      <literal><function>spi_exec_query</function>(<replaceable>query</re=
placeable> [, <replaceable>limit</replaceable>])</literal>
       <indexterm>
        <primary>spi_exec_query</primary>
        <secondary>in PL/Perl</secondary>
@@ -449,9 +449,14 @@ use strict;
      </term>
      <listitem>
       <para>
-       <literal>spi_exec_query</literal> executes an SQL command and
-returns the entire row set as a reference to an array of hash
-references.  <emphasis>You should only use this command when you know
+       <function>spi_exec_query</function> executes an SQL command and
+returns the entire row set as a reference to an array of hash references.
+If <replaceable>limit</replaceable> is specified,
+then <function>spi_exec_query</function> retrieves at
+most <replaceable>limit</replaceable> rows, much as if the query included
+a <literal>LIMIT</literal> clause.  Omitting <literal>limit</literal> or
+specifying it as zero results in no row limit.
+<emphasis>You should only use this command when you know
 that the result set will be relatively small.</emphasis>  Here is an
 example of a query (<command>SELECT</command> command) with the
 optional maximum number of rows:
@@ -643,7 +648,10 @@ $plan =3D spi_prepare('SELECT * FROM test WHERE id &g=
t; $1 AND name =3D $2',
     by <literal>spi_exec_query</literal>, or in <literal>spi_query_prepar=
ed</literal> which returns a cursor
     exactly as <literal>spi_query</literal> does, which can be later pass=
ed to <literal>spi_fetchrow</literal>.
     The optional second parameter to <literal>spi_exec_prepared</literal>=
 is a hash reference of attributes;
-    the only attribute currently supported is <literal>limit</literal>, w=
hich sets the maximum number of rows returned by a query.
+    the only attribute currently supported is <literal>limit</literal>, w=
hich
+    sets the maximum number of rows returned from the query.
+    Omitting <literal>limit</literal> or specifying it as zero results in=
 no
+    row limit.
     </para>
 =

     <para>
diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index e190c90f45..2dd6ea6b32 100644
--- a/doc/src/sgml/plpython.sgml
+++ b/doc/src/sgml/plpython.sgml
@@ -789,7 +789,7 @@ $$ LANGUAGE plpython3u;
 =

   <variablelist>
    <varlistentry>
-    <term><literal>plpy.<function>execute</function>(<replaceable>query</=
replaceable> [, <replaceable>max-rows</replaceable>])</literal></term>
+    <term><literal>plpy.<function>execute</function>(<replaceable>query</=
replaceable> [, <replaceable>limit</replaceable>])</literal></term>
     <listitem>
      <para>
       Calling <function>plpy.execute</function> with a query string and a=
n
@@ -797,6 +797,15 @@ $$ LANGUAGE plpython3u;
       be returned in a result object.
      </para>
 =

+     <para>
+      If <replaceable>limit</replaceable> is specified,
+      then <function>plpy.execute</function> retrieves at
+      most <replaceable>limit</replaceable> rows, much as if the query
+      included a <literal>LIMIT</literal>
+      clause. Omitting <literal>limit</literal> or specifying it as zero
+      results in no row limit.
+     </para>
+
      <para>
       The result object emulates a list or dictionary object.  The result
       object can be accessed by row number and column name.  For example:
@@ -887,7 +896,7 @@ foo =3D rv[i]["my_column"]
 =

    <varlistentry>
     <term><literal>plpy.<function>prepare</function>(<replaceable>query</=
replaceable> [, <replaceable>argtypes</replaceable>])</literal></term>
-    <term><literal>plpy.<function>execute</function>(<replaceable>plan</r=
eplaceable> [, <replaceable>arguments</replaceable> [, <replaceable>max-ro=
ws</replaceable>]])</literal></term>
+    <term><literal>plpy.<function>execute</function>(<replaceable>plan</r=
eplaceable> [, <replaceable>arguments</replaceable> [, <replaceable>limit<=
/replaceable>]])</literal></term>
     <listitem>
      <para>
       <indexterm><primary>preparing a query</primary><secondary>in PL/Pyt=
hon</secondary></indexterm>
diff --git a/doc/src/sgml/pltcl.sgml b/doc/src/sgml/pltcl.sgml
index bf56ba6b1c..b31f2c1330 100644
--- a/doc/src/sgml/pltcl.sgml
+++ b/doc/src/sgml/pltcl.sgml
@@ -341,9 +341,11 @@ $$ LANGUAGE pltcl;
        </para>
        <para>
         The optional <literal>-count</literal> value tells
-        <function>spi_exec</function> the maximum number of rows
-        to process in the command.  The effect of this is comparable to
-        setting up a query as a cursor and then saying <literal>FETCH <re=
placeable>n</replaceable></literal>.
+        <function>spi_exec</function> to stop
+        once <replaceable>n</replaceable> rows have been retrieved,
+        much as if the query included a <literal>LIMIT</literal> clause.
+        If <replaceable>n</replaceable> is zero, the query is run to
+        completion, the same as when <literal>-count</literal> is omitted=
.
        </para>
        <para>
         If the command is a <command>SELECT</command> statement, the valu=
es of the

------- =_aaaaaaaaaa0--