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

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