Re: OIDs as object handles?

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(at)bumba(dot)net>