Re: bug in Prepared statement with DELETE RETURNING and rule on view

On Tue, 2013-05-28 at 12:41 +0530, Amit Kapila wrote:
> On Tuesday, May 28, 2013 12:39 AM Brice André wrote:
> > 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.
>
> I had tried in latest 9.3 code with psql using prepared statements and it
> worked fine, please see result below.
> I shall check your libpq application code as well, but in the mean time can
> you please verify whether the below works for you on 8.4 (I don't have 8.4
> setup).
>

It does work with psql and pgAdmin. The issue is with PQprepare, and
PQexecPrepared. So yeah, you need to look at the application code.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com