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

Search Discussions

  • Tom Lane at Nov 19, 1999 at 3:46 am

    Bruce Momjian 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...
  • Bruce Momjian at Nov 19, 1999 at 4:16 am

    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
  • Aaron J. Seigo at Nov 19, 1999 at 5:15 am

    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
  • Tom Lane at Nov 19, 1999 at 5:41 am

    "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
  • Bruce Momjian at Nov 19, 1999 at 12:41 pm

    "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
  • Peter Eisentraut at Nov 21, 1999 at 12:04 am

    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
  • Bruce Momjian at Nov 21, 1999 at 1:04 am
    [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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedNov 19, '99 at 1:13a
activeNov 21, '99 at 1:04a
posts8
users4
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase