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, Pennsylvania 19026

Thu, 18 Nov 1999 20:12:18 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Getting OID in psql of recent insert
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...

Thu, 18 Nov 1999 22:45:14 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Getting OID in psql of recent insert 
> 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, Pennsylvania 19026

Thu, 18 Nov 1999 23:16:13 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Getting OID in psql of recent insert
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

Thu, 18 Nov 1999 21:48:54 -0700
"Aaron J. Seigo" <aaron@gtv.ca>
Re: [HACKERS] Getting OID in psql of recent insert
"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

Fri, 19 Nov 1999 00:40:02 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Getting OID in psql of recent insert 
> "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, Pennsylvania 19026

Fri, 19 Nov 1999 07:36:30 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Getting OID in psql of recent insert
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


Sat, 20 Nov 1999 03:38:35 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] Getting OID in psql of recent insert 
[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, Pennsylvania 19026

Sat, 20 Nov 1999 19:55:03 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Getting OID in psql of recent insert