Horton

Thread

Commits

  1. Doc: fix out-of-date example of SPI usage.

  2. Fix SPI documentation for new handling of ExecutorRun's count parameter.

  1. Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html

    Curt Kolovson <ckolovson@gmail.com> — 2023-07-17T23:52:53Z

    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)

  2. Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html

    David G. Johnston <david.g.johnston@gmail.com> — 2023-07-18T00:26:26Z

    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. 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.
>
>
> db=# SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1);
>  execq
> -------
>      2
> (1 row)
>
>
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.

David J.

  3. Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html

    jian he <jian.universality@gmail.com> — 2023-07-18T00:34:24Z

    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 if the 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

  4. Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-07-18T01:22:02Z

    "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

  5. Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html

    Curt Kolovson <ckolovson@gmail.com> — 2023-07-18T01:52:34Z

    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

  6. Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html

    David G. Johnston <david.g.johnston@gmail.com> — 2023-07-18T02:21:45Z

    On Mon, 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?
>
>
Appears to be a documentation fix oversight back in v9.0

https://github.com/postgres/postgres/commit/2ddc600f8f0252a0864e85d5cc1eeb3b9687d7e9

We fixed the wording for the API argument but not the example that
demonstrated it.

David J.

  7. Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-07-18T02:36:46Z

    "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

  8. Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html

    Curt Kolovson <ckolovson@gmail.com> — 2023-07-18T02:45:24Z

    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. 

> 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

  9. Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html

    David G. Johnston <david.g.johnston@gmail.com> — 2023-07-18T03:13:41Z

    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:

=> SELECT execq('CREATE TABLE a (x integer)', 0); // start at 0
=> INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0)); // insert 2
=> SELECT execq('SELECT * FROM a', 0);

+ => SELECT execq('INSERT INTO a SELECT x + 2 FROM a returning x', 1); --
one more
+ -- (IIUC non-deterministically regardless of observed behavior even if
there were a limit in the SELECT)

=> SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1); // three more
=> SELECT execq('SELECT * FROM a', 10); // 6 as 10 is a limit

...[next block]
David J.

  10. Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-07-18T16:00:49Z

    "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