Обсуждение: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html

The actual results (shown below) are different than shown on this doc
page. The reason is because the second parameter to the UDF that is
passed to SPI_exec is the maximum number of rows to return, or 0 for
no limit. It is not the maximum number of rows to process. In the case
of "SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1)", it returned
0 rows, but it inserted (processed) 2 rows. This example should be
corrected.

Curt

db=# SELECT execq('CREATE TABLE a (x integer)', 0);
 execq
-------
     0
(1 row)

db=# INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0));
INSERT 0 1
db=# SELECT execq('SELECT * FROM a', 0);
INFO:  EXECQ:  0
INFO:  EXECQ:  1
 execq
-------
     2
(1 row)

db=# SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1);
 execq
-------
     2
(1 row)

db=# SELECT execq('SELECT * FROM a', 10);
INFO:  EXECQ:  0
INFO:  EXECQ:  1
INFO:  EXECQ:  2
INFO:  EXECQ:  3
 execq
-------
     4
(1 row)

On Tue, Jul 18, 2023 at 8:26 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> No INFO messages appear because you did not include a returning clause.  The 1 you passed to the call is immaterial
ifthe query you supply doesn't produce a result set. 
>
> David J.
>

indeed. https://www.postgresql.org/docs/current/spi-spi-execute.html


--
 I recommend David Deutsch's <<The Beginning of Infinity>>

  Jian

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Jul 17, 2023 at 4:53 PM Curt Kolovson <ckolovson@gmail.com> wrote:
>> The actual results (shown below) are different than shown on this doc
>> page.

> SPI_exec sees "INSERT 0 2" as the command tag from the SQL command you
> passed and so 2 is the output of the execq function call.
> No INFO messages appear because you did not include a returning clause.
> The 1 you passed to the call is immaterial if the query you supply doesn't
> produce a result set.

I think his point is that this example does not behave as the
documentation claims.  Which it does not, according to my
tests here.  I find this a bit disturbing --- did we intentionally
change the behavior of SPI_exec somewhere along the line?

            regards, tom lane

Tom is correct. It appears that nobody tested this example, which by the way seems unnecessarily complicated.

Sent from my iPhone

> On Jul 17, 2023, at 6:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>>> On Mon, Jul 17, 2023 at 4:53 PM Curt Kolovson <ckolovson@gmail.com> wrote:
>>> The actual results (shown below) are different than shown on this doc
>>> page.
>
>> SPI_exec sees "INSERT 0 2" as the command tag from the SQL command you
>> passed and so 2 is the output of the execq function call.
>> No INFO messages appear because you did not include a returning clause.
>> The 1 you passed to the call is immaterial if the query you supply doesn't
>> produce a result set.
>
> I think his point is that this example does not behave as the
> documentation claims.  Which it does not, according to my
> tests here.  I find this a bit disturbing --- did we intentionally
> change the behavior of SPI_exec somewhere along the line?
>
>            regards, tom lane

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Jul 17, 2023 at 6:22 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I think his point is that this example does not behave as the
>> documentation claims.  Which it does not, according to my
>> tests here.  I find this a bit disturbing --- did we intentionally
>> change the behavior of SPI_exec somewhere along the line?

> Appears to be a documentation fix oversight back in v9.0
> https://github.com/postgres/postgres/commit/2ddc600f8f0252a0864e85d5cc1eeb3b9687d7e9

Ah, thanks for the pointer.  I'd just been trying to bisect where
between 8.4 and 9.0 it changed, but failed because early-9.0 versions
don't build at all with current bison :-(

Anyway, given that the example needs updating, how should we do that
exactly?  Is it worth demonstrating both the behavior with RETURNING
and that without?  If not, which one to show?

            regards, tom lane

I’d vote for showing both (with RETURNING and without), since without it the second argument to SPI_exec has no effect
inthis example, which may not be obvious. That seems to be one of the subtle points illustrated by this example.  

> On Jul 17, 2023, at 7:36 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>>> On Mon, Jul 17, 2023 at 6:22 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> I think his point is that this example does not behave as the
>>> documentation claims.  Which it does not, according to my
>>> tests here.  I find this a bit disturbing --- did we intentionally
>>> change the behavior of SPI_exec somewhere along the line?
>
>> Appears to be a documentation fix oversight back in v9.0
>> https://github.com/postgres/postgres/commit/2ddc600f8f0252a0864e85d5cc1eeb3b9687d7e9
>
> Ah, thanks for the pointer.  I'd just been trying to bisect where
> between 8.4 and 9.0 it changed, but failed because early-9.0 versions
> don't build at all with current bison :-(
>
> Anyway, given that the example needs updating, how should we do that
> exactly?  Is it worth demonstrating both the behavior with RETURNING
> and that without?  If not, which one to show?
>
>            regards, tom lane

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Jul 17, 2023 at 7:45 PM Curt Kolovson <ckolovson@gmail.com> wrote:
>> I’d vote for showing both (with RETURNING and without), since without it
>> the second argument to SPI_exec has no effect in this example, which may
>> not be obvious. That seems to be one of the subtle points illustrated by
>> this example.

> I concur:

Agreed.  Done at 137b131d6.

            regards, tom lane