pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedBug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
10+ messages / 4 participants
[nested] [flat]
* Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
@ 2023-07-17 23:52 Curt Kolovson <ckolovson@gmail.com>
2023-07-18 00:26 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Curt Kolovson @ 2023-07-17 23:52 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org
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)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
2023-07-17 23:52 Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Curt Kolovson <ckolovson@gmail.com>
@ 2023-07-18 00:26 ` David G. Johnston <david.g.johnston@gmail.com>
2023-07-18 00:34 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html jian he <jian.universality@gmail.com>
2023-07-18 01:22 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 10+ messages in thread
From: David G. Johnston @ 2023-07-18 00:26 UTC (permalink / raw)
To: Curt Kolovson <ckolovson@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
2023-07-17 23:52 Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Curt Kolovson <ckolovson@gmail.com>
2023-07-18 00:26 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html David G. Johnston <david.g.johnston@gmail.com>
@ 2023-07-18 00:34 ` jian he <jian.universality@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: jian he @ 2023-07-18 00:34 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Curt Kolovson <ckolovson@gmail.com>; pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
2023-07-17 23:52 Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Curt Kolovson <ckolovson@gmail.com>
2023-07-18 00:26 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html David G. Johnston <david.g.johnston@gmail.com>
@ 2023-07-18 01:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-07-18 01:52 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Curt Kolovson <ckolovson@gmail.com>
2023-07-18 02:21 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 2 replies; 10+ messages in thread
From: Tom Lane @ 2023-07-18 01:22 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Curt Kolovson <ckolovson@gmail.com>; pgsql-docs@lists.postgresql.org
"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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
2023-07-17 23:52 Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Curt Kolovson <ckolovson@gmail.com>
2023-07-18 00:26 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html David G. Johnston <david.g.johnston@gmail.com>
2023-07-18 01:22 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-07-18 01:52 ` Curt Kolovson <ckolovson@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Curt Kolovson @ 2023-07-18 01:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
2023-07-17 23:52 Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Curt Kolovson <ckolovson@gmail.com>
2023-07-18 00:26 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html David G. Johnston <david.g.johnston@gmail.com>
2023-07-18 01:22 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-07-18 02:21 ` David G. Johnston <david.g.johnston@gmail.com>
2023-07-18 02:36 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 10+ messages in thread
From: David G. Johnston @ 2023-07-18 02:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Curt Kolovson <ckolovson@gmail.com>; pgsql-docs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
2023-07-17 23:52 Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Curt Kolovson <ckolovson@gmail.com>
2023-07-18 00:26 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html David G. Johnston <david.g.johnston@gmail.com>
2023-07-18 01:22 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Tom Lane <tgl@sss.pgh.pa.us>
2023-07-18 02:21 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html David G. Johnston <david.g.johnston@gmail.com>
@ 2023-07-18 02:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-07-18 02:45 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Curt Kolovson <ckolovson@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2023-07-18 02:36 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Curt Kolovson <ckolovson@gmail.com>; pgsql-docs@lists.postgresql.org
"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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
2023-07-17 23:52 Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Curt Kolovson <ckolovson@gmail.com>
2023-07-18 00:26 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html David G. Johnston <david.g.johnston@gmail.com>
2023-07-18 01:22 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Tom Lane <tgl@sss.pgh.pa.us>
2023-07-18 02:21 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html David G. Johnston <david.g.johnston@gmail.com>
2023-07-18 02:36 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-07-18 02:45 ` Curt Kolovson <ckolovson@gmail.com>
2023-07-18 03:13 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Curt Kolovson @ 2023-07-18 02:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
2023-07-17 23:52 Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Curt Kolovson <ckolovson@gmail.com>
2023-07-18 00:26 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html David G. Johnston <david.g.johnston@gmail.com>
2023-07-18 01:22 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Tom Lane <tgl@sss.pgh.pa.us>
2023-07-18 02:21 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html David G. Johnston <david.g.johnston@gmail.com>
2023-07-18 02:36 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Tom Lane <tgl@sss.pgh.pa.us>
2023-07-18 02:45 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Curt Kolovson <ckolovson@gmail.com>
@ 2023-07-18 03:13 ` David G. Johnston <david.g.johnston@gmail.com>
2023-07-18 16:00 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: David G. Johnston @ 2023-07-18 03:13 UTC (permalink / raw)
To: Curt Kolovson <ckolovson@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
2023-07-17 23:52 Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Curt Kolovson <ckolovson@gmail.com>
2023-07-18 00:26 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html David G. Johnston <david.g.johnston@gmail.com>
2023-07-18 01:22 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Tom Lane <tgl@sss.pgh.pa.us>
2023-07-18 02:21 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html David G. Johnston <david.g.johnston@gmail.com>
2023-07-18 02:36 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Tom Lane <tgl@sss.pgh.pa.us>
2023-07-18 02:45 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Curt Kolovson <ckolovson@gmail.com>
2023-07-18 03:13 ` Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html David G. Johnston <david.g.johnston@gmail.com>
@ 2023-07-18 16:00 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2023-07-18 16:00 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Curt Kolovson <ckolovson@gmail.com>; pgsql-docs@lists.postgresql.org
"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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2023-07-18 16:00 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-17 23:52 Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html Curt Kolovson <ckolovson@gmail.com>
2023-07-18 00:26 ` David G. Johnston <david.g.johnston@gmail.com>
2023-07-18 00:34 ` jian he <jian.universality@gmail.com>
2023-07-18 01:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-07-18 01:52 ` Curt Kolovson <ckolovson@gmail.com>
2023-07-18 02:21 ` David G. Johnston <david.g.johnston@gmail.com>
2023-07-18 02:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-07-18 02:45 ` Curt Kolovson <ckolovson@gmail.com>
2023-07-18 03:13 ` David G. Johnston <david.g.johnston@gmail.com>
2023-07-18 16:00 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox