Thread: INSERT/UPDATE ... RETURNING
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
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 #
At 07:37 PM 28/04/2003 +1000, Gavin Sherry wrote: >Oracle is one of the only commercial >databases supporting this functionality. DecRDB does too; it restricts update...returning to only return one row, and throws an error if more than one row is updated. It covers 95% of cases, but returning multiple rows might be nice. >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? My preference would be to return all rows that were affected. >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. The main thing that I think you need to cover is to ensure that common implementations of updateable views are supported in a reasonable way. Which this sounds like it does. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
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