enhance SPI to support EXECUTE commands

Started by Quan Zongliangover 6 years ago11 messages

Quan Zongliang

zongliang.quan@postgresdata.com

over 6 years ago

1 attachment(s)

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

Attachments:

spi_execmd.patchtext/plain; charset=UTF-8; name=spi_execmd.patch; x-mac-creator=0; x-mac-type=0Download

diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c
index 22dd55c378..b364492e0e 100644
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -2272,6 +2272,27 @@ _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI,
 					_SPI_current->processed = pg_strtouint64(completionTag + 5,
 															 NULL, 10);
 				}
+				else if (IsA(stmt->utilityStmt, ExecuteStmt))
+				{
+					if (strncmp(completionTag, "INSERT ", 7) == 0)
+					{
+						char *p = completionTag + 7;
+						/* INSERT: skip oid and space */
+						while (*p && *p != ' ')
+							p++;
+						if (*p != 0)
+						{
+							_SPI_current->processed =
+									pg_strtouint64(p, NULL, 10);
+						}
+					}
+					else if (strncmp(completionTag, "UPDATE ", 7) == 0 ||
+							strncmp(completionTag, "DELETE ", 7) == 0)
+					{
+						_SPI_current->processed =
+								pg_strtouint64(completionTag + 7, NULL, 10);
+					}
+				}
 			}
 
 			/*

Pavel Stehule

pavel.stehule@gmail.com

over 6 years ago

In reply to: Quan Zongliang (#1)

Re: enhance SPI to support EXECUTE commands

č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

Show quoted text

Regards,
Quan Zongliang

Quan Zongliang

zongliang.quan@postgresdata.com

over 6 years ago

In reply to: Pavel Stehule (#2)

Re: enhance SPI to support EXECUTE commands

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

Show quoted text

Regards

Pavel

Regards,
Quan Zongliang

Pavel Stehule

pavel.stehule@gmail.com

over 6 years ago

In reply to: Quan Zongliang (#3)

Re: enhance SPI to support EXECUTE commands

č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?

Show quoted text

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

Quan Zongliang

zongliang.quan@postgresdata.com

over 6 years ago

In reply to: Pavel Stehule (#4)

Re: enhance SPI to support EXECUTE commands

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.

Show quoted text

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

Pavel Stehule

pavel.stehule@gmail.com

over 6 years ago

In reply to: Quan Zongliang (#5)

Re: enhance SPI to support EXECUTE commands

č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

Show quoted text

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

Quan Zongliang

zongliang.quan@postgresdata.com

over 6 years ago

In reply to: Pavel Stehule (#6)

Re: enhance SPI to support EXECUTE commands

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.

Show quoted text

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
>

Pavel Stehule

pavel.stehule@gmail.com

over 6 years ago

In reply to: Quan Zongliang (#7)

Re: enhance SPI to support EXECUTE commands

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

Show quoted text

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

Ahsan Hadi

ahsan.hadi@gmail.com

over 6 years ago

In reply to: Quan Zongliang (#1)

Re: enhance SPI to support EXECUTE commands

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

#10

Tomas Vondra

tomas.vondra@2ndquadrant.com

about 6 years ago

In reply to: Ahsan Hadi (#9)

Re: enhance SPI to support EXECUTE commands

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

#11

Tomas Vondra

tomas.vondra@2ndquadrant.com

almost 6 years ago

In reply to: Tomas Vondra (#10)

Re: enhance SPI to support EXECUTE commands

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