Re: OIDs as object handles? - Mailing list pgsql-general

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>