Thread: Getting OID in psql of recent insert
In writing the book, I see the serious limitation that there is no way in psql to access the most recently inserted oid. Without it, there seems to be no way to use the oid value as a foreign key in another table. Should I add a function to return the most recently assigned oid to the backend, or is there a better way? -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> In writing the book, I see the serious limitation that there is no way
> in psql to access the most recently inserted oid. Without it, there
> seems to be no way to use the oid value as a foreign key in another
> table.
> Should I add a function to return the most recently assigned oid to the
> backend, or is there a better way?
I'm not sure why, but a backend-side function seems like the wrong way
to approach it. I guess I'm worried that the state would be too
volatile on the backend side. (Example: if you use the hypothetical
lastoid() function in an SQL query that causes triggers or rules to
be invoked behind your back, those triggers/rules could do new inserts.
Will lastoid() still return the "right" value by the time it gets
executed?)
It'd certainly be easy enough for psql to save off the OID anytime it
gets an "INSERT nnn" command response. The missing link is to invent
a way for a psql script to access that value and insert it into
subsequent SQL commands.
If you want to attack this, I'd suggest thinking a little larger than
just the last-OID problem. I'd like to be able to save off both
insertion OIDs and values extracted by SELECTs into named variables
of some sort, and then insert those values into as many later commands
as I want. Right now there's no way to do any such thing in a psql
script; you have to move up a level of difficulty into ecpg or pgtcl
or even C code if your application needs this. Plain psql scripts
would become substantially more powerful if psql had a capability
like this.
OTOH: we shouldn't ask psql to do everything under the sun. I'd
certainly think that it'd be unreasonable to try to do conditional
evaluation or looping in psql scripts, for instance. Maybe the right
answer is to teach people a little bit about using honest-to-goodness
scripting languages when their applications reach this level of
complexity. How much daylight is there between needing script
variables and needing control flow, do you think?
regards, tom lane
PS: not relevant to your main point, but to your example: I think it's
a real bad idea to teach people to use OIDs as foreign keys. That'll
create all kinds of trouble when it comes time to dump/reload their
database. Better to tell them to use SERIAL columns as keys. Not so
incidentally, we have currval() already...
> If you want to attack this, I'd suggest thinking a little larger than
> just the last-OID problem. I'd like to be able to save off both
> insertion OIDs and values extracted by SELECTs into named variables
> of some sort, and then insert those values into as many later commands
> as I want. Right now there's no way to do any such thing in a psql
> script; you have to move up a level of difficulty into ecpg or pgtcl
> or even C code if your application needs this. Plain psql scripts
> would become substantially more powerful if psql had a capability
> like this.
Yes, I understand. The new psql has the ability to have variables, so
this seems like a natural use for this:
testdb=> \set foo bar
Maybe we could have:
testdb=> \set foo lastoid
testdb=> \echo "foo is now ${foo}."
Seems those variables are not available in queries, though.
> OTOH: we shouldn't ask psql to do everything under the sun. I'd
> certainly think that it'd be unreasonable to try to do conditional
> evaluation or looping in psql scripts, for instance. Maybe the right
> answer is to teach people a little bit about using honest-to-goodness
> scripting languages when their applications reach this level of
> complexity. How much daylight is there between needing script
> variables and needing control flow, do you think?
I think I agree, but a powerful psql interface is very important for any
database.
> PS: not relevant to your main point, but to your example: I think it's
> a real bad idea to teach people to use OIDs as foreign keys. That'll
> create all kinds of trouble when it comes time to dump/reload their
> database. Better to tell them to use SERIAL columns as keys. Not so
> incidentally, we have currval() already...
OK, I am dealing with this in the book. What are oids good for then?
-- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
On Thu, 18 Nov 1999, Bruce Momjian wrote: > > If you want to attack this, I'd suggest thinking a little larger than > > just the last-OID problem. I'd like to be able to save off both > > insertion OIDs and values extracted by SELECTs into named variables > > of some sort, and then insert those values into as many later commands > > as I want. Right now there's no way to do any such thing in a psql > > script; you have to move up a level of difficulty into ecpg or pgtcl > > or even C code if your application needs this. Plain psql scripts > > would become substantially more powerful if psql had a capability > > like this. > we talked about this a few weeks ago as users... even those of us using C or higher level scripting languages agreed it would be nice to be able to have arbitrary values that are the result of an insert/update/delete able to be returned, without a subsequent select. if this made it into postgres, i think you'd have many happy users =) > OK, I am dealing with this in the book. What are oids good for then? > i can tell you what i use them for as someone who works with postgres daily... i'm not sure if this was what they were intended for.. but =) once inserted, a row keeps its oid. so, when performing complex selects, i'll often grab the oid too... do some tests on the returned values, and if an action is appropriate on that row, i reference it by its oid. the only chance of this failing is if the database is dumped then restored between the select and the update (not gonna happen, as the program requires the database available for execution)... using the oid this way, its often simpler and faster to update a known row, especially when the initial select involved many fields. -- Aaron J. Seigo Sys Admin
"Aaron J. Seigo" <aaron@gtv.ca> writes:
> On Thu, 18 Nov 1999, Bruce Momjian wrote:
>> OK, I am dealing with this in the book. What are oids good for then?
> once inserted, a row keeps its oid. so, when performing complex
> selects, i'll often grab the oid too... do some tests on the returned
> values, and if an action is appropriate on that row, i reference it by
> its oid. the only chance of this failing is if the database is dumped
> then restored between the select and the update (not gonna happen, as
> the program requires the database available for execution)... using
> the oid this way, its often simpler and faster to update a known row,
> especially when the initial select involved many fields.
Yes, I use 'em the same way. I think an OID is kind of like a pointer
in a C program: good for fast, unique access to an object within the
context of the execution of a particular application (and maybe not
even that long). You don't write pointers into files to be used again
by other programs, though, and in the same way an OID isn't a good
candidate for a long-lasting reference from one table to another.
regards, tom lane
> "Aaron J. Seigo" <aaron@gtv.ca> writes: > > On Thu, 18 Nov 1999, Bruce Momjian wrote: > >> OK, I am dealing with this in the book. What are oids good for then? > > > once inserted, a row keeps its oid. so, when performing complex > > selects, i'll often grab the oid too... do some tests on the returned > > values, and if an action is appropriate on that row, i reference it by > > its oid. the only chance of this failing is if the database is dumped > > then restored between the select and the update (not gonna happen, as > > the program requires the database available for execution)... using > > the oid this way, its often simpler and faster to update a known row, > > especially when the initial select involved many fields. > > Yes, I use 'em the same way. I think an OID is kind of like a pointer > in a C program: good for fast, unique access to an object within the > context of the execution of a particular application (and maybe not > even that long). You don't write pointers into files to be used again > by other programs, though, and in the same way an OID isn't a good > candidate for a long-lasting reference from one table to another. My feeling was that oid's are fine for joins in cases where the number is not visible to the user, because they are not sequential. Does that make sense, or is that too broad a usage? -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On 1999-11-18, Tom Lane mentioned: > It'd certainly be easy enough for psql to save off the OID anytime it > gets an "INSERT nnn" command response. The missing link is to invent > a way for a psql script to access that value and insert it into > subsequent SQL commands. Okay, I guess I'm way ahead of everyone here. It is in fact only a matter of adding a few lines to save the oid in a variable, and all the infrastructure for doing this is already present. In fact, I was going to do this in the next few days. testdb=> \set singlestep on testdb=> \set sql_interpol '#' testdb=> \set foo 'pg_class' testdb=> select * from #foo#; ***(Single step mode: Verify query)************** QUERY: select * from pg_class ***(press return to proceed or enter x and return to cancel)******************** x testdb=> > If you want to attack this, I'd suggest thinking a little larger than > just the last-OID problem. I'd like to be able to save off both > insertion OIDs and values extracted by SELECTs into named variables > of some sort, and then insert those values into as many later commands > as I want. Right now there's no way to do any such thing in a psql > script; you have to move up a level of difficulty into ecpg or pgtcl > or even C code if your application needs this. Plain psql scripts > would become substantially more powerful if psql had a capability > like this. Hmm, saving the SELECT results in a variable sounds like a great idea. I'll work on that. But in general, all the framework for this sort of thing is already there as you see. > OTOH: we shouldn't ask psql to do everything under the sun. I'd > certainly think that it'd be unreasonable to try to do conditional > evaluation or looping in psql scripts, for instance. Maybe the right I actually had (simple) conditional expressions on my list, but loops are not possible in the current design. Since I just redesigned it, I am quite hesitant to changing the design again. > answer is to teach people a little bit about using honest-to-goodness > scripting languages when their applications reach this level of > complexity. How much daylight is there between needing script > variables and needing control flow, do you think? Good question. It has been bothering me all along. The best answer to this is probably an interactive interpreter of some procedural language we offer. (I recall Oracle has their frontend that way.) Adding any more complex functionality to psql will probably cripple it beyond recognition. You can only go so far with hand-written parsers acting on poorly specified rules consisting of tons of backslashes. :) Anyway, good to see that all this "thinking big" might have had a point after all. -Peter -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
[Charset ISO-8859-1 unsupported, filtering to ASCII...] > On 1999-11-18, Tom Lane mentioned: > > > It'd certainly be easy enough for psql to save off the OID anytime it > > gets an "INSERT nnn" command response. The missing link is to invent > > a way for a psql script to access that value and insert it into > > subsequent SQL commands. > > Okay, I guess I'm way ahead of everyone here. It is in fact only a matter > of adding a few lines to save the oid in a variable, and all the > infrastructure for doing this is already present. In fact, I was going to > do this in the next few days. > > testdb=> \set singlestep on > testdb=> \set sql_interpol '#' > testdb=> \set foo 'pg_class' > testdb=> select * from #foo#; > ***(Single step mode: Verify query)************** > QUERY: select * from pg_class > ***(press return to proceed or enter x and return to > cancel)******************** > x > testdb=> This is exactly what I was hoping you would say. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026