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

On Tuesday, June 11, 2013 12:15 AM Tom Lane wrote:
> [ got around to looking at this thread finally ]
>
> Amit Kapila <amit(dot)kapila(at)huawei(dot)com> writes:
> > 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.
>
> The point here is that in extended-query mode, we've defined that only
> the same statement that sets the command tag can return RETURNING rows.
> In the case at hand, the original DELETE isn't executed at all, being
> replaced by an UPDATE according to the rule. But we don't change the
> returned command tag to UPDATE, and we don't let the UPDATE's RETURNING
> clause return anything to the client. Both of these rules are meant to
> ensure unsurprising behavior as seen from the client side. We could
> debate changing them, but I'd be pretty worried about breaking user
> applications if we did.

There are only 2 points I could think of supporting such behavior:
1. Explain on Delete statement will show Update, so returning command tag as
Update is not wrong.
2. Maintaining consistency between psql and client interface.

I think user's have facility to obtain information about prepared statement
by using PQdescribePrepared() to know what they could expect in result.

> At the same time, things don't look terribly consistent because in psql
> (which uses simple query protocol) you *do* see the RETURNING results.
> That's because simple query protocol doesn't have a restriction that
> only one resultset can be returned from a single query. So it's a lot
> more wild-west as to what will really happen, and application code is
> expected to just deal with that. psql doesn't have a problem with
> multiple query results because it doesn't particularly care what they
> are; it's just going to print each one. Apps that are supposed to
> actually make sense of the data have more of an issue with that. The
> extended query protocol was explicitly designed to lock things down
> better so that interactions would be more predictable.
>
> The main thing I'm noticing in looking at this is that the
> documentation
> doesn't seem to explain anywhere the restriction to getting RETURNING
> results back from only the primary query. We ought to fix that.

I could think of below text that can be mentioned either in Create Rule
(Notes Section) page or in Extended Query section:

For extended-query mode, if the RULE changes the original statement, command
tag will not be modified and RETURNING clause will not return rows.

With Regards,
Amit Kapila.