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

On Tuesday, May 28, 2013 1:28 PM Brice André wrote:

I shall look into it today in later half of the day.
> Dear Amit,
>
> Thanks for your answer.
>
> I performed the same test as you and I get the same result (on my
> linux server, debian, postgresql 8.4).
>
> Maybe the problem is related to libpq ?
>
> Did you tried the C code provided to see if you can reproduce the
> problem ?
>
> Regards,
> Brice
>
>
> 2013/5/28 Amit Kapila <amit(dot)kapila(at)huawei(dot)com>:
> > 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.
> >