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

On Tuesday, May 28, 2013 6:50 PM Brice Andr=E9 wrote:
> Hello Amit,
>=20
> Thanks for your answer.
>=20
> 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=20
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.

=20
> 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
>=20
> 2013/5/28 Amit Kapila <amit.kapila@huawei.com>:
> > On Tuesday, May 28, 2013 1:54 PM Brice Andr=E9 wrote:
> >> 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,
> >> >
> >> > 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.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.
> >> > >
> >>
> >>
> >>
> >> --
> >> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-bugs
> >