Re: OID's - Mailing list pgsql-general

On Sat, 2004-10-23 at 17:46 +0200, Leen Besselink wrote:
> Eddy Macnaghten zei:
> > I think you are correct in not using OIDs, as, firstly, as you point out
> > they are optional, also that they are not neccessarily unique.
> >
>
> I'm sorry Eddy, but you most be mistaken:
>
> Every row in POSTGRESQL is assigned a unique, normally invisible number
> called an object identification number (OID). When the software is
> initialized with initdb , 12.1 a counter is created and set to
> approximately seventeen-thousand. The counter is used to uniquely number
> every row. Although databases may be created and destroyed, the counter
> continues to increase.
>
> http://www.postgresql.org/docs/aw_pgsql_book/node71.html

That is dated 2002.  It is now possible to create a table without oids,
and oids are not guaranteed always to exist in all future releases.  It
is likely that the default table creation will switch to being without
oids soon; that can already be specified as the default (in 8.0beta3).

Oids are not guaranteed to be unique, since they wrap round when they
reach the end of their range.  If you wanted to use an oid as a
guaranteed unique id, you would need to add a unique index on the oid
column for that table; that could then cause an insertion to fail if an
oid in the table were to be reused.  If it were a very large table, that
would cause the application to fail, because many insertions would be
likely to fail after the wrap-around.

> > The use of sequences is an idea, however, why the complication?  Why not
> > simply use a sequence called "mytable_sequence", or "mytable_id" where
> > "mytable" is the name of the table? (or some other such standard).
> >
>
> Because a lot of the time we query databases we did not create our selfs,
> we were looking for a general way, to handle it.

Reliance on a database feature, such as oids, as a key is a sign of bad
design; a table row ought to have a unique key of some kind, and if you
insert that row, you must know what that key is.  If there is no other
way to distinguish it, you can add a serial column for the sole purpose
of providing a primary key.  That would be part of the data rather than
a side-effect of the implementation.

I can't see how the use of oids would help you with a database of
someone else's design, unless the designer used that feature already.


> > The other thing to be aware of is if a large number of people are
> > writing to the database concurrently it can go wrong (any method).  That
> > is if you insert a record (using nextval for the sequence), then someone
> > else quickly inserts a row too before you have a chance to get the
> > sequence number at the next statement then the sequence number you get
> > will be wrong (it would be of the new one, not yours).  This would be
> > the case regardless of how the records are committed.
> >
>
> I thought that was the whole idea of sequences, each call to nextval ()
> will actually give you a unique number for that sequence (unless ofcourse
> it it wraps..)

You are correct.  nextval() is guaranteed never to give the same number
(unless setval() were used to reset the sequence value).  A lot of
people seem not to understand that.  The trade-off is that sequences are
not rolled back if a transaction is aborted.

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "I press toward the mark for the prize of the high
      calling of God in Christ Jesus."
                                   Philippians 3:14