Thread: [HACKERS] getting new serial value of serial insert
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
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
"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
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
> *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
>
> 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) #
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
Why can't this simply be done with a stored proc? Or am I missing the boat? Stored proc accepts parameters to insert, and returns whatever value you want it to. MikeA >> -----Original Message----- >> From: Aaron J. Seigo [mailto:aaron@gtv.ca] >> Sent: Thursday, November 04, 1999 8:26 AM >> To: Tom Lane >> Cc: Ed Loehr; pgsql-hackers@postgreSQL.org >> Subject: 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 >> >> ************ >>
I assume it is possible in pgsql to return the just-inserted serial value with a stored procedure. Stored procedures, though, would seem to be significantly more hassle vs. the INSERT-returns-serial approach. I share the concern about non-std SQL, though it seems the pgsql system (like most other RDBMS) is already loaded with non-std SQL precisely because the std has repeatedly been judged lacking for itches that needed scratching. As for concern about modifying INSERT semantics just for serial types, that too, I would normally share. A generalized solution is better. However, the pg serial type is already a special case, constructed by Postgres from other existing components unlike other types. For that reason, I think the case of facilitating an atomic return of the serial value from a SQL insert statement would provide pragmatic support for the key access mode to a special-case (non-std?) extension already present. For the same reason, it strikes me that the generalized ability to return any value from an INSERT should be treated as largely orthogonal to the special case serial type. Cheers. Ed "Ansley, Michael" wrote: > Why can't this simply be done with a stored proc? Or am I missing the boat? > Stored proc accepts parameters to insert, and returns whatever value you > want it to.
hi.. > Why can't this simply be done with a stored proc? Or am I missing the boat? > Stored proc accepts parameters to insert, and returns whatever value you > want it to. > in an earlier post i mentioned that this doesn't do anything FUNCTIONALY new, it merely allows doing it with EASE and greater SPEED.. ease, because you don't have to write a function (not really stored procedure =) to handle each specific insert and return pair you want.. with RETURN this would be defined on a per query basis... speed, because you would skip the SELECT to get the information.. it would tap the tuple whilst still in memory during the read, like a tigger... you skip the SELECt... last, it allows certain security possibilities: giving people access to the information they just inserted without giving them general SELECT permissions on the table(s) involved... so, no.. you aren't missing the boat by thinking this sort of thing CAN be done via other methods. the point is merely that the current methods are clumsy and slow and it seems a number of people are going through the current necessary hoops... -- Aaron J. Seigo Sys Admin