Re: enhance SPI to support EXECUTE commands

[Tomas Vondra]

I've marked this patch as returned with feedback. It's been sitting in
the CF without any response from the author since September, and it's
not quite clear we actually want/need this feature. If needed, the patch
can be resubmitted for 2020-03.

regards

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: enhance SPI to support EXECUTE commands

[Tomas Vondra]

Hi,

On Wed, Sep 18, 2019 at 05:29:52PM +0500, Ahsan Hadi wrote:

I don't see much use for this because the documentation says that "server's
execute command cannot be used directly within pl/pgsql function (and it is
not needed). Within pl/pgsql you can execute update/delete commands using
pl/pgsql EXECUTE command and get results like row_count using "get
diagnostic".

Why would somebody do what you have shown in your example in pl/pgsql? Or
do you have a more general use-case for this enhancement?



Yeah, I think that's a good question - why would we need this? In fact,
the plpgsql docs explicitly say:

  The PL/pgSQL EXECUTE statement is not related to the EXECUTE SQL
  statement supported by the PostgreSQL server. The server's EXECUTE
  statement cannot be used directly within PL/pgSQL functions (and is
  not needed).

That is because all queries in plpgsql are prepared and cached
automatically, so why would we need this feature?

In any case, the patch should probably be in "waiting on author" state,
so I'll make it that way.


registrace

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services 

Re: enhance SPI to support EXECUTE commands

[Ahsan Hadi]

I don't see much use for this because the documentation says that "server's
execute command cannot be used directly within pl/pgsql function (and it is
not needed). Within pl/pgsql you can execute update/delete commands using
pl/pgsql EXECUTE command and get results like row_count using "get
diagnostic".

Why would somebody do what you have shown in your example in pl/pgsql? Or
do you have a more general use-case for this enhancement?

On Thu, Sep 5, 2019 at 11:39 AM Quan Zongliang <
zongliang.q...@postgresdata.com> wrote:

> Dear hackers,
>
> I found that such a statement would get 0 in PL/pgSQL.
>
> PREPARE smt_del(int) AS DELETE FROM t1;
> EXECUTE 'EXECUTE smt_del(100)';
> GET DIAGNOSTICS j = ROW_COUNT;
>
> In fact, this is a problem with SPI, it does not support getting result
> of the EXECUTE command. I made a little enhancement. Support for the
> number of rows processed when executing INSERT/UPDATE/DELETE statements
> dynamically.
>
> Regards,
> Quan Zongliang
>


-- 
Highgo Software (Canada/China/Pakistan)
URL : http://www.highgo.ca
ADDR: 10318 WHALLEY BLVD, Surrey, BC
EMAIL: mailto: ahsan.h...@highgo.ca

Re: enhance SPI to support EXECUTE commands

[Pavel Stehule]

pá 6. 9. 2019 v 3:36 odesílatel Quan Zongliang <
zongliang.q...@postgresdata.com> napsal:

> On 2019/9/5 17:33, Pavel Stehule wrote:
> >
> >
> > čt 5. 9. 2019 v 10:57 odesílatel Quan Zongliang
> >  > > napsal:
> >
> > On 2019/9/5 16:31, Pavel Stehule wrote:
> >  >
> >  >
> >  > čt 5. 9. 2019 v 10:25 odesílatel Quan Zongliang
> >  >  > 
> >  >  > >> napsal:
> >  >
> >  > On 2019/9/5 15:09, Pavel Stehule wrote:
> >  >  >
> >  >  >
> >  >  > čt 5. 9. 2019 v 8:39 odesílatel Quan Zongliang
> >  >  >  > 
> >  >  > >
> >  >  >  > 
> >  >  >  >  >  >
> >  >  > Dear hackers,
> >  >  >
> >  >  > I found that such a statement would get 0 in PL/pgSQL.
> >  >  >
> >  >  > PREPARE smt_del(int) AS DELETE FROM t1;
> >  >  > EXECUTE 'EXECUTE smt_del(100)';
> >  >  > GET DIAGNOSTICS j = ROW_COUNT;
> >  >  >
> >  >  > In fact, this is a problem with SPI, it does not
> support
> >  > getting result
> >  >  > of the EXECUTE command. I made a little enhancement.
> > Support
> >  > for the
> >  >  > number of rows processed when executing
> > INSERT/UPDATE/DELETE
> >  > statements
> >  >  > dynamically.
> >  >  >
> >  >  >
> >  >  > Is there some use case for support this feature?
> >  >  >
> >  > A user deletes the data in PL/pgSQL using the above method,
> > hoping
> >  > to do
> >  > more processing according to the number of rows affected, and
> > found
> >  > that
> >  > each time will get 0.
> >  >
> >  > Sample code:
> >  > PREPARE smt_del(int) AS DELETE FROM t1 WHERE c=$1;
> >  > EXECUTE 'EXECUTE smt_del(100)';
> >  > GET DIAGNOSTICS j = ROW_COUNT;
> >  >
> >  >
> >  > This has not sense in plpgsql. Why you use PREPARE statement
> > explicitly?
> >  >
> > Yes, I told him to do it in other ways, and the problem has been
> solved.
> >
> > Under psql, we can get this result
> >
> > flying=# EXECUTE smt_del(100);
> > DELETE 1
> >
> > So I think this may be the negligence of SPI, it should be better to
> > deal with it.
> >
> >
> > Personally, I would not to support features that allows bad code.
> >
> My code is actually a way to continue the CREATE AS SELECT and COPY
> statements. In spi.c, they look like this:
>
> if (IsA(stmt->utilityStmt, CreateTableAsStmt))  // original code
> ...
> else if (IsA(stmt->utilityStmt, CopyStmt))  // original code
> ...
> else if (IsA(stmt->utilityStmt, ExecuteStmt))  // my code
>
> My patch was not developed for this PL/pgSQL approach. I just because it
> found this problem.
>

ok, I can understand to this - but your example is usage is not good.

Pavel


>
> > Pavel
> >
> >
> >  >
> >  > IF j=1 THEN
> >  > do something
> >  > ELSIF j=0 THEN
> >  > do something
> >  >
> >  > Here j is always equal to 0.
> >  >
> >  >
> >  >
> >  > Regards
> >  >
> >  >  > Regards
> >  >  >
> >  >  > Pavel
> >  >  >
> >  >  >
> >  >  > Regards,
> >  >  > Quan Zongliang
> >  >  >
> >  >
> >
>
>
>

Re: enhance SPI to support EXECUTE commands

[Quan Zongliang]

On 2019/9/5 17:33, Pavel Stehule wrote:



čt 5. 9. 2019 v 10:57 odesílatel Quan Zongliang 
> napsal:


On 2019/9/5 16:31, Pavel Stehule wrote:
 >
 >
 > čt 5. 9. 2019 v 10:25 odesílatel Quan Zongliang
 > mailto:zongliang.q...@postgresdata.com>
 > >> napsal:
 >
 >     On 2019/9/5 15:09, Pavel Stehule wrote:
 >      >
 >      >
 >      > čt 5. 9. 2019 v 8:39 odesílatel Quan Zongliang
 >      > mailto:zongliang.q...@postgresdata.com>
 >     >
 >      > 
 >           >
 >      >     Dear hackers,
 >      >
 >      >     I found that such a statement would get 0 in PL/pgSQL.
 >      >
 >      >     PREPARE smt_del(int) AS DELETE FROM t1;
 >      >     EXECUTE 'EXECUTE smt_del(100)';
 >      >     GET DIAGNOSTICS j = ROW_COUNT;
 >      >
 >      >     In fact, this is a problem with SPI, it does not support
 >     getting result
 >      >     of the EXECUTE command. I made a little enhancement.
Support
 >     for the
 >      >     number of rows processed when executing
INSERT/UPDATE/DELETE
 >     statements
 >      >     dynamically.
 >      >
 >      >
 >      > Is there some use case for support this feature?
 >      >
 >     A user deletes the data in PL/pgSQL using the above method,
hoping
 >     to do
 >     more processing according to the number of rows affected, and
found
 >     that
 >     each time will get 0.
 >
 >     Sample code:
 >     PREPARE smt_del(int) AS DELETE FROM t1 WHERE c=$1;
 >     EXECUTE 'EXECUTE smt_del(100)';
 >     GET DIAGNOSTICS j = ROW_COUNT;
 >
 >
 > This has not sense in plpgsql. Why you use PREPARE statement
explicitly?
 >
Yes, I told him to do it in other ways, and the problem has been solved.

Under psql, we can get this result

flying=# EXECUTE smt_del(100);
DELETE 1

So I think this may be the negligence of SPI, it should be better to
deal with it.


Personally, I would not to support features that allows bad code.

My code is actually a way to continue the CREATE AS SELECT and COPY 
statements. In spi.c, they look like this:


if (IsA(stmt->utilityStmt, CreateTableAsStmt))  // original code
...
else if (IsA(stmt->utilityStmt, CopyStmt))  // original code
...
else if (IsA(stmt->utilityStmt, ExecuteStmt))  // my code

My patch was not developed for this PL/pgSQL approach. I just because it 
found this problem.




Pavel


 >
 >     IF j=1 THEN
 >         do something
 >     ELSIF j=0 THEN
 >         do something
 >
 >     Here j is always equal to 0.
 >
 >
 >
 >     Regards
 >
 >      > Regards
 >      >
 >      > Pavel
 >      >
 >      >
 >      >     Regards,
 >      >     Quan Zongliang
 >      >
 >

Re: enhance SPI to support EXECUTE commands

[Pavel Stehule]

čt 5. 9. 2019 v 10:25 odesílatel Quan Zongliang <
zongliang.q...@postgresdata.com> napsal:

> On 2019/9/5 15:09, Pavel Stehule wrote:
> >
> >
> > čt 5. 9. 2019 v 8:39 odesílatel Quan Zongliang
> >  > > napsal:
> >
> > Dear hackers,
> >
> > I found that such a statement would get 0 in PL/pgSQL.
> >
> > PREPARE smt_del(int) AS DELETE FROM t1;
> > EXECUTE 'EXECUTE smt_del(100)';
> > GET DIAGNOSTICS j = ROW_COUNT;
> >
> > In fact, this is a problem with SPI, it does not support getting
> result
> > of the EXECUTE command. I made a little enhancement. Support for the
> > number of rows processed when executing INSERT/UPDATE/DELETE
> statements
> > dynamically.
> >
> >
> > Is there some use case for support this feature?
> >
> A user deletes the data in PL/pgSQL using the above method, hoping to do
> more processing according to the number of rows affected, and found that
> each time will get 0.
>
> Sample code:
> PREPARE smt_del(int) AS DELETE FROM t1 WHERE c=$1;
> EXECUTE 'EXECUTE smt_del(100)';
> GET DIAGNOSTICS j = ROW_COUNT;
>

This has not sense in plpgsql. Why you use PREPARE statement explicitly?


> IF j=1 THEN
>do something
> ELSIF j=0 THEN
>do something
>
> Here j is always equal to 0.
>



>
> Regards
>
> > Regards
> >
> > Pavel
> >
> >
> > Regards,
> > Quan Zongliang
> >
>
>

Re: enhance SPI to support EXECUTE commands

[Pavel Stehule]

čt 5. 9. 2019 v 10:57 odesílatel Quan Zongliang <
zongliang.q...@postgresdata.com> napsal:

> On 2019/9/5 16:31, Pavel Stehule wrote:
> >
> >
> > čt 5. 9. 2019 v 10:25 odesílatel Quan Zongliang
> >  > > napsal:
> >
> > On 2019/9/5 15:09, Pavel Stehule wrote:
> >  >
> >  >
> >  > čt 5. 9. 2019 v 8:39 odesílatel Quan Zongliang
> >  >  > 
> >  >  > >> napsal:
> >  >
> >  > Dear hackers,
> >  >
> >  > I found that such a statement would get 0 in PL/pgSQL.
> >  >
> >  > PREPARE smt_del(int) AS DELETE FROM t1;
> >  > EXECUTE 'EXECUTE smt_del(100)';
> >  > GET DIAGNOSTICS j = ROW_COUNT;
> >  >
> >  > In fact, this is a problem with SPI, it does not support
> > getting result
> >  > of the EXECUTE command. I made a little enhancement. Support
> > for the
> >  > number of rows processed when executing INSERT/UPDATE/DELETE
> > statements
> >  > dynamically.
> >  >
> >  >
> >  > Is there some use case for support this feature?
> >  >
> > A user deletes the data in PL/pgSQL using the above method, hoping
> > to do
> > more processing according to the number of rows affected, and found
> > that
> > each time will get 0.
> >
> > Sample code:
> > PREPARE smt_del(int) AS DELETE FROM t1 WHERE c=$1;
> > EXECUTE 'EXECUTE smt_del(100)';
> > GET DIAGNOSTICS j = ROW_COUNT;
> >
> >
> > This has not sense in plpgsql. Why you use PREPARE statement explicitly?
> >
> Yes, I told him to do it in other ways, and the problem has been solved.
>
> Under psql, we can get this result
>
> flying=# EXECUTE smt_del(100);
> DELETE 1
>
> So I think this may be the negligence of SPI, it should be better to
> deal with it.
>

Personally, I would not to support features that allows bad code.

Pavel

>
> >
> > IF j=1 THEN
> > do something
> > ELSIF j=0 THEN
> > do something
> >
> > Here j is always equal to 0.
> >
> >
> >
> > Regards
> >
> >  > Regards
> >  >
> >  > Pavel
> >  >
> >  >
> >  > Regards,
> >  > Quan Zongliang
> >  >
> >
>
>

Re: enhance SPI to support EXECUTE commands

[Quan Zongliang]

On 2019/9/5 16:31, Pavel Stehule wrote:



čt 5. 9. 2019 v 10:25 odesílatel Quan Zongliang 
> napsal:


On 2019/9/5 15:09, Pavel Stehule wrote:
 >
 >
 > čt 5. 9. 2019 v 8:39 odesílatel Quan Zongliang
 > mailto:zongliang.q...@postgresdata.com>
 > >> napsal:
 >
 >     Dear hackers,
 >
 >     I found that such a statement would get 0 in PL/pgSQL.
 >
 >     PREPARE smt_del(int) AS DELETE FROM t1;
 >     EXECUTE 'EXECUTE smt_del(100)';
 >     GET DIAGNOSTICS j = ROW_COUNT;
 >
 >     In fact, this is a problem with SPI, it does not support
getting result
 >     of the EXECUTE command. I made a little enhancement. Support
for the
 >     number of rows processed when executing INSERT/UPDATE/DELETE
statements
 >     dynamically.
 >
 >
 > Is there some use case for support this feature?
 >
A user deletes the data in PL/pgSQL using the above method, hoping
to do
more processing according to the number of rows affected, and found
that
each time will get 0.

Sample code:
PREPARE smt_del(int) AS DELETE FROM t1 WHERE c=$1;
EXECUTE 'EXECUTE smt_del(100)';
GET DIAGNOSTICS j = ROW_COUNT;


This has not sense in plpgsql. Why you use PREPARE statement explicitly?


Yes, I told him to do it in other ways, and the problem has been solved.

Under psql, we can get this result

flying=# EXECUTE smt_del(100);
DELETE 1

So I think this may be the negligence of SPI, it should be better to 
deal with it.




IF j=1 THEN
    do something
ELSIF j=0 THEN
    do something

Here j is always equal to 0.



Regards

 > Regards
 >
 > Pavel
 >
 >
 >     Regards,
 >     Quan Zongliang
 >

Re: enhance SPI to support EXECUTE commands

[Quan Zongliang]

On 2019/9/5 15:09, Pavel Stehule wrote:



čt 5. 9. 2019 v 8:39 odesílatel Quan Zongliang 
> napsal:


Dear hackers,

I found that such a statement would get 0 in PL/pgSQL.

PREPARE smt_del(int) AS DELETE FROM t1;
EXECUTE 'EXECUTE smt_del(100)';
GET DIAGNOSTICS j = ROW_COUNT;

In fact, this is a problem with SPI, it does not support getting result
of the EXECUTE command. I made a little enhancement. Support for the
number of rows processed when executing INSERT/UPDATE/DELETE statements
dynamically.


Is there some use case for support this feature?

A user deletes the data in PL/pgSQL using the above method, hoping to do 
more processing according to the number of rows affected, and found that 
each time will get 0.


Sample code:
PREPARE smt_del(int) AS DELETE FROM t1 WHERE c=$1;
EXECUTE 'EXECUTE smt_del(100)';
GET DIAGNOSTICS j = ROW_COUNT;

IF j=1 THEN
  do something
ELSIF j=0 THEN
  do something

Here j is always equal to 0.

Regards


Regards

Pavel


Regards,
Quan Zongliang

Re: enhance SPI to support EXECUTE commands

[Pavel Stehule]

čt 5. 9. 2019 v 8:39 odesílatel Quan Zongliang <
zongliang.q...@postgresdata.com> napsal:

> Dear hackers,
>
> I found that such a statement would get 0 in PL/pgSQL.
>
> PREPARE smt_del(int) AS DELETE FROM t1;
> EXECUTE 'EXECUTE smt_del(100)';
> GET DIAGNOSTICS j = ROW_COUNT;
>
> In fact, this is a problem with SPI, it does not support getting result
> of the EXECUTE command. I made a little enhancement. Support for the
> number of rows processed when executing INSERT/UPDATE/DELETE statements
> dynamically.
>

Is there some use case for support this feature?

Regards

Pavel


> Regards,
> Quan Zongliang
>