Thread: DELETE...RETURNING problem with libpq

DELETE...RETURNING problem with libpq

From
Brice André
Date:
Dear all,

I am trying to translate a code written in php to C++. So, I am now using lipq in order to access my postgresql database from C++.

As performance is an important feature, I am using prepared statements.

I have a SQL statement that performs a 'DELETE ... RETURNING ... ' stuff and I execute it from a prepared statement (using PQprepare and PQexecPrepared). Now, when I execute this command, it properly deletes requested row, but when I use command PQntuples, it returns 0, as if no data was returned.

When I execute the same sql command from PgAdmin or from my old php script (that did not use prepared statements), everything works fine.

Note that, in another part of my script, I use the same technique to perform an 'UPDATE ... RETURNING' and it works properly...

Does anyone has an idea of what may fail and how I can solve this problem ?

Regards,
Brice

PS : my postgresql server version is 8.4 and it is running on a Debian server, if it may help.

Re: DELETE...RETURNING problem with libpq

From
Wolfe Whalen
Date:
Hi Brice,
 
I believe that you'll need PQcmdTuples - "Returns the number of rows affected by the SQL command."
 
Watch out, it returns char* instead of int.  I believe it's supposed to be used for the UPDATE ... RETURNING as well, but I'd double check on that.
 
It's under "30.3.3. Retrieving Result Information for Other Commands" in the 8.4 docs ("These functions are used to extract information from PGresult objects that are not SELECT results."):
 
Let us know if that helps or if we should dig into it a little deeper.
 
Best regards,
 
Wolfe
 
--
Wolfe Whalen
wolfe@quios.net
 
 
 
On Sat, May 25, 2013, at 04:07 AM, Brice André wrote:
Dear all,
 
I am trying to translate a code written in php to C++. So, I am now using lipq in order to access my postgresql database from C++.
 
As performance is an important feature, I am using prepared statements.
 
I have a SQL statement that performs a 'DELETE ... RETURNING ... ' stuff and I execute it from a prepared statement (using PQprepare and PQexecPrepared). Now, when I execute this command, it properly deletes requested row, but when I use command PQntuples, it returns 0, as if no data was returned.
 
When I execute the same sql command from PgAdmin or from my old php script (that did not use prepared statements), everything works fine.
 
Note that, in another part of my script, I use the same technique to perform an 'UPDATE ... RETURNING' and it works properly...
 
Does anyone has an idea of what may fail and how I can solve this problem ?
 
Regards,
Brice
 
PS : my postgresql server version is 8.4 and it is running on a Debian server, if it may help.

Re: DELETE...RETURNING problem with libpq

From
Brice André
Date:
Hi Wolfe,

First, thanks for your help.

I tried your code, but it does not work... the function returns a string : "0". When I check, this command properly modifies one row, as expected.

I don't know if it may help, but just in case... The DELETE operation is not performed on a table : it is performed on a view. I have a rule on that view that, instead of performing a delete on the corresponding table, performs an update with table entry tagged as deleted (in a dedicated column of the table). This code (sql part) works for years and, when executing this command by other ways (php and pgadmin), it deletes and returns expected data. With libpq, it deletes, but does not return anything.


Regards,
Brice

Re: DELETE...RETURNING problem with libpq

From
Guillaume Lelarge
Date:
On Sat, 2013-05-25 at 14:49 +0200, Brice André wrote:
> Hi Wolfe,
>
> First, thanks for your help.
>
> I tried your code, but it does not work... the function returns a string :
> "0". When I check, this command properly modifies one row, as expected.
>

It works for me with PQntuples.

> I don't know if it may help, but just in case... The DELETE operation is
> not performed on a table : it is performed on a view. I have a rule on that
> view that, instead of performing a delete on the corresponding table,
> performs an update with table entry tagged as deleted (in a dedicated
> column of the table). This code (sql part) works for years and, when
> executing this command by other ways (php and pgadmin), it deletes and
> returns expected data. With libpq, it deletes, but does not return anything.
>

You'll find my test case attached. It's C code, not C++, but I guess it
won't be a big issue :)

It gives me this result:

$ ./example
connection OK
prepare OK
execute OK
ntuples: 81
cleanup OK

81 is the number of lines to delete. If I launch it another time, it
gives me 0 as all the rows have already been deleted.

Hope it helps.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

Attachment

Re: DELETE...RETURNING problem with libpq

From
Brice André
Date:
Hello Guillaume,

Thanks for your answer.

Your example is working fine on my computer too (I had to adapt some includes because my client is under Windows, but everything else was fine...).

But, this example is slightly different from my real code : in your example, the delete on the rule really deletes the element. In my code, the delete on the rule tags the element as deleted (with an UPDATE statement and a dedicated column in t1 table).

I slightly changed your example to be more representative of my code. Here are my results :
  • When executing the SQL statement from pgadmin, I get my 81 columns marked as deleted and I get the 81 row results to the query.
  • Whe executing it from your script, the function PQexecPrepared does not return 'PGRES_TUPLES_OK' anymore. It now returns 'PGRES_COMMAND_OK'.
  • From your program, the 81 rows are marked as deleted, as expected.
  • From your program, PQntuples returns the "0" string.
  • I did not try from php, but I expect same behaviour as with my real program...

So, once modified, this example behaves like my program.

I suppose that php and pgadmin use the same interface to execute the query. So, I suppose that there should be a solution to my problem... Do you think it's a bug in my version of libpq ? Or maybe is it related to the fact that I use prepared statement ?

I joined my little test program to this e-mail.

Thanks in advance,

Brice



2013/5/26 Guillaume Lelarge <guillaume@lelarge.info>
On Sat, 2013-05-25 at 14:49 +0200, Brice André wrote:
> Hi Wolfe,
>
> First, thanks for your help.
>
> I tried your code, but it does not work... the function returns a string :
> "0". When I check, this command properly modifies one row, as expected.
>

It works for me with PQntuples.

> I don't know if it may help, but just in case... The DELETE operation is
> not performed on a table : it is performed on a view. I have a rule on that
> view that, instead of performing a delete on the corresponding table,
> performs an update with table entry tagged as deleted (in a dedicated
> column of the table). This code (sql part) works for years and, when
> executing this command by other ways (php and pgadmin), it deletes and
> returns expected data. With libpq, it deletes, but does not return anything.
>

You'll find my test case attached. It's C code, not C++, but I guess it
won't be a big issue :)

It gives me this result:

$ ./example
connection OK
prepare OK
execute OK
ntuples: 81
cleanup OK

81 is the number of lines to delete. If I launch it another time, it
gives me 0 as all the rows have already been deleted.

Hope it helps.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

Attachment

Re: DELETE...RETURNING problem with libpq

From
Guillaume Lelarge
Date:
Hi,

On Sun, 2013-05-26 at 20:35 +0200, Brice André wrote:
> [...]
> Thanks for your answer.
>
> Your example is working fine on my computer too (I had to adapt some
> includes because my client is under Windows, but everything else was
> fine...).
>
> But, this example is slightly different from my real code : in your
> example, the delete on the rule really deletes the element. In my code, the
> delete on the rule tags the element as deleted (with an UPDATE statement
> and a dedicated column in t1 table).
>

Oh OK, I didn't understand that when I was working on the code.

> I slightly changed your example to be more representative of my code. Here
> are my results :
>
>    - When executing the SQL statement from pgadmin, I get my 81 columns
>    marked as deleted and I get the 81 row results to the query.
>    - Whe executing it from your script, the function PQexecPrepared does
>    not return 'PGRES_TUPLES_OK' anymore. It now returns 'PGRES_COMMAND_OK'.
>    - From your program, the 81 rows are marked as deleted, as expected.
>    - From your program, PQntuples returns the "0" string.
>    - I did not try from php, but I expect same behaviour as with my real
>    program...
>
> So, once modified, this example behaves like my program.
>

It does to me too.

> I suppose that php and pgadmin use the same interface to execute the query.
> So, I suppose that there should be a solution to my problem... Do you think
> it's a bug in my version of libpq ? Or maybe is it related to the fact that
> I use prepared statement ?
>

It took me a while to understand the difference between pgadmin/psql and
our little test program. Actually, the difference is that they don't use
PQprepare and PQexecPrepared. I've attached the new code. With
USE_PREPARED_STATEMENT defined at:
 * 0, it will simply do a PQexec of the real query
 * 1, it will do the usual PQprepare/PQexecPrepared
 * 2, it will do PQexec on the PREPARE statement, and PQexec on the
   EXECUTE statement (which is what both pgadmin and psql do)

It works with 0 and 1, not with 2. I still have no idea why. It might be
a bug, but I find it strange it's not been discovered since 8.4 (it also
doesn't work on 9.3 beta 1).


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

Attachment

Re: DELETE...RETURNING problem with libpq

From
Brice André
Date:
Hello Guillaume,

Thanks for your help.

I also think that it's a bug because, IMHO, prepared statements should behave like non-prepared ones and delete on a view should be consistent whatever is really done in the underlying structure of the database. Note that, in my real code, once marked as deleted, it is no more viewed by the view (there is a where clause on the deleted in the definition of the view), so the delete on the view really behaves like a real delete statement (as seen by user).

I will probably try to report a bug in postgresql bug tracker, just to see what they answer to me.

My current workaround is to split this prepared statement in 4 : begin transaction;DELETE;SELECT;commit transaction. I am not sure this is faster than 1 single non-prepared statement, but I will do performance tests later to try to detrmine this.

Once again, thanks for your help.

Regards,
Brice


2013/5/27 Guillaume Lelarge <guillaume@lelarge.info>
Hi,

On Sun, 2013-05-26 at 20:35 +0200, Brice André wrote:
> [...]
> Thanks for your answer.
>
> Your example is working fine on my computer too (I had to adapt some
> includes because my client is under Windows, but everything else was
> fine...).
>
> But, this example is slightly different from my real code : in your
> example, the delete on the rule really deletes the element. In my code, the
> delete on the rule tags the element as deleted (with an UPDATE statement
> and a dedicated column in t1 table).
>

Oh OK, I didn't understand that when I was working on the code.

> I slightly changed your example to be more representative of my code. Here
> are my results :
>
>    - When executing the SQL statement from pgadmin, I get my 81 columns
>    marked as deleted and I get the 81 row results to the query.
>    - Whe executing it from your script, the function PQexecPrepared does
>    not return 'PGRES_TUPLES_OK' anymore. It now returns 'PGRES_COMMAND_OK'.
>    - From your program, the 81 rows are marked as deleted, as expected.
>    - From your program, PQntuples returns the "0" string.
>    - I did not try from php, but I expect same behaviour as with my real
>    program...
>
> So, once modified, this example behaves like my program.
>

It does to me too.

> I suppose that php and pgadmin use the same interface to execute the query.
> So, I suppose that there should be a solution to my problem... Do you think
> it's a bug in my version of libpq ? Or maybe is it related to the fact that
> I use prepared statement ?
>

It took me a while to understand the difference between pgadmin/psql and
our little test program. Actually, the difference is that they don't use
PQprepare and PQexecPrepared. I've attached the new code. With
USE_PREPARED_STATEMENT defined at:
 * 0, it will simply do a PQexec of the real query
 * 1, it will do the usual PQprepare/PQexecPrepared
 * 2, it will do PQexec on the PREPARE statement, and PQexec on the
   EXECUTE statement (which is what both pgadmin and psql do)

It works with 0 and 1, not with 2. I still have no idea why. It might be
a bug, but I find it strange it's not been discovered since 8.4 (it also
doesn't work on 9.3 beta 1).