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

On Tuesday, May 28, 2013 12:39 AM Brice Andr=E9 wrote:
> Dear all,
>=20
> I found what I really think is a bug in the postgresql 8.4.
>=20
> 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.
>=20
> 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.
>=20
> 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).


postgres=3D> prepare t1plan (int,int) AS Delete from v1 where c1 between =
$1
and $2=20
 returning c1,deleted;=20
PREPARE=20
postgres=3D> Execute t1plan(10,90);=20
 c1 | deleted=20
----+---------=20
 10 | t=20
 11 | t=20
 12 | t=20
 13 | t=20
 14 | t=20
 15 | t=20
 16 | t=20
 17 | t=20
 18 | t=20
 19 | t=20
 20 | t=20
 21 | t=20
 22 | t=20
 23 | t=20
 24 | t=20
 25 | t=20
 26 | t=20
 27 | t=20
 28 | t=20
 29 | t=20
 30 | t=20
 31 | t=20
 32 | t=20
 33 | t=20
 34 | t=20
 35 | t=20
 36 | t=20
 37 | t=20
 38 | t=20
 39 | t=20
 40 | t=20
 41 | t=20
 42 | t=20
 43 | t=20
 44 | t=20
 45 | t=20
 46 | t=20
 47 | t=20
 48 | t=20
 49 | t=20
 50 | t=20
 51 | t=20
 52 | t=20
 53 | t=20
 54 | t=20
 55 | t=20
 56 | t=20
 57 | t=20
 58 | t=20
 59 | t=20
 60 | t=20
 61 | t=20
 62 | t=20
 63 | t=20
 64 | t=20
 65 | t=20
 66 | t=20
 67 | t=20
 68 | t=20
 69 | t=20
 70 | t=20
 71 | t=20
 72 | t=20
 73 | t=20
 74 | t=20
 75 | t=20
 76 | t=20
 77 | t=20
 78 | t=20
 79 | t=20
 80 | t=20
 81 | t=20
 82 | t=20
 83 | t=20
 84 | t=20
 85 | t=20
 86 | t=20
 87 | t=20
 88 | t=20
 89 | t=20
 90 | t=20
(81 rows)=20


DELETE 0=20

With Regards,
Amit Kapila.