Thread: AW: [HACKERS] Getting OID in psql of recent insert

AW: [HACKERS] Getting OID in psql of recent insert

From
Zeugswetter Andreas SEV
Date:
> > 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
> 

I thought this special case is where the new xid access method would come
in.
It is actually a lot faster than oid access, since it marks the physical
position
that would otherwise need to be looked up in the oid index. This same oid
index
would also add unneeded overhead to the inserts and updates.

Is someone still working on the xid access ?

Andreas


Re: AW: [HACKERS] Getting OID in psql of recent insert

From
Tom Lane
Date:
Zeugswetter Andreas SEV <ZeugswetterA@wien.spardat.at> writes:
>> 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.

> I thought this special case is where the new xid access method would come
> in.

Good point, but (AFAIK) you could only use it for tables that you were
sure no other client was updating in parallel.  Otherwise you might be
updating a just-obsoleted tuple.  Or is there a solution for that?

> Is someone still working on the xid access ?

I think we have the ability to refer to CTID in WHERE now, but not yet an
access method that actually makes it fast...
        regards, tom lane


Re: AW: [HACKERS] Getting OID in psql of recent insert

From
Bruce Momjian
Date:
> Zeugswetter Andreas SEV <ZeugswetterA@wien.spardat.at> writes:
> >> 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.
> 
> > I thought this special case is where the new xid access method would come
> > in.
> 
> Good point, but (AFAIK) you could only use it for tables that you were
> sure no other client was updating in parallel.  Otherwise you might be
> updating a just-obsoleted tuple.  Or is there a solution for that?
> 
> > Is someone still working on the xid access ?
> 
> I think we have the ability to refer to CTID in WHERE now, but not yet an
> access method that actually makes it fast...

Hiroshi supplied a patch to allow it in the executor, and I applied it.

--  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,
Pennsylvania19026