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

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).

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

DELETE 0

With Regards,
Amit Kapila.