Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
On Mon, Jul 17, 2023 at 7:45 PM Curt Kolovson 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.
Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
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 wrote: > > "David G. Johnston" writes: >>> On Mon, Jul 17, 2023 at 6:22 PM Tom Lane 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
Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
"David G. Johnston" writes: > On Mon, Jul 17, 2023 at 6:22 PM Tom Lane 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
Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
On Mon, Jul 17, 2023 at 6:22 PM Tom Lane wrote: > "David G. Johnston" writes: > > On Mon, Jul 17, 2023 at 4:53 PM Curt Kolovson > 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.
Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
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 wrote: > > "David G. Johnston" writes: >>> On Mon, Jul 17, 2023 at 4:53 PM Curt Kolovson 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
Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
"David G. Johnston" writes: > On Mon, Jul 17, 2023 at 4:53 PM Curt Kolovson 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
Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
On Tue, Jul 18, 2023 at 8:26 AM David G. Johnston 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 <> Jian
Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html
On Mon, Jul 17, 2023 at 4:53 PM Curt Kolovson 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.
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)
Re: Improvement of clarity in pg_ctl command docummentation
On Sat, Jul 15, 2023 at 05:57:40AM +, PG Doc comments form wrote: > I was reading the documentation about pg_ctl and there everything was well > written about the usage and option that can be used with the pg_ctl command > but there is not mentioned that we can not run pg_ctl command as root and > why we cant run it as root. Yes, I guess that you are right in the fact that we don't document pg_ctl cannot be run as root. The short explanation behind this restriction is that the PostgreSQL backend is considered as having the same rights as the OS user running it, and we don't want to encourage unsecure behaviors where systems could be easily compromised. -- Michael signature.asc Description: PGP signature
