Thread: About OIDs

Hi, group,

(1) I found system tables are all created without OIDs.
But the default behavior of creating user tables are with OIDs.
Is there any benefit to create user tables with OIDs?

(2) I heard that OID usage at row level will be a future problems.

(3) What's your preference to create table with OIDs or not.

(4) Will drop the whole database release used OIDs?

Thank you

The OID concept is great.  Sadly, since they are only 32-bit they can be
problematic.  I usually create tables without OIDS and create a field
called object_id of type int8 that uses a global sequence called
'objects'.

The advantage OIDs still have is that OIDs are shown in the data
dictionary.Therefore, with OIDs, you can write a script to merge two
records, and search the data dictionary for any OID-related fields, and
update the related fields with the new OID for the merged record.  This
works even if you don't know what tables and columns may be referencing
your table beforehand.

Anyway, that's my take.  I'm interested in what other developers have to
say.

Jon

On Thu, 27 Mar 2003, Flower Sun wrote:

>
> Hi, group,
>
> (1) I found system tables are all created without OIDs.
> But the default behavior of creating user tables are with OIDs.
> Is there any benefit to create user tables with OIDs?
>
>
> (2) I heard that OID usage at row level will be a future problems.
>
>
> (3) What's your preference to create table with OIDs or not.
>
> (4) Will drop the whole database release used OIDs?
>
> Thank you
>
>
>
>
> ---------------------------------
> Do you Yahoo!?
> Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!

On Thu, Mar 27, 2003 at 12:42:37PM -0800, Flower Sun wrote:

> (1) I found system tables are all created without OIDs.

There's no way to do this.  All system tables have OIDs.

> But the default behavior of creating user tables are with OIDs.
> Is there any benefit to create user tables with OIDs?

No, unless you explicitly use them.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Et put se mouve" (Galileo Galilei)

> -----Original Message-----
> From: Alvaro Herrera [mailto:alvherre@dcc.uchile.cl]
> Sent: Thursday, March 27, 2003 1:29 PM
> To: Flower Sun
> Cc: Pgsql Group
> Subject: Re: [GENERAL] About OIDs
>
>
> On Thu, Mar 27, 2003 at 12:42:37PM -0800, Flower Sun wrote:
>
> > (1) I found system tables are all created without OIDs.
>
> There's no way to do this.  All system tables have OIDs.
>
> > But the default behavior of creating user tables are with OIDs. Is
> > there any benefit to create user tables with OIDs?
>
> No, unless you explicitly use them.

They are very handy for our ODBC driver.  It ensures that there is
always some sort of unique identifier for any table, even if the
designer did not create a primary key.  (Sure, that's a naughty no-no,
but it is astonishing how often it happens).  At any rate, some complex
joins can be sped up enormously by the existence of Odes.

Hi, Alvaro,

Thank you for your reply. Well, if you view the system defination from PgAdminII 1.4.12, you will see the following one example which shows that no OIDs. I am not sure whether PgadminII is right or you are right on this topic.

-- Table: pg_amop

CREATE TABLE pg_amop (

amopclaid oid,

amopstrategy int2,

amopreqcheck bool,

amopopr oid,

CONSTRAINT pg_amop_opc_opr_index UNIQUE (amopclaid, amopopr),

CONSTRAINT pg_amop_opc_strategy_index UNIQUE (amopclaid, amopstrategy)

) WITHOUT OIDS;

 Alvaro Herrera <alvherre@dcc.uchile.cl> wrote:

On Thu, Mar 27, 2003 at 12:42:37PM -0800, Flower Sun wrote:

> (1) I found system tables are all created without OIDs.

There's no way to do this. All system tables have OIDs.

> But the default behavior of creating user tables are with OIDs.
> Is there any benefit to create user tables with OIDs?

No, unless you explicitly use them.

--
Alvaro Herrera ()
"Et put se mouve" (Galileo Galilei)

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> On Thu, Mar 27, 2003 at 12:42:37PM -0800, Flower Sun wrote:
>> (1) I found system tables are all created without OIDs.

> There's no way to do this.  All system tables have OIDs.

Some do, some don't.  We don't bother with OIDs in the tables that have
no need for them (because their entries don't define referenceable
objects).

            regards, tom lane

> but it is astonishing how often it happens).  At any rate, some complex
> joins can be sped up enormously by the existence of Odes.

Like what?  I'm very curious on this point.

Jon

>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

On Thu, Mar 27, 2003 at 01:44:06PM -0800, Dann Corbit wrote:
> They are very handy for our ODBC driver.  It ensures that there is
> always some sort of unique identifier for any table, even if the
> designer did not create a primary key.  (Sure, that's a naughty no-no,
> but it is astonishing how often it happens).  At any rate, some complex
> joins can be sped up enormously by the existence of Odes.

OIDs are not necessarily unique. How do you handle that?

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
>   - Samuel P. Huntington

> -----Original Message-----
> From: Martijn van Oosterhout [mailto:kleptog@svana.org]
> Sent: Thursday, March 27, 2003 5:39 PM
> To: Dann Corbit
> Cc: Pgsql Group
> Subject: Re: [GENERAL] About OIDs
>
>
> On Thu, Mar 27, 2003 at 01:44:06PM -0800, Dann Corbit wrote:
> > They are very handy for our ODBC driver.  It ensures that there is
> > always some sort of unique identifier for any table, even if the
> > designer did not create a primary key.  (Sure, that's a
> naughty no-no,
> > but it is astonishing how often it happens).  At any rate, some
> > complex joins can be sped up enormously by the existence of Odes.
>
> OIDs are not necessarily unique. How do you handle that?

I do assume that they are unique within a single table's Oid set.
However, I create a unique index on the Oid column, so even when the
counter does wrap, I get a failure and an error message (yes, it has
happened).

When the failure does occur, I am forced to drop the database and
rebuild it.  I will have a better solution once the 7.x official win32
port becomes available.  God willing and the crick don't rise.

Hi, Dann,

Do you think every database within a postgresql server has its own 32bits OID to use?

Thank you

 Dann Corbit <DCorbit@connx.com> wrote:

> -----Original Message-----
> From: Martijn van Oosterhout [mailto:kleptog@svana.org]
> Sent: Thursday, March 27, 2003 5:39 PM
> To: Dann Corbit
> Cc: Pgsql Group
> Subject: Re: [GENERAL] About OIDs
>
>
> On Thu, Mar 27, 2003 at 01:44:06PM -0800, Dann Corbit wrote:
> > They are very handy for our ODBC driver. It ensures that there is
> > always some sort of unique identifier for any table, even if the
> > designer did not create a primary key. (Sure, that's a
> naughty no-no,
> > but it is astonishing how often it happens). At any rate, some
> > complex joins can be sped up enormously by the existence of Odes.
>
> OIDs are not necessarily unique. How do you handle that?

I do assume that they are unique within a single table's Oid set.
However, I create a uni que index on the Oid column, so even when the
counter does wrap, I get a failure and an error message (yes, it has
happened).

When the failure does occur, I am forced to drop the database and
rebuild it. I will have a better solution once the 7.x official win32
port becomes available. God willing and the crick don't rise.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-----Original Message-----
From: Flower Sun [mailto:sun_2002_flower@yahoo.com]
Sent: Friday, March 28, 2003 9:13 AM
To: Dann Corbit; Pgsql Group
Subject: Re: [GENERAL] About OIDs


Hi, Dann,
Do you think every database within a postgresql server has its own
32bits OID to use?
>>
No.  You can rely on them being present only if you build the tables
yourself.

Many other database systems have a similar idea.  For instance, Oracle's
Rdb has a DBKEY.

In Codd's latest "Database Manifesto" he suggests doing away with them
(along with SQL itself).
I think he has been sitting in an ivory tower too long, but what do I
know.
<<

If OIDS are for the whole postgresql server, not for individual database.

You have to drop all the databases in order to reuse OIDS, I think, which is not good for those companys who let host company hosting their databases. Because there are many other companys using the same postgresql server at the same time. And you have no idea whether other companys' tables are created with OIDS or not.

I think OID is equivlent to Oracle ROWID which is 64bits right now. ROWID don't have exhaustion problem.

 Dann Corbit <DCorbit@connx.com> wrote:

-----Original Message-----
From: Flower Sun [mailto:sun_2002_flower@yahoo.com]
Sent: Friday, March 28, 2003 9:13 AM
To: Dann Corbit; Pgsql Group
Subject: Re: [GENERAL] About OIDs


Hi, Dann,
Do you think every database within a postgresql server has its own
32bits OID to use?
>>
No. You can rely on them being present only if you build the tables
yourself.

Many other database systems have a similar idea. For instance, Oracle's
Rdb has a DBKEY.

In Codd's latest "Database Manifesto" he suggests doing away with them
(along with SQL itself).
I think he has been sitting in an ivory tower too long, but what do I
know.
<<


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Thu, Mar 27, 2003 at 01:14:30PM -0800, Jonathan Bartlett wrote:
> update the related fields with the new OID for the merged record.  This
> works even if you don't know what tables and columns may be referencing
> your table beforehand.

Really?  Since there's no guarantee that OIDs are unique across the
system, that sounds like a terrible recipe for breakage to me.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110