Thread: Question on oid's
My first question is about oid's. What are they exactly and when should/shouldn't I use them. I had trouble finding any information on them in either the online documentation or the online books. I'm using pgadmin to develop my database and I noticed that once I select or don't select oid that I can't change my mind.
Thanks for you help.
Julie
Dont use OID's when your table has a huge amount of data.
ie +2147483647 records.
then your oid will roll over and will not be usefull.
----- Original Message -----
From: Juliet MaySent: Tuesday, April 09, 2002 10:36 PMSubject: [NOVICE] Question on oid'sMy first question is about oid's. What are they exactly and when should/shouldn't I use them. I had trouble finding any information on them in either the online documentation or the online books. I'm using pgadmin to develop my database and I noticed that once I select or don't select oid that I can't change my mind.Thanks for you help.Julie
Juliet, > My first question is about oid's. What are they exactly and when > should/shouldn't I use them. I had trouble finding any information on > them in either the online documentation or the online books. I'm > using pgadmin to develop my database and I noticed that once I select > or don't select oid that I can't change my mind. We need a FAQ on this. Sigh. OIDs are for system use. Occasionally some developers will build transaction tables, to which thousands or millions of records will be written and erased per day. For that case (and that case only) they will build "OID-less tables" in Postgres 7.2. Otherwise, you should build your tables with OIDs, but then ignore the OIDs and not use them as an index. The system helps by "hiding" the OID column from you. Should you run across documentation suggesting that you use the OID as an index or primary, key, that documentation is outdated. -Josh Berkus
I am also new to Postgresql though not ORDBMS systems ... why should you not use OIDs as a primary key in Postgresql? Joe ----- Original Message ----- From: "Josh Berkus" <josh@agliodbs.com> To: "Juliet May" <jmay@speark.com>; <pgsql-novice@postgresql.org> Sent: Tuesday, April 09, 2002 11:19 PM Subject: Re: [NOVICE] Question on oid's > Juliet, > > > My first question is about oid's. What are they exactly and when > > should/shouldn't I use them. I had trouble finding any information on > > them in either the online documentation or the online books. I'm > > using pgadmin to develop my database and I noticed that once I select > > or don't select oid that I can't change my mind. > > We need a FAQ on this. Sigh. > > OIDs are for system use. Occasionally some developers will build > transaction tables, to which thousands or millions of records will be > written and erased per day. For that case (and that case only) they > will build "OID-less tables" in Postgres 7.2. > > Otherwise, you should build your tables with OIDs, but then ignore the > OIDs and not use them as an index. The system helps by "hiding" the > OID column from you. > > Should you run across documentation suggesting that you use the OID as > an index or primary, key, that documentation is outdated. > > -Josh Berkus > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
On Wed, 2002-04-10 at 18:38, Joseph Molnar wrote:
> I am also new to Postgresql though not ORDBMS systems ... why should you not
> use OIDs as a primary key in Postgresql?
Because they will be different next time you load your data from a dump.
Cheers,
Andrew.
>
> ----- Original Message -----
> From: "Josh Berkus" <josh@agliodbs.com>
> To: "Juliet May" <jmay@speark.com>; <pgsql-novice@postgresql.org>
> Sent: Tuesday, April 09, 2002 11:19 PM
> Subject: Re: [NOVICE] Question on oid's
>
>
> > Juliet,
> >
> > > My first question is about oid's. What are they exactly and when
> > > should/shouldn't I use them. I had trouble finding any information on
> > > them in either the online documentation or the online books. I'm
> > > using pgadmin to develop my database and I noticed that once I select
> > > or don't select oid that I can't change my mind.
> >
> > We need a FAQ on this. Sigh.
> >
> > OIDs are for system use. Occasionally some developers will build
> > transaction tables, to which thousands or millions of records will be
> > written and erased per day. For that case (and that case only) they
> > will build "OID-less tables" in Postgres 7.2.
> >
> > Otherwise, you should build your tables with OIDs, but then ignore the
> > OIDs and not use them as an index. The system helps by "hiding" the
> > OID column from you.
I don't agree with this statement though. I don't personally see much
value in having OID's in my tables in general. Obviously they are
needed for (most) system tables, but why should I bother with them
normally.
What system use is made of OIDs in non-system tables? None?
> > Should you run across documentation suggesting that you use the OID as
> > an index or primary, key, that documentation is outdated.
And was possibly suspect in the first place.
Cheers,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?
Hmm, so I guess they did that to get around the problem of converging an already loaded set and what was on disk..which basically means their algorithm for oid creation is not guaranteeing uniqueness between 'installations'. That does seem a bit strange. It is not uncommon (love double-negatives ;) in an OR system to form a relationship along an OID and then building application level (or stored procs) constraints to guarantee the object is of the right type (ie an Address can be owned-by a Person or a Company, but not an Invoice). What are oid's used for then, other than simple DB management? I would presume that they cannot be used by the inheritance relationships or other interesting features of Postgresql since they would fall into the same issues as above..?? Joe ----- Original Message ----- From: "Andrew McMillan" <andrew@catalyst.net.nz> To: "Joseph Molnar" <josephmolnar@hotmail.com> Cc: "Josh Berkus" <josh@agliodbs.com>; <pgsql-novice@postgresql.org> Sent: Wednesday, April 10, 2002 3:02 AM Subject: Re: [NOVICE] Question on oid's > On Wed, 2002-04-10 at 18:38, Joseph Molnar wrote: > > I am also new to Postgresql though not ORDBMS systems ... why should you not > > use OIDs as a primary key in Postgresql? > > Because they will be different next time you load your data from a dump. > > Cheers, > Andrew. > > > > ----- Original Message ----- > > From: "Josh Berkus" <josh@agliodbs.com> > > To: "Juliet May" <jmay@speark.com>; <pgsql-novice@postgresql.org> > > Sent: Tuesday, April 09, 2002 11:19 PM > > Subject: Re: [NOVICE] Question on oid's > > > > > > > Juliet, > > > > > > > My first question is about oid's. What are they exactly and when > > > > should/shouldn't I use them. I had trouble finding any information on > > > > them in either the online documentation or the online books. I'm > > > > using pgadmin to develop my database and I noticed that once I select > > > > or don't select oid that I can't change my mind. > > > > > > We need a FAQ on this. Sigh. > > > > > > OIDs are for system use. Occasionally some developers will build > > > transaction tables, to which thousands or millions of records will be > > > written and erased per day. For that case (and that case only) they > > > will build "OID-less tables" in Postgres 7.2. > > > > > > Otherwise, you should build your tables with OIDs, but then ignore the > > > OIDs and not use them as an index. The system helps by "hiding" the > > > OID column from you. > > I don't agree with this statement though. I don't personally see much > value in having OID's in my tables in general. Obviously they are > needed for (most) system tables, but why should I bother with them > normally. > > What system use is made of OIDs in non-system tables? None? > > > > > Should you run across documentation suggesting that you use the OID as > > > an index or primary, key, that documentation is outdated. > > And was possibly suspect in the first place. > > Cheers, > Andrew. > > -- > -------------------------------------------------------------------- > Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington > WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St > DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267 > Are you enrolled at http://schoolreunions.co.nz/ yet? > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Joe, > I am also new to Postgresql though not ORDBMS systems ... why should > you not > use OIDs as a primary key in Postgresql? 1. OIDs are not normally backed up, and it requires special pg_dump options to backup and restore them, and there can be problems in this process. 2. If you have a very large database with a lot of activity, the OID counter will "roll over" which is not a problem for the system, but will be for any OID-based primary keys. 3. It's not great design to make a column which is normally hidden in queries your primary key. -Josh Berkus