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
databases supporting this functionality. I checked DB2, Informix 7.2
(which is the most recent release for which I have a manual), Sybase and
Interbase. I also checked Firebird and MySQL.

Oracle returns data into binded variables, which we do not support. It
returns multiple rows into bind arrays, ie, an array of instances of 
binded variables.


Why is RETURNING useful?

1) It simplifies the use of sequences

2) Situations where applications don't know about the data which is being
stored. Eg:

i) INSERT .. VALUES(DEFAULT,...)
ii) UPDATE ... set col=col+X

and variations there of.


Issues with implementing this under Postgres:

1) Returning multiple rows

I see no read why multiple rows should not be returned if multiple rows
are affected. I cannot see any technical reason why this should not be the
case.

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?

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?

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.

5) Permissions

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

6) Protocol changes

I don't see that this will affect the FE/BE protocol.

7) Zero affected rows

Return zero rows.

Comments, ideas?

Thanks,

Gavin


Mon, 28 Apr 2003 19:37:57 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
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@Yahoo.com #


Mon, 28 Apr 2003 07:18:55 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: INSERT/UPDATE ... RETURNING
Gavin Sherry <swm@linuxworld.com.au> writes:
> I am working on a project which requires INSERT/UPDATE ... RETURNING
> functionality.

This would be good to have.

> Oracle returns data into binded variables, which we do not support. It
> returns multiple rows into bind arrays, ie, an array of instances of 
> binded variables.

Right.  I think it would be better to return the result just like a
SELECT result.

> 2) Same row affected multiple times

> UPDATEs can affect rows multiple times.

No they can't.  Study the MVCC rules.

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

Why does this matter?  The RETURNING clause can only name columns of the
topmost table, and so these expressions can surely be computed for every
row of every child table too.

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

In principle I think you could rewrite the RETURNING clause too, but
certainly it'd be okay to punt on this for a first implementation.

			regards, tom lane


Mon, 28 Apr 2003 10:18:25 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: INSERT/UPDATE ... RETURNING 