Re: Proposal: Solving the "Return proper effected tuple count

Joe Conway wrote:
> Bruce Momjian wrote:
> > I liked option #2. I don't think the _last_ query in a rule should have
> > any special handling.
> >
> > 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
> > return OID if all INSERTs in the rule insert only one row, else zero
> >
>
> How about:
>
> if no INSTEAD,
> return value of original command
>
> if INSTEAD,
> return tag MUTATED
> return sum of sum of tuple counts of all replacement commands
> return OID if sum of all replacement INSERTs in the rule inserted
> only one row, else zero
>
> This is basically Tom's proposal, but substituting MUTATED for the
> original command tag name acknowledges that the original command was not
> executed unchanged. It also serves as a warning that the affected
> tuple count is from one or more substitute operations, not the original
> command.

Any suggestion on how to show the tag mutated? Do we want to add more
tag possibilities?

> > I don't think we should add tuple counts from different commands, i.e.
> > adding UPDATE and DELETE counts just yeilds a totally meaningless
> > number.
>
> I don't know about that. The number of "rows affected" is indeed this
> number. It's just that they were not all affected in the same way.

Yes, that is true. The problem is that a DELETE returning a value of 10
may have deleted only one row and updated another 9 rows. In such
cases, returning 1 is better. Of course, if there are multiple deletes
then perhaps the total is better, but then again, there is no way to
flag this so we have to do one or the other consistently.

The real problem which you outline is that suppose the delete does _no_
deletes but only inserts. In my plan, we would return zero while in
yours you would return the rows updated.

In my view, if you return a delete tag, you better only count deletes.

Also, your total affected isn't going to work well with INSERT because
we could return a non-1 for rows affected and still return an OID, which
would be quite confusing. I did the total only matching tags because it
does mesh with the INSERT behavior.

> > I don't think there is any need/desire to add additional API routines to
> > handle multiple return values.
>
> Agreed.

Yep.

> > Can I get some votes on this? We have one user very determined to get a
> > fix, and the TODO.detail file has another user who really wants a fix.
>
> +1 for the version above ;-)

OK, we are getting closer.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073