Postgres Pro
Downloads
Home   >   mailing lists

Re: DELETE...RETURNING problem with libpq - Mailing list pgsql-sql

From Brice André
Subject Re: DELETE...RETURNING problem with libpq
Date
Msg-id CAOBG12mv6FH-uo3sHvb1SaHX4E_qVfVeaRr0y999Y+yUMUj0mw@mail.gmail.com
Whole thread Raw
In response to Re: DELETE...RETURNING problem with libpq  (Guillaume Lelarge <guillaume@lelarge.info>)
List pgsql-sql
Tree view
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

pgsql-sql by date:

Previous
From: Guillaume Lelarge
Date:
Subject: Re: DELETE...RETURNING problem with libpq
Next
From: Bill MacArthur
Date:
Subject: Re: reduce many loosely related rows down to one