Re: Proposal: Solving the "Return proper effected tuple

Bruce Momjian wrote:
> Joe Conway wrote:
>>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?

The suggestion was made based on what I think is the desired behavior,
but I must admit I have no idea how it would be implemented at this
point. It may turn out to be more pain than it's worth.

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

Sure, but that's why I am in favor of changing the tag. If you did:

DELETE FROM fooview WHERE name LIKE 'Joe%';

and got:

MUTATED 507324 3

it would mean that 3 tuples in total were affected by all of the
substitute operations, only of of them being an INSERT, and the Oid of
the lone INSERT was 507324. If instead I got:

DELETE 0

I'd be back to having no useful information. Did any rows in fooview
match the criteria "LIKE 'Joe%'"? Did any data in my database get
altered? Can't tell from this.

Joe