Thread: DELETE...RETURNING problem with libpq
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 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++.
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.
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,BricePS : my postgresql server version is 8.4 and it is running on a Debian server, if it may help.
Hi Wolfe,
First, thanks for your help.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
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
Hello Guillaume,
Thanks for your answer.- 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:It works for me with PQntuples.
> 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.
>You'll find my test case attached. It's C code, not C++, but I guess it
> 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.
>
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
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
Hello Guillaume,
Thanks for your help.Regards,
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.Oh OK, I didn't understand that when I was working on the code.
>
> 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).
>> - When executing the SQL statement from pgadmin, I get my 81 columns
> I slightly changed your example to be more representative of my code. Here
> are my results :
>> 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...It does to me too.
>
> So, once modified, this example behaves like my program.
>It took me a while to understand the difference between pgadmin/psql and
> 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 ?
>
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).