Re: Insert..returning (was Re: Re: postgres TODO) - Mailing list pgsql-hackers

At 21:28 11/07/00 -0400, Tom Lane wrote:
>
>> Putting this another way, does anyone object to this being implemented, *at
>> least* in the case of single row updates?
>
>Provide a specification *first*.

Picky, picky, picky...

The basic pholosophy would be:
   insert into x ... returning f1, f2

should produce the same results as:
   insert into x ...   select f1,f2 from x where x.oid in (oid's of affected rows).

So the returned fields must be in the target table, and multiple rows could
be returned. 

The only commercial DB that implements this kind of behaviour does it on
update only, and restricts it to updates that only affect one row. As a
first pass, it would satisfy 99.9% of users needs to only allow this
feature on inserts & updates that affected one row.


>  What exactly do you expect to do,
>and how will the code behave in the case of multiple affected rows,

Ideally, return the rowset as per a select. But as above, it might be a lot
simpler to raise an error if more than one row is affected.


>zero affected rows,

Do whatever 'update' does with zero rows affected.


> same row affected multiple times (possible with
>a joined UPDATE),

Return the most recent version of the row.


> inherited UPDATE that affects rows in multiple tables,

I don't know much about inherited stuff, which is why I posted the original
question about non-trivial problems with the implementation.

In this case I would say it should fall back on trying to reproduce the
behaviour of an 'insert into x*' followed by a 'select ... from x*'


>inserts/updates that are suppressed or redirected or turned into
>multiple operations (possibly on multiple tables) by rules or triggers,
>etc etc?

This is why I mentioned the 'maintain a list of affected oids' option; it
should only return rows of the target table that were affected by the
statement. When I do an 'insert' statement in psql, it reports the number
of rows inserted: whatever is used to show this number should be used to
determine the rows returned.


>  Not to mention the juicy topics of access permissions and
>possible errors.

Can't one fall back here on the 'insert followed by select' analogy? Or is
there a specific example that you have in mind?


>Also, how will this affect the frontend/backend
>protocol and what are the risks of breaking existing frontend code?

I have absolutely no idea - hence why I asked what people who knew PG
thought of the suggestion. 

I had naievely assumed that the fe would pass a query off to the be, and
handle the result based on what the be tells it to do. ie. I assumed that
the fe would not know that it was passing an 'insert' statement, and
therefor would not die when it got a set of tuples returned.


>Finally, how does your spec compare to similar features in other DBMSs?

See above.


>I don't have any fundamental objection to it given a well-thought-out
>specification and implementation ... but I don't want to find us stuck
>with supporting a half-baked nonstandard feature.  We have quite enough
>of those already ;-)

And I'm happy to do the leg work, if we can come to a design that people
who understand pg internals think will (a) not involve rewriting half of
pg, and (b) be clear, concise and easily supportable.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/