pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDELETE...RETURNING problem with libpq
7+ messages / 3 participants
[nested] [flat]
* DELETE...RETURNING problem with libpq
@ 2013-05-25 11:07 Brice André <brice@famille-andre.be>
0 siblings, 0 replies; 7+ messages in thread
From: Brice André @ 2013-05-25 11:07 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: DELETE...RETURNING problem with libpq
@ 2013-05-25 12:00 Wolfe Whalen <wolfe@quios.net>
0 siblings, 1 reply; 7+ messages in thread
From: Wolfe Whalen @ 2013-05-25 12:00 UTC (permalink / raw)
To: Brice André <brice@famille-andre.be>; pgsql-sql
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."):
[1]http://www.postgresql.org/docs/8.4/static/libpq-exec.html
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.
References
1. http://www.postgresql.org/docs/8.4/static/libpq-exec.html
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: DELETE...RETURNING problem with libpq
@ 2013-05-25 12:49 Brice André <brice@famille-andre.be>
parent: Wolfe Whalen <wolfe@quios.net>
0 siblings, 1 reply; 7+ messages in thread
From: Brice André @ 2013-05-25 12:49 UTC (permalink / raw)
To: Wolfe Whalen <wolfe@quios.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: DELETE...RETURNING problem with libpq
@ 2013-05-26 06:51 Guillaume Lelarge <guillaume@lelarge.info>
parent: Brice André <brice@famille-andre.be>
0 siblings, 1 reply; 7+ messages in thread
From: Guillaume Lelarge @ 2013-05-26 06:51 UTC (permalink / raw)
To: Brice André <brice@famille-andre.be>; +Cc: Wolfe Whalen <wolfe@quios.net>; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Attachments:
[application/x-compressed-tar] example.tgz (1.3K, ../../1369551079.3087.4.camel@localhost.localdomain/2-example.tgz)
download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: DELETE...RETURNING problem with libpq
@ 2013-05-26 18:35 Brice André <brice@famille-andre.be>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 7+ messages in thread
From: Brice André @ 2013-05-26 18:35 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Wolfe Whalen <wolfe@quios.net>; pgsql-sql
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
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Attachments:
[application/zip] example.zip (1.3K, ../../CAOBG12nZ5fOB7=TeMezVdC9s5rmX-g+3FtW=6cJE8gtRZGcnhw@mail.gmail.com/3-example.zip)
download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: DELETE...RETURNING problem with libpq
@ 2013-05-27 06:56 Guillaume Lelarge <guillaume@lelarge.info>
parent: Brice André <brice@famille-andre.be>
0 siblings, 1 reply; 7+ messages in thread
From: Guillaume Lelarge @ 2013-05-27 06:56 UTC (permalink / raw)
To: Brice André <brice@famille-andre.be>; +Cc: Wolfe Whalen <wolfe@quios.net>; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Attachments:
[application/zip] example.zip (1.8K, ../../1369637775.2987.7.camel@localhost.localdomain/2-example.zip)
download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: DELETE...RETURNING problem with libpq
@ 2013-05-27 07:06 Brice André <brice@famille-andre.be>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 7+ messages in thread
From: Brice André @ 2013-05-27 07:06 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Wolfe Whalen <wolfe@quios.net>; pgsql-sql
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).
>
>
> --
> Guillaume
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2013-05-27 07:06 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-05-25 11:07 DELETE...RETURNING problem with libpq Brice André <brice@famille-andre.be>
2013-05-25 12:00 Re: DELETE...RETURNING problem with libpq Wolfe Whalen <wolfe@quios.net>
2013-05-25 12:49 ` Brice André <brice@famille-andre.be>
2013-05-26 06:51 ` Guillaume Lelarge <guillaume@lelarge.info>
2013-05-26 18:35 ` Brice André <brice@famille-andre.be>
2013-05-27 06:56 ` Guillaume Lelarge <guillaume@lelarge.info>
2013-05-27 07:06 ` Brice André <brice@famille-andre.be>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox