Re: INSERT/UPDATE ... RETURNING

Gavin Sherry wrote:
>
> Hi all,
>
> I am working on a project which requires INSERT/UPDATE ... RETURNING
> functionality. As far as I can tell, Oracle is one of the only commercial
> [...]
>
> 2) Same row affected multiple times
>
> UPDATEs can affect rows multiple times. It would be very easy to just
> return every tuple affected but that seems broken to me. The logical
> solution is to return that tuple which eventually results from the
> UPDATE. Unfortunately, that means that we cannot just printtup(). It'll
> add a bit of overhead to traverse already UPDATEd tuples for every tuple
> -- particularly if we hit disk. Ideas?

The same statement should not be able to affect the same row multiple
times. If it happens, this would be a bug in the tuple visibility.

>
> 3) Inherited updates affecting multiple tables
>
> Seems that it would be the right thing to do to allow returning of
> inherited rows, but it might be a bit painful to implement. Ideas?

If they get affected, yes.

>
> 4) Handling rule cases
>
> Seems reasonable to allow RETURNING when the query is re-written to a
> single query and that the operation is not transformed.

This might need some deeper look. It sounds plausible, but the rule
rewriting system has it's own meaning for gravity.

>
> 5) Permissions
>
> To use RETURNING, the user must have select privileges on the
> table(s) being affected

Agreed.

>
> 6) Protocol changes
>
> I don't see that this will affect the FE/BE protocol.

Agreed.

>
> 7) Zero affected rows
>
> Return zero rows.
>
> Comments, ideas?

I don't see a reason why not to allow DELETE ... RETURNING as well.

With some restriction it could be far simpler than you think. If the
targetlist after RETURNING is allowed to contain Var nodes referencing
NEW or OLD only, then the executor could build a result tuple right
where the heap operation is done and use printtup() to send it to the
client.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #