pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Nielsen <psql@www.tcu-inc.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: how do I update a field with a particular oid?
Date: Mon, 03 Aug 1998 10:07:57 -0400
Message-ID: <25906.902153277@sss.pgh.pa.us> (raw)
In-Reply-To: <199808030152.VAA24510@www.tcu-inc.com>

Mark Nielsen <psql@www.tcu-inc.com> writes:
> Anyways, my question is, I want to update specific rows according to their
> oids. Oh, I am also using the perl module Pg. I can find out the oid of
> the data retrieved, but I want to update data according to their oid. 

There's more to this than meets the eye, actually.  I just went through
it, and while it's easy to make it work, making it work *efficiently*
is another story.  The main thing is that you must create an index on
OID.  Without that, the system resorts to sequential scan of the whole
table to locate the right row --- there are no special smarts about
finding rows by OID, it turns out.  So:

	create index table_oid_index on table using btree (oid);

Now, to find out OID of an interesting row, you do something like:

	select oid,* from table where (conditions);

To update a row targeted by OID, eg OID 123456:

	update table set ... where oid = 123456::oid;

Note the explicit cast of the integer value to type OID.  For some
reason the OID index won't be used unless you do that.  (I think this
is probably a bug, but that's how the current sources behave.)

			regards, tom lane



Message-ID: <25906.902153277@sss.pgh.pa.us>
Permalink:  ../25906.902153277@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/25906.902153277@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, psql@www.tcu-inc.com
  Subject: Re: how do I update a field with a particular oid?
  In-Reply-To: <25906.902153277@sss.pgh.pa.us>
  References: <199808030152.VAA24510@www.tcu-inc.com> <25906.902153277@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox