Re: DELETE...RETURNING problem with libpq

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(at)lelarge(dot)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
>