473,326 Members | 2,148 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Use of oids

While updating to 7.3.4 I note with some alarm the following passage in
README.Debian.migration.gz written by Oliver Elphick:

"Some schema designs rely on the use of oids as row identifiers. This is
definitely not recommended, not least because oids are not guaranteed to
exist in all future versions of PostgreSQL. Oids are an internal feature
only. They are not suitable as candidate keys, since they are not
guaranteed to be unique; furthermore, the starting point for oids is likely
to change whenever a change to the database structure occurs."

While I have not used oids to join tables, I have used them extensively in
programming, because if Postgres has supplied a unique number for each row,
why on earth should I bother supplying another one of my own?

Like many people starting with Postgres, three or four years ago I
carefully read Bruce Moynihan's excellent introductory book on Postgres,
which explained many initially difficult concepts with such clarity.

The book states "Every row in POSTGRESQL is assigned a unique, normally
invisible number called an object identification number (OID). When the
software is initialized with initdb, 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. It is used by all databases, so identification
numbers are always unique. No two rows in any table or in any database will
ever have the same object ID."

Further down we read:
"Object identification numbers can be used as primary and foreign key
values in joins. Since every row has a unique object ID, a separate column
is not needed to hold the row's unique number."

On the next page are listed the limitations of oids, for example they are
nonsequential, nonmodifiable, and not backed up by default, but for my uses
these were not problems at all. I have merely used the oid number as a
temporary unique identifier before assigning a permanent booking number to
it, which takes about a nanosecond, and in other similar cases.

To sum up: The Debian migration gzip file declares that oids are not
guaranteed to be unique, issues dire warnings about using them as keys and
worst of all states that they may be phased out in the future.
The book states that they are unique, tells you how to use them, actually
gives an example of using them as primary and foreign keys (which
fortunately I decided was not very wise) and certainly doesn't say anything
about phasing them out in the future.

Can anybody shed any light on this?

Malcolm Warren

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #1
0 3389

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Amir Khawaja | last post by:
Hi Everyone, I have a rather trivial (I hope) question about OID types and PostgreSQL. Since PostgreSQL creates tables "WITH OIDS" by default, I'm wondering if it is bad practice to allow the...
12
by: btober | last post by:
I run three instances of a database, in a typical change-control scenario: Production, QAT, and DEV. The Production database is the "real" data, and we periodically take a back up from Prod and...
2
by: Amir Khawaja | last post by:
Hi Everyone, I have a rather trivial (I hope) question about OID types and PostgreSQL. Since PostgreSQL creates tables "WITH OIDS" by default, I'm wondering if it is bad practice to allow the...
5
by: Paul Groth | last post by:
Hi All, Having lots of dramas trying to pass an array of structures to a function. I manage to print to the elemnt of the first structure in the array and then I get a core dump. The code is...
1
by: Együd Csaba | last post by:
Hi, I'm winder if I can change the clause 'WITH OIDS' to 'WITHOUT OIDS' on a table without recreating it (eg. usong ALTER TABLE). The problem is that my tables are very big, and I do not need the...
2
by: Dan Sugalski | last post by:
I'm trying to properly tag the types of the parameters I'm passing into PQexecParams, as it seems to be the right thing to do, and it's not that big a deal given my existing code base....
1
by: Eran.Yasso | last post by:
Hi all, Is there a way to send OIDs request/set to miniport drivers? I understand that the only way is using WMI. Which method in WMI should be used? thanks.
1
by: ntech | last post by:
Hello, i am facing a problem. I am using postgresql database in Linux. After taking the dump of any postgresql database, when i restore the dump file into a new database, i dont get any oids. so can...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.