Re: Proposal: Solving the "Return proper effected tuple count from complex commands [return]" issue

On Sun, 8 Sep 2002 19:50:21 -0300, Steve Howe <howe(at)carcass(dot)dhs(dot)org>
wrote:
>Proposal #1 (author: Steve Howe):
>---------------------------------
>
>PQcmdStatus() ==> Should return the last executed command

#1a

> or the same as the original command

#1b = #2

>PQcmdTuples() ==> should return the sum of modified rows of all
> commands executed by the rule (DELETE / INSERT /
> UPDATE).

= #2c

>
>PQoidValue() ==> should return the value for the last INSERT executed
> command in the rule (if any).

>Proposal #2 (author: Tom lane):
>-------------------------------
>
>PQcmdStatus() ==> Should always return the same command type original
> submitted by the client.
>
>PQcmdTuples() ==> If no INSTEAD rule, return same output as for
> original command, ignoring other commands in the
> rule.If there is INSTEAD rules,
> use result of last command in the rewritten series,

#2a

> use result of last command of same type as original command

#2b

> or sum up the results of all the rewritten commands.

#2c

>PQoidValue() ==> If the original command was not INSERT, return 0.
> otherwise, if one INSERT, return it's original
> PQoidValue(). If more then one INSERT command
> applied, use last

#2A

> or other possibilities

#2B; one of these possibilities is: return 0 (#2C).

On Sun, 8 Sep 2002 21:52:45 -0400 (EDT), Bruce Momjian
<pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
:So, to summarize #2, we have:
:
: if no INSTEAD,
: return value of original command
:
: if INSTEAD,
: return tag of original command
: return sum of all affected rows with the same tag

this is a new interpretation: #2d

: return OID if all INSERTs in the rule insert only one row, else zero

this is #2C

>Proposal #3 (author: Steve Howe):
>---------------------------------
>
>Another possibility (which does not go against the other proposals but
>extends them) would be returning a stack of all commands executed and
>returning it on new functions, whose extend the primary's
>functionality; let's say these new functions are called
>PQcmdStatusEx(), PQcmdTuplesEx() and PQoidValueEx().

>Proposal #4 (author: Hiroshi Inoue):
>------------------------------------
>
>Hiroshi's proposal consist in a makeshift solution as stated on
>http://archives.postgresql.org/pgsql-general/2002-05/msg00170.php.
>
>Please refer to that thread for details.

Proposal #5:

On Sun, 08 Sep 2002 19:54:45 -0700, Joe Conway <mail(at)joeconway(dot)com>
wrote:
: if no INSTEAD,
: return value of original command
:
: if INSTEAD,
: return tag MUTATED
: return sum of sum of tuple counts of all replacement commands

this equals #2c

: return OID if sum of all replacement INSERTs in the rule inserted
: only one row, else zero

this is #2C

On Mon, 9 Sep 2002 20:41:41 +0200 (CEST), Peter Eisentraut
<peter_e(at)gmx(dot)net> wrote:
:The major premise in the standard's point of view is that
:views are supposed to be transparent. That is, if
:
: SELECT * FROM my_view WHERE condition;
:
:return N rows, then a subsequently executed
:
: UPDATE my_view SET ... WHERE condition;
:
:returns an update count of N, no matter what happens behind the scenes.

ISTM this is one of those problems where there is no generic solution.
Whatever you implement, it is easy to come up with an example that
shows that the implementation is broken (for a suitable definition of
"broken"), because there are so many different ways to use this
feature.

Here is just another "bad idea": As it is impossible to *guess* the
correct behaviour, let the dba *define* what he wants. There is no
CREATE RULE statement in SQL92, so we can't break any standard by
changing its syntax.

CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ INSTEAD ] action

where action can be:

NOTHING
| rulequery
| ( rulequery; rulequery ... )

where rulequery is:

[ COUNT ] query

(or any other keyword instead of COUNT)

Proposal #6:

If no INSTEAD, return value of original command (this is compatible to
#2), else ...

PQcmdStatus() ==> Always return tag of original command
(this equals #2).

PQcmdTuples() ==> Sum up the results of all the rewritten commands
marked as COUNTed.

PQoidValue() ==> If the original command was not INSERT, return 0.
otherwise, if all COUNTed rewritten INSERTs insert
exactly one row, then return its OID, else 0.

Proposal #7 (a variation of #6):

If no INSTEAD, treat the original command the same as a COUNTed
rewritten command.

+/- for both #6 and #7

Pro: Regarding PQcmdTuples this can emulate #1 and all variants of #2.

Con: need to store COUNTed flag for rule queries ==> catalog change
==> initdb ==> not for 7.3 (except we can find an unused bit).

Servus
Manfred