Re: 8.1, OID's and plpgsql

On Thu, Dec 01, 2005 at 07:18:10PM -0800, Uwe C. Schroeder wrote:
> Why not have something like the rowid in oracle?

http://www.postgresql.org/docs/8.1/interactive/datatype-oid.html, search
on ctid. And
http://www.postgresql.org/docs/8.1/interactive/ddl-system-columns.html.

From the 2nd URL:
ctid

    The physical location of the row version within its table. Note that
    although the ctid can be used to locate the row version very
    quickly, a row's ctid will change each time it is updated or moved
    by VACUUM FULL. Therefore ctid is useless as a long-term row
    identifier. The OID, or even better a user-defined serial number,
    should be used to identify logical rows.

Though I think that a lazy vacuum can change (well, technically remove)
a ctid. AFAIK, it's not safe to use a ctid outside of the transaction
you got it in. Though come to think about it, I don't think there's any
way to get the ctid of a row you just inserted anyway...

Maybe the docs should be changed to just say that you should never reuse
a ctid outside of the transaction you obtained the ctid in?
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461