Thread: OIDs as object handles?

    Greetings!

        There is a particular feature I find myself wanting:  Given a
table row, I'd like a handle to that row in storage so that I may access
it again quickly (ie. without the overhead of a SELECT).  I was
originally under the impression that the oid would suffice for this
purpose, but I encountered some documentation which indicated that oid
numbers might not be unique in a given table, so it seems this
functionality could not be present after all.

        Also, could someone explain the following (possibly related)
error?  My syntax may be completely off, but I'm trying to do just what
I describe above:

test=# CREATE TABLE foo (a int, b int);
CREATE
test=# CREATE TABLE bar (c foo, d, int);
CREATE
test=# INSERT INTO foo VALUES(3,4);
INSERT 19839 1
test=# INSERT INTO bar VALUES(19839::foo, 6);
INSERT 19840 1
test=# SELECT bar.c.a FROM bar;
ERROR:   fmgr_info: function 19839: cache lookup failed

    Cheers,
        dj trombley
        <dtrom@bumba.net>

Dave Trombley <dtrom@bumba.net> writes:
>         There is a particular feature I find myself wanting:  Given a
> table row, I'd like a handle to that row in storage so that I may access
> it again quickly (ie. without the overhead of a SELECT).

There isn't any such thing as a "handle to that row in storage",
primarily because there's no guarantee that the row is in memory at
all.  You could, however, use the row's ctid (physical location) to
access it quickly.

    select ctid, ... from table where <conditions>;

    ...

    select ... from table where ctid = 'value';

I'd only recommend that you do this within a transaction block, so that
you can be certain that the row doesn't get deleted or updated between
the two selects.  Otherwise there's the possibility of retrieving no
row, or even the wrong row at the second select.

>         Also, could someone explain the following (possibly related)
> error?

The rowtype-as-column functionality doesn't work; I'm not certain that
it ever has worked, at least not in the way you expect.  AFAICT, the
original Berkeley implementation involved expecting to find the OID of
a *function*, not a row, in the stored column value --- this function
would be executed to get the row(s) represented.  This might still work
if you cared to set it up, but I wouldn't be surprised to find it
suffering from bit rot.

            regards, tom lane

Tom Lane wrote:

>
>There isn't any such thing as a "handle to that row in storage",
>primarily because there's no guarantee that the row is in memory at
>all.  You could, however, use the row's ctid (physical location) to
>access it quickly.
>
    Right, that's exactly what I want.  If I needed it cached in memory
instead of physical storage, I could simply cache the whole row in my
application.  So, ctids are globally unique at any instant, but can
change for a given object over the lifetime of the database?

>
>
>    select ctid, ... from table where <conditions>;
>
>    ...
>
>    select ... from table where ctid = 'value';
>
>I'd only recommend that you do this within a transaction block, so that
>you can be certain that the row doesn't get deleted or updated between
>the two selects.  Otherwise there's the possibility of retrieving no
>row, or even the wrong row at the second select.
>
    I'd assume that your suggesting this as a solution implies that
scans on the ctid pseudo-column are special-cased; in particular they
don't get looked up in a hash or tree index and don't scan each row,
instead simply access the storage at that location.  Is this correct?

>
>The rowtype-as-column functionality doesn't work; I'm not certain that
>it ever has worked, at least not in the way you expect.  AFAICT, the
>original Berkeley implementation involved expecting to find the OID of
>a *function*, not a row, in the stored column value --- this function
>would be executed to get the row(s) represented.  This might still work
>if you cared to set it up, but I wouldn't be surprised to find it
>suffering from bit rot.
>
    Ah, that would explain this sort of thing, which also puzzled me:

test=# select foo.a.d;
ERROR:  Function 'd(int4)' does not exist
        Unable to identify a function that satisfies the given argument
types
        You may need to add explicit typecasts

    I supposed the development list is the place to take up the question
of how it ought to behave if a row is given (do row values get cached in
the same fashion?  I'll have to look at the source to understand this
all, I guess), but what are your impressions on that?  Morally speaking,
there ought to be a way to join a table with a row typed column with the
table that the row type is from.  I *think* this is actually done in
fact; in particular the JDBC object serialization stuff claims to use
it, if I'm reading
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/jdbc-ext.html#AEN18941
correctly.  Using my previous example with tables 'foo' and 'bar', I
can't seem to do it:

test=# select a,b from foo, bar where foo.oid = bar.c;
ERROR:  Unable to identify an operator '=' for types 'oid' and 'foo'
        You will have to retype this query using an explicit cast

test=# select a,b from foo, bar where foo.oid = oid(bar.c);
ERROR:  fmgr_info: function 19839: cache lookup failed

    Which is wholly consistent with what you have said.  I'm baffled.
 Time for me to hit the source.

    Cheers,
        dj trombley
        <dtrom@bumba.net>

Dave Trombley <dtrom@bumba.net> writes:
>     I'd assume that your suggesting this as a solution implies that
> scans on the ctid pseudo-column are special-cased;

Correct.  If you try EXPLAIN you should see a "Tid Scan" plan.

            regards, tom lane