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

[ 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.

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.

regards, tom lane