Re: [HACKERS] getting new serial value of serial insert - Mailing list pgsql-hackers

> *nods* this is where the RETURN clause we've been batting around comes
> in as a more powerful and secure way of dealing with this... oh well,
> i was hoping that perhaps the serial return concept could be applied
> here as well...

I don't like *any* of the proposals that have appeared in this thread.
Inventing nonstandard SQL syntax is a bad idea, and furthermore all
of these solutions are extremely limited in capability: they only work
for "serial" columns, they only work for a single serial column, etc
etc.  If we're going to address this issue at all, we should invent
a general-purpose mechanism for passing back to the frontend application
the results of server-side operations that are performed as a side effect
of SQL commands.

The idea that comes to my mind is to invent a new command, available in
"trigger" procedures, that causes a message to be sent to the frontend
application.  This particular problem of returning a serial column's
value could be handled in an "after insert" trigger procedure, with a
command along the lines ofSENDFE "mytable.col1=" + new.col1
We'd have to think about what restrictions to put on the message
contents, if any.  It might be sufficient just to counsel users
to stick identification strings on the front of the message text
as illustrated above.

With this approach we wouldn't be adding nonstandard SQL syntax (trigger
procedures are already nonstandard, and we'd be keeping the additions
in there).  Also, since more than one message could be sent during a
transaction, there wouldn't be any artificial restriction to just
returning one or a fixed number of values.  Finally, we'd not be
creating data-type-specific behavior for SERIAL; the facility could
be used for many things.

We'd need to think about just how to make the messages available to
client applications.  For libpq, something similar to the existing
NOTIFY handling might work.  Not sure how that would map into ODBC or
other frontend libraries.

Another issue is what about transaction semantics?  If we send such
a message right away, and then later the transaction is aborted, then
we shouldn't have sent the message at all.  But if the application wants
the message so it can get a serial number to insert in another record,
then it doesn't want the message to be held off till end of transaction,
either.  Maybe we need two sorts of SENDFE commands, one that sends
immediately and one that is queued until and unless the transaction
commits.  An application using the first kind would have to take
responsibility for not using the returned data in a way that would cause
transactional problems.
        regards, tom lane