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

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

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

Ok, the fact, that the row changed is known, because we can check the 
snapshot. We also know, that the new row must be near the physical end 
of the table, so maybe we could do a backward scan ?
Maybe we could also simply bail out, like Oracle with a "snapshot too old" 
error message  ?
(I know that this is not the same situation as the stated Oracle error)

> 
> > Is someone still working on the xid access ?
> 
> I think we have the ability to refer to CTID in WHERE now, 

Do we use the sql syntax 'where rowid = :xxx' for it, 
or do we say 'where ctid = :xxx'.
I would like the rowid naming, because Informix, Oracle (and DB/2 ?) use it.

> but not yet an access method that actually makes it fast...

Well that is of course only half the fun :-(
Could it be done like an index access, 
where the first part of the work is skipped, or tunneled through ?

Andreas


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

From
"Hiroshi Inoue"
Date:
> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Zeugswetter
> Andreas SEV
> Sent: Tuesday, November 23, 1999 5:58 PM
> To: 'pgsql-hackers@postgreSQL.org'
> Subject: AW: AW: [HACKERS] Getting OID in psql of recent insert 
> 
> > 
> > > Is someone still working on the xid access ?
> > 
> > I think we have the ability to refer to CTID in WHERE now, 
> 
> Do we use the sql syntax 'where rowid = :xxx' for it, 
> or do we say 'where ctid = :xxx'.
> I would like the rowid naming, because Informix, Oracle (and DB/2 
> ?) use it.
>

You could say 'where ctid= ...' in current tree.
It has been rejected due to the lack of equal operator for type TID. 
The syntax itself has been allowed by parser.
> > but not yet an access method that actually makes it fast...
> 
> Well that is of course only half the fun :-(
> Could it be done like an index access, 
> where the first part of the work is skipped, or tunneled through ?

I would commit the implementation of direct scan by tuple id soon.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp


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

From
Bruce Momjian
Date:
[Charset iso-8859-1 unsupported, filtering to ASCII...]
> 
> > >> 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?
> 
> Ok, the fact, that the row changed is known, because we can check the 
> snapshot. We also know, that the new row must be near the physical end 
> of the table, so maybe we could do a backward scan ?
> Maybe we could also simply bail out, like Oracle with a "snapshot too old" 
> error message  ?
> (I know that this is not the same situation as the stated Oracle error)

That is too strange.  If the tuple is superceeded, not sure how to
handle that.  My guess is that we just let them access it.  How do we
know if it is still a valid tuple in their own transaction?  I am unsure
of this, though.  Maybe there is a way to know.

> 
> > 
> > > Is someone still working on the xid access ?
> > 
> > I think we have the ability to refer to CTID in WHERE now, 
> 
> Do we use the sql syntax 'where rowid = :xxx' for it, 
> or do we say 'where ctid = :xxx'.
> I would like the rowid naming, because Informix, Oracle (and DB/2 ?) use it.

Is Informix rowid an actual physical row location.  If so, it would be
nice to auto-rename the column references to ctid on input.  Good idea.

> 
> > but not yet an access method that actually makes it fast...
> 
> Well that is of course only half the fun :-(
> Could it be done like an index access, 
> where the first part of the work is skipped, or tunneled through ?

They get the location they ask for, or a failure.  Hunting around for
the new tuple seems like a real waste, and if someone vacuums, it is
gone, no?

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