Dear all,
I found what I really think is a bug in the postgresql 8.4.
I have an sql database structure in which a real table has a column
that is used to mark the entries as deleted without really deleting
them. Then, I have a view that is hiding this to the users, with
proper rules that perform real actions on the table. So, a ON DELETE
rule on this view is performing an UPDATE which marks the rows as
delete without deleting them. The view is hiding the rows tagged as
deleted.
This code is working from several years and I have a web-service that
performs several actions on top of this database. Those actions
include a "DELETE ... RETURNING ..." command on the view. This
web-service was implemented by a php script that did not use any
prepared statement, and everything was working properly.
I had performance issue with this solution and I decided to rewrite
the service in C++, and to use prepared statements. The SQL commands
are exactly the same, but they are now executed from a C++ application
using libpq, and they use prepared statements.
Now, when I perform the "DELETE...RETURNING ..." statement, the rows
are properly marked as deleted in the database, but PQexecPrepared
returns PGRES_COMMAND_OK and PQntuples returns 0, as if the query had
no result. All other functions that allow retrieving the results
behave as if no result was available.
I posted this problem on a user mailing list, and with people that
helped me in investigating this problem, we wrote a small application
that reproduces the problem. I joined the application to this e-mail.
The first version of the application worked properly ! After
investigation, we realised that it was because, in the first version,
the ON DELETE rule of the view really deleted the elements in the
table. In this case, the PQexecPrepared statement returns
PGRES_TUPLES_OK and we can retrieve the elements with dedicated
functions.
But once we change the ON DELETE rule to tag the entries as deleted,
without really deleting them, the PQexecPrepared statement returns
PGRES_COMMAND_OK and everything else behaves like if the statement had
no result.
In all cases, using non-prepared statements solve the problem.
My client is compiled under Windows and uses a 8.4 postgresql server.
I tested with one server hosted under Linux (debian) and one server
hosted under Windows : both give the same result.
One of the guys that helped me investigating the problem told me that
he could reproduce the problem on a 9.3 beta 1 version, but I
personnaly did not test it.
Regards,
Brice
