Обсуждение: 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 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.

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

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