Thread: oids vs. serial question

Hi, I'm new to PostreSQL, and I fear this might be a FAQ.

I am considering using OIDs for my application. I like the fact that
they are just automagic. However, the magic seems to disappear when it
comes to dumping and restoring data. Is there a way to get references to
oids across tables get restored correctly? I see that pg_dump has --oids
and pg_restore has --oid-order. But when I do a restore using
--oid-order, my oid references are all off by a constant number.  For
example, if I have a oid column that is a references between these two
tables:

CREATE TABLE park (
   region_oid oid references region,
   primary key (oid),
...
);

CREATE TABLE region (
   PRIMARY KEY  (oid),
...
) ;


Then I do
pg_dump -Fc --oids > my.db
[drop tables]
pg_restore -Fc --oid-order my.db

Then the region_oid column values do not match the current oids in
region.oid. It can be fixed with a query, but someone (me) is sure to
forget to do this cleanup.

update park set region_oid = region_oid + DELTA
where DELTA is the difference of min(region.oid) and min(park.region_oid)

Am I missing something with the use of dump and restore, or should I
just forget it and use SERIAL columns?

Alex Rice, Software Developer
Architectural Research Consultants, Inc.
alex_rice@arc.to
alrice@swcp.com

On Fri, Mar 01, 2002 at 05:10:47PM -0700, Alex Rice wrote:
...
> Am I missing something with the use of dump and restore, or should I
> just forget it and use SERIAL columns?

Personnally, serial sounds cleaner to me, so if it's an option, I'd go for it!

Patrick

Alex Rice schrieb:
> I am considering using OIDs for my application. I like the fact that
> they are just automagic. However, the magic seems to disappear when it

Hi,

as far as I understood the thing, take OIDs as 'internal' (they are
unique all over all databases) and do not use them to organize your
data.

> CREATE TABLE park (
>   region_oid oid references region,
>   primary key (oid),
> ...
> );
>
> CREATE TABLE region (
>   PRIMARY KEY  (oid),
> ...
> ) ;

Something like

  CREATE TABLE park (
    id serial primary key,
    region int4 references region,
    ...
  );

  CREATE TABLE region (
    id serial primary key,
    ...
  ) ;

should work.

Is SERIAL still INT4 in v7.2?

Bye,
Knut Sübert

> as far as I understood the thing, take OIDs as 'internal' (they are
> unique all over all databases) and do not use them to organize your
> data.

I think that sums it up nicely.

> Is SERIAL still INT4 in v7.2?

Yes, but there's now a BIGSERIAL (or SERIAL8 if you prefer).

Greg

> > as far as I understood the thing, take OIDs as 'internal' (they are
> > unique all over all databases) and do not use them to organize your
> > data.
>
> I think that sums it up nicely.
>
> > Is SERIAL still INT4 in v7.2?
>
> Yes, but there's now a BIGSERIAL (or SERIAL8 if you prefer).

OH COOL!!!!  Very useful!  Is that in the ever famous, "7.2 docs" that
are still pending release?  ::hint hint::  -sc

--
Sean Chittenden

Sean Chittenden <sean@chittenden.org> writes:
> OH COOL!!!!  Very useful!  Is that in the ever famous, "7.2 docs" that
> are still pending release?  ::hint hint::  -sc

What "pending" release?

http://www.ca.postgresql.org/users-lounge/docs/#7.2
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/index.html

            regards, tom lane

> > OH COOL!!!!  Very useful!  Is that in the ever famous, "7.2 docs" that
> > are still pending release?  ::hint hint::  -sc
>
> What "pending" release?
>
> http://www.ca.postgresql.org/users-lounge/docs/#7.2
> http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/index.html

Sorry, I meant the interactive documentation:

http://www.postgresql.org/idocs/index.php

-sc

--
Sean Chittenden

On Mon, Mar 04, 2002 at 09:20:43PM -0800, Sean Chittenden wrote:
> > > as far as I understood the thing, take OIDs as 'internal' (they are
> > > unique all over all databases) and do not use them to organize your
> > > data.
> >
> > I think that sums it up nicely.
> >
> > > Is SERIAL still INT4 in v7.2?
> >
> > Yes, but there's now a BIGSERIAL (or SERIAL8 if you prefer).
>
> OH COOL!!!!  Very useful!  Is that in the ever famous, "7.2 docs" that
> are still pending release?  ::hint hint::  -sc


http://www.postgresql.org/users-lounge/docs/7.2/postgres/datatype.html#DATATYPE-SERIAL



- Andrew