Lamar Owen <lamar.owen@wgcr.org> writes:
> However, the utility of INSERT returning a unique identifier to the
> inserted row needs to be addressed -- I would prefer it return the
> defined PRIMARY KEY value for the tuple just inserted, if a PRIMARY
> KEY is defined. If no PRIMARY KEY is defined, return a unique
> identifier (even a temporary one like the ctid) so that I have that
> information for use later in the application. The utility of that
> feature should not be underestimated.
That's something that needs to be thought about, all right. I kinda
like the idea of returning the ctid, because it is (a) very low
overhead, which is nice for something that the client may not actually
need, and (b) the tuple can be retrieved *very* quickly given a tid,
much more so than was possible with OID. OTOH, if you want to use a
tid you'd best use it right away, before someone else can update the
row...
The major problem with any change away from returning OID is that it'll
break client libraries and apps. How much pain do we want to cause
ourselves in that line?
Certainly, to return anything besides/instead of OID we'd have to change
the FE/BE protocol. IIRC, there are a number of other things pending
that require protocol changes, so gathering them all together and
updating the protocol isn't necessarily a bad thing. But I don't think
we have time for it in the 7.2 cycle, unless we slip the schedule past
the beta-by-end-of-August that I believe we're shooting for.
Another possibility, given that any app using a feature like this is
nonportable anyway, is to extend the INSERT statement along the lines
that someone (maybe Larry R? I forget now) proposed before:
INSERT INTO foo ... RETURNING x,y,z,...
where x,y,z, etc are expressions in the variables of the inserted
tuple(s). This could be made to look like a SELECT at the protocol
level, which would mean that it wouldn't break client libraries or
require a protocol bump, and it's *way* more flexible than any
hardwired decision about what columns to return. It wouldn't have
any problem with multiple tuples inserted by an INSERT ... SELECT,
either.
regards, tom lane