On the topic of how to programatically get a just-inserted serial
value, I propose the Sqlflex model for adoption into postgresql.
In that model, the return protocol for INSERT is altered to return
the serial value of the just-inserted record IFF the input value
for the serial column was 0.  [Side rules: tables can only have one
serial column, and db-generated serial values are always natural
numbers.]  For example,

 create table mytable (id serial, name varchar);

 -- this returns # of rows inserted, as usual...
 insert into mytable (name) values ('John');

 -- this returns serial 'id' of inserted record...
 insert into mytable (id,name) values (0,'Mary');

This requires no syntax change to INSERT (a Good Thing),
and does not require any additional higher-level processing to
get the serial value.  We have had good success with this
approach on some relatively high-performance 7x24x365 dbs.

Presently, I am performing an additional select to get the same
effect (in perl DBI) immediately after $sth->execute() for the
original insert query, e.g.,

 select id from mytable where oid = $sth->{pg_oid_status}

Seems a waste to have to do this, but I'm not aware of another way.

-Ed


Wed, 03 Nov 1999 12:43:04 -0600
Ed Loehr <ELOEHR@austin.rr.com>
[HACKERS] getting new serial value of serial insert
hi...

>  create table mytable (id serial, name varchar);
> 
>  -- this returns # of rows inserted, as usual...
>  insert into mytable (name) values ('John');
> 
>  -- this returns serial 'id' of inserted record...
>  insert into mytable (id,name) values (0,'Mary');

hm.. this is very elegant syntactically.. 

however, it would be nice to be able to have returned any number of fields of
any types... (for example, i have a trigger that changes a field in a record
whenever it gets updated/inserted.. it would be nice to get this returned as
well...)

also, if possible, it would be nice to extend this to UPDATE... 

can you think of a way to use this syntax aproach that would meet the needs
above?

>  select id from mytable where oid = $sth->{pg_oid_status}
> 
> Seems a waste to have to do this, but I'm not aware of another way.

*nods* seems quite a few people are running into this.

-- 
Aaron J. Seigo
Sys Admin

Wed, 3 Nov 1999 13:16:13 -0700
"Aaron J. Seigo" <aaron@gtv.ca>
Re: [HACKERS] getting new serial value of serial insert
"Aaron J. Seigo" wrote:

> >  -- this returns serial 'id' of inserted record...
> >  insert into mytable (id,name) values (0,'Mary');
>
> hm.. this is very elegant syntactically..
>
> however, it would be nice to be able to have returned any number of fields of
> any types... (for example, i have a trigger that changes a field in a record
> whenever it gets updated/inserted.. it would be nice to get this returned as
> well...)

>
> also, if possible, it would be nice to extend this to UPDATE...
>
> can you think of a way to use this syntax aproach that would meet the needs
> above?

No, and I'm not sure it'd be good to couple the two operations syntactically
even if one thought of a clever way to do it.  Serial-insert value retrieval is
a very frequent lightweight operation that fits nicely within current INSERT
syntax, and thus it seems intuitively "natural" to stretch INSERT semantics
in this way.

In the trigger scenario you mention, I'd be slightly more inclined to say it
crosses the fuzzy gray line into the area where a subsequent SELECT is in
order, as opposed to modifying INSERT syntax/semantics to allow this
SELECT functionality.  How's that for fuzzy logic?

Cheers.
Ed







Wed, 03 Nov 1999 14:19:28 -0600
Ed Loehr <ELOEHR@austin.rr.com>
Re: [HACKERS] getting new serial value of serial insert
hi...

> 
> No, and I'm not sure it'd be good to couple the two operations syntactically
> even if one thought of a clever way to do it.  Serial-insert value retrieval is
> a very frequent lightweight operation that fits nicely within current INSERT
> syntax, and thus it seems intuitively "natural" to stretch INSERT semantics
> in this way.

put that way, i can see your point clearly and agree... =)  

i think this would be a nice addition to pgsql... 
  
> In the trigger scenario you mention, I'd be slightly more inclined to say it
> crosses the fuzzy gray line into the area where a subsequent SELECT is in
> order, as opposed to modifying INSERT syntax/semantics to allow this
> SELECT functionality.  How's that for fuzzy logic?

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

-- 
Aaron J. Seigo
Sys Admin

Wed, 3 Nov 1999 16:15:15 -0700
"Aaron J. Seigo" <aaron@gtv.ca>
Re: [HACKERS] getting new serial value of serial insert
> *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 of
	SENDFE "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

Wed, 03 Nov 1999 18:41:43 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] getting new serial value of serial insert 
>
> hi...
>
> >
> > No, and I'm not sure it'd be good to couple the two operations syntactically
> > even if one thought of a clever way to do it.  Serial-insert value retrieval is
> > a very frequent lightweight operation that fits nicely within current INSERT
> > syntax, and thus it seems intuitively "natural" to stretch INSERT semantics
> > in this way.
>
> put that way, i can see your point clearly and agree... =)
>
> i think this would be a nice addition to pgsql...
>
> > In the trigger scenario you mention, I'd be slightly more inclined to say it
> > crosses the fuzzy gray line into the area where a subsequent SELECT is in
> > order, as opposed to modifying INSERT syntax/semantics to allow this
> > SELECT functionality.  How's that for fuzzy logic?

    Don't  forget  about  a  BEFORE  ROW  trigger that decides to
    return a NULL tuple  instead  of  a  valid  (maybe  modified)
    tuple.  Thus,  it  suppresses  the  entire  INSERT, UPDATE or
    DELETE operation silently. You cannot access  a  plain  value
    then  without  having a flag telling that there is a value at
    all.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Thu, 4 Nov 1999 04:18:30 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] getting new serial value of serial insert
hi...

> 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

agreed... at the same time though, just about every other database out there has
non-standard SQL statements to work around various limitations, perceived and
otherwise... also, a quick look through the user documentation for postgres
will show that there already are a lot of non-standard SQL statements..
*shrug*

> 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 RETURN cluase concept isn't limited to serial columns or single columns...
it would allow the return of any columns that were affected by the
INSERT/UPDATE/DELETE...
   
> 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 of
> 	SENDFE "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.

i don't think this is leaps and bounds above what can already be done with
functions, triggers and external code now. while this would probably create a
speed adantage (by skipping a select statement step) it would still leave the
problem of having to implement a trigger for every type of data you want back. 

and there are limitations inherent to this method: if
you wanted field1 returned when updating feild2, but field3 when updating
fielld4... except that one time when you want both field1 and field3 returned...
*takes a deep breath* it just isn't flexible enough... 

for every possible return situation, you'd have to define it in a trigger...
and there still would be limitations to what rules you could set up.. e.g. how
would you define in a trigger different returned values depending on the user
that is currently accessing the database? a real world example would be a user
coming in over the web and an admin coming in through the same method. unless
pgsql handles the user authentication (which in most webplications, it doesn't)
there would be no way to tell the difference without going through more work
than it takes to do it with current methods (e.g. select).

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

this is _exactly_ what i have said in several previous posts: that it should not
be limited just to serial fields... 

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

if it was integrated into the INSERT/UPDATE/DELETE queries, it wouldn't need to
be implemented in each frontend library. it would just be output, much like the
OID and # of records inserted that currently appears after an
INSERT/UDPATE/DELETE.

however, if it is so completely horrid to add functionality to the SQL
statements, i really can't think of another method that would provide the
functionality that would actually make it useful outside of a limited number of
situations.... so unless someone can think of a way, maybe its just better to
leave it be.

-- 
Aaron J. Seigo
Sys Admin

Rule #1 of Software Design:  Engineers are _not_ users

Wed, 3 Nov 1999 23:25:51 -0700
"Aaron J. Seigo" <aaron@gtv.ca>
Re: [HACKERS] getting new serial value of serial insert