Re: bug in Prepared statement with DELETE RETURNING and rule on view - Mailing list pgsql-bugs

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

I shall look into it today in later half of the day.
> Dear Amit,
>=20
> Thanks for your answer.
>=20
> I performed the same test as you and I get the same result (on my
> linux server, debian, postgresql 8.4).
>=20
> Maybe the problem is related to libpq ?
>=20
> Did you tried the C code provided to see if you can reproduce the
> problem ?
>=20
> Regards,
> Brice
>=20
>=20
> 2013/5/28 Amit Kapila <amit.kapila@huawei.com>:
> > On Tuesday, May 28, 2013 12:39 AM Brice Andr=E9 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=3D> prepare t1plan (int,int) AS Delete from v1 where c1
> between $1
> > and $2
> >  returning c1,deleted;
> > PREPARE
> > postgres=3D> 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.
> >