Thread: enhance SPI to support EXECUTE commands

enhance SPI to support EXECUTE commands

From

Quan Zongliang

Date:

05 September 2019, 09:39:00

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

Attachment

spi_execmd.patch

Re: enhance SPI to support EXECUTE commands

From

Pavel Stehule

Date:

05 September 2019, 10:09:26

čt 5. 9. 2019 v 8:39 odesílatel Quan Zongliang <zongliang.quan@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

Re: enhance SPI to support EXECUTE commands

From

Quan Zongliang

Date:

05 September 2019, 11:25:15

On 2019/9/5 15:09, Pavel Stehule wrote:
> 
> 
> čt 5. 9. 2019 v 8:39 odesílatel Quan Zongliang 
> <zongliang.quan@postgresdata.com 
> <mailto:zongliang.quan@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;

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

From

Pavel Stehule

Date:

05 September 2019, 11:31:19

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

On 2019/9/5 15:09, Pavel Stehule wrote:
>
>
> čt 5. 9. 2019 v 8:39 odesílatel Quan Zongliang
> <zongliang.quan@postgresdata.com
> <mailto:zongliang.quan@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?

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

From

Quan Zongliang

Date:

05 September 2019, 11:56:48

On 2019/9/5 16:31, Pavel Stehule wrote:
> 
> 
> čt 5. 9. 2019 v 10:25 odesílatel Quan Zongliang 
> <zongliang.quan@postgresdata.com 
> <mailto:zongliang.quan@postgresdata.com>> napsal:
> 
>     On 2019/9/5 15:09, Pavel Stehule wrote:
>      >
>      >
>      > čt 5. 9. 2019 v 8:39 odesílatel Quan Zongliang
>      > <zongliang.quan@postgresdata.com
>     <mailto:zongliang.quan@postgresdata.com>
>      > <mailto:zongliang.quan@postgresdata.com
>     <mailto:zongliang.quan@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

From

Pavel Stehule

Date:

05 September 2019, 12:33:34

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

On 2019/9/5 16:31, Pavel Stehule wrote:
>
>
> čt 5. 9. 2019 v 10:25 odesílatel Quan Zongliang
> <zongliang.quan@postgresdata.com
> <mailto:zongliang.quan@postgresdata.com>> napsal:
>
> On 2019/9/5 15:09, Pavel Stehule wrote:
> >
> >
> > čt 5. 9. 2019 v 8:39 odesílatel Quan Zongliang
> > <zongliang.quan@postgresdata.com
> <mailto:zongliang.quan@postgresdata.com>
> > <mailto:zongliang.quan@postgresdata.com
> <mailto:zongliang.quan@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.

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

From

Quan Zongliang

Date:

06 September 2019, 04:35:51

On 2019/9/5 17:33, Pavel Stehule wrote:
> 
> 
> čt 5. 9. 2019 v 10:57 odesílatel Quan Zongliang 
> <zongliang.quan@postgresdata.com 
> <mailto:zongliang.quan@postgresdata.com>> napsal:
> 
>     On 2019/9/5 16:31, Pavel Stehule wrote:
>      >
>      >
>      > čt 5. 9. 2019 v 10:25 odesílatel Quan Zongliang
>      > <zongliang.quan@postgresdata.com
>     <mailto:zongliang.quan@postgresdata.com>
>      > <mailto:zongliang.quan@postgresdata.com
>     <mailto:zongliang.quan@postgresdata.com>>> napsal:
>      >
>      >     On 2019/9/5 15:09, Pavel Stehule wrote:
>      >      >
>      >      >
>      >      > čt 5. 9. 2019 v 8:39 odesílatel Quan Zongliang
>      >      > <zongliang.quan@postgresdata.com
>     <mailto:zongliang.quan@postgresdata.com>
>      >     <mailto:zongliang.quan@postgresdata.com
>     <mailto:zongliang.quan@postgresdata.com>>
>      >      > <mailto:zongliang.quan@postgresdata.com
>     <mailto:zongliang.quan@postgresdata.com>
>      >     <mailto:zongliang.quan@postgresdata.com
>     <mailto:zongliang.quan@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.
> 
> 
> 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

From

Pavel Stehule

Date:

06 September 2019, 06:18:58

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

On 2019/9/5 17:33, Pavel Stehule wrote:
>
>
> čt 5. 9. 2019 v 10:57 odesílatel Quan Zongliang
> <zongliang.quan@postgresdata.com
> <mailto:zongliang.quan@postgresdata.com>> napsal:
>
> On 2019/9/5 16:31, Pavel Stehule wrote:
> >
> >
> > čt 5. 9. 2019 v 10:25 odesílatel Quan Zongliang
> > <zongliang.quan@postgresdata.com
> <mailto:zongliang.quan@postgresdata.com>
> > <mailto:zongliang.quan@postgresdata.com
> <mailto:zongliang.quan@postgresdata.com>>> napsal:
> >
> > On 2019/9/5 15:09, Pavel Stehule wrote:
> > >
> > >
> > > čt 5. 9. 2019 v 8:39 odesílatel Quan Zongliang
> > > <zongliang.quan@postgresdata.com
> <mailto:zongliang.quan@postgresdata.com>
> > <mailto:zongliang.quan@postgresdata.com
> <mailto:zongliang.quan@postgresdata.com>>
> > > <mailto:zongliang.quan@postgresdata.com
> <mailto:zongliang.quan@postgresdata.com>
> > <mailto:zongliang.quan@postgresdata.com
> <mailto:zongliang.quan@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.
>
>
> 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

From

Ahsan Hadi

Date:

18 September 2019, 15:29:52

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.quan@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.hadi@highgo.ca

Re: enhance SPI to support EXECUTE commands

From

Tomas Vondra

Date:

05 January 2020, 02:05:28

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

From

Tomas Vondra

Date:

01 February 2020, 14:21:05

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