Re: Doubts about oid

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Jayadevan M <Jayadevan(dot)Maymala(at)ibsplc(dot)com>
Cc: adrian(dot)klaver(at)gmail(dot)com, pgsql-general(at)postgresql(dot)org, pgsql-general-owner(at)postgresql(dot)org
Subject: Re: Doubts about oid
Date: 2010-02-19 13:47:55
Message-ID: 20100219134754.GB5735@alvh.no-ip.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Jayadevan M escribió:

> I am an Oracle guy who is learning PostgreSQL. oid sounded a lot like
> rowid in Oracle. In Oracle, access by rowid is expected to be the fastest
> way of accessing a record, faster than even an index access followed by
> table access using the primary key. That was why I have this doubt about
> usage of oid being deprecated. Even if we use a sequence as PK (which is
> there in Oracle too), it is not as fast as access by rowid (I don't know
> if this applies to PostgreSQL's oid too). This is important when we use a
> cursors in an Oracle procedure (function in PostgreSQL) and loop through
> it and update specific records, when some conditions are met. Of course,
> that approach has its drawbacks -as in the case when row movement is
> enabled some maintenance activity moves the row to another location.

I suppose you could use a cursor and then

UPDATE ... WHERE CURRENT OF <the cursor>

> Another scenario is when we want to delete duplicate records in a table.

You can use the ctid system column for this. This column represents the
physical position of the row in the table, so it changes in many
situations, for example during an UPDATE.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Chris Barnes 2010-02-19 14:03:01 Re: Setting a table to be ignored by autovacuum
Previous Message Heddon's Gate Hotel 2010-02-19 13:10:29 PostgreSQL fails to start