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

On Tuesday, May 28, 2013 6:50 PM Brice André wrote:
> Hello Amit,
>
> Thanks for your answer.
>
> The reason why it does not work is still not really clear for me .What
> I find very strange is that, if you perform exactly same request, with
> exactly same C++ code, but that you change the database schema so that
> the ON DELETE rule of the view really deletes elements, it works
> properly. The inconsistency between both cases looks very strange to
> me.

What happens when you change ON DELETE rule of the view that really deletes
elements is that command type after applying rule remains same which means
Delete, so it can set the Tag.
Refer Function QueryRewrite().
Setting tag means after sql statement execution, it tells you the number of
elements affected. For example
1. when your rule is such that it internally updates, it will mention after
sql execution as
DELETE 0
2. when your rule is such that it internally deletes, it will mention after
sql execution as
DELETE 81

Now based on whether you can set the tag or not, ChoosePortalStrategy() will
decide portal strategy (PORTAL_ONE_RETURNING or PORTAL_RUN_MULTI).
When the rule is to do update, in that case it choose PORTAL_RUN_MULTI which
doesn't send tuples.

This is very old code, not sure how we can change to make it work for your
case. I had mentioned my analysis related to code so that others can also
give suggestions.

> Thank you for your workaround. I will test it and, if it works, I will
> use it as, I agree with you, it should meet my performance
> requirements.Note that my application can perform some SQL requests
> thousands of time during the same server session. So, for me, using
> prepared statement is an important feature !

> Thanks for your help.
> Regards,
> Brice
>
> 2013/5/28 Amit Kapila <amit(dot)kapila(at)huawei(dot)com>:
> > 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
> >