Обсуждение: general question on OIDs

Hi,

Using of OIDs as foreign keys is recommended in the docs. It is a unique
number for every object of the PostgreSQL server. I can dump OIDs with
-o option.

But what happens if I have to upgrade my server? For example for the new
7.1 release an initdb is required. OIDs start from 16384 or something.
After importing my old databases can I expect that OIDs are not unique
any more? Or is there any other handling to prevent this?

Regards,
Oliver

Oliver Vecernik <vecernik@aon.at> writes:
> Using of OIDs as foreign keys is recommended in the docs.

Er ... where?  It shouldn't be.

> But what happens if I have to upgrade my server? For example for the new
> 7.1 release an initdb is required. OIDs start from 16384 or something.
> After importing my old databases can I expect that OIDs are not unique
> any more? Or is there any other handling to prevent this?

This is why it shouldn't be.  Reloading a pg_dump-with-OIDs will
probably yield a conflict-free database, but it's not certain;
and it is certain that you'll waste lots of OIDs that way.  Much
better to use serial columns for foreign keys.

            regards, tom lane

Tom Lane wrote:
>
> Oliver Vecernik <vecernik@aon.at> writes:
> > Using of OIDs as foreign keys is recommended in the docs.
>
> Er ... where?  It shouldn't be.

Sorry, it was not in the official docs, but in Bruce's book in the
chapter about OIDs:

| 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.
|
| For example, in Chapter  we used a column called customer.customer_id.
This
| column held the customer number and uniquely identified each row.
Alternatively, | we could have used the row's object identification
number as the unique number
| for each row, eliminating the need to create the | | column
customer.customer_id. | In that case, customer.oid would be the unique
customer number.

>
> [...]
>
> This is why it shouldn't be.  Reloading a pg_dump-with-OIDs will
> probably yield a conflict-free database, but it's not certain;
> and it is certain that you'll waste lots of OIDs that way.  Much
> better to use serial columns for foreign keys.
>
>                         regards, tom lane

Everthing is clear now. I won't use OIDs for foreign keys.

Regards,
Oliver