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

On Tuesday, May 28, 2013 1:54 PM Brice André wrote:
> 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 ?

I checked your C code and found the reason why you are not able to get the
tuples returned by "Delete .. Returning .."

Currently it is not supported to return tuples for non-select statements
using PQexecPrepared and the reason is, there is
no provision for Describe to send a RowDescription during this execution.
You can refer function PortalRunMulti() in code, if you want to know
more details.

I could see below way for you to change your application if you want rows
returned by "Delete .. Returning .."
Use PQexec for below sql statements:

prepare t1plan (int,int) AS Delete from v1 where c1 between $1 and $2
returning c1,deleted;
Execute t1plan(10,90);

After preparing once, you can call Execute SQL statement multiple times, it
can save your time of prepare each time of delete statement, which was
your motto for using PQexecPrepared().

>
> >
> > 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.
> > >
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs