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.