Thread: OIDs depending data -- how to dump/restore?

Hi all,

I have several databases here which I would like to update
from 8.2 to 8.4, which in turn requires a dump/restore.

However, the databases are OIDs depending, so, some values
depend on OIDs in other tables.

AFAIK the dump/restore does not rebuild the original OID
values, so all relations built accross OIDs fail.

(1)
Is there a way to keep the original OID values somehow?

(2)
If I need to go the long way and replace the OIDs with
SERIALs first, updating all relations to it etc: Would a
dump/restore then restore the original values in a SERIAL
column?

Thank You
 Felix

On 14/03/2010 16:21, fkater@googlemail.com wrote:

> (2)
> If I need to go the long way and replace the OIDs with
> SERIALs first, updating all relations to it etc: Would a
> dump/restore then restore the original values in a SERIAL
> column?

So-called SERIAL types are actually just integer columns which take
nextval('some_sequence') as their DEFAULT, so yes - if you first replace
your OIDs with serials, the values will still be there after a dump/restore.

This is probably the more robust way to go long-term.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On Sunday 14 March 2010 9:21:06 am fkater@googlemail.com wrote:
> Hi all,
>
> I have several databases here which I would like to update
> from 8.2 to 8.4, which in turn requires a dump/restore.
>
> However, the databases are OIDs depending, so, some values
> depend on OIDs in other tables.
>
> AFAIK the dump/restore does not rebuild the original OID
> values, so all relations built accross OIDs fail.
>
> (1)
> Is there a way to keep the original OID values somehow?

From here:
http://www.postgresql.org/docs/8.4/interactive/app-pgdump.html

-o
--oids

    Dump object identifiers (OIDs) as part of the data for every table. Use this
option if your application references the OID columns in some way (e.g., in a
foreign key constraint). Otherwise, this option should not be used.

>
> (2)
> If I need to go the long way and replace the OIDs with
> SERIALs first, updating all relations to it etc: Would a
> dump/restore then restore the original values in a SERIAL
> column?
>
> Thank You
>  Felix



--
Adrian Klaver
adrian.klaver@gmail.com

Adrian Klaver:

> > AFAIK the dump/restore does not rebuild the original OID
> > values, so all relations built accross OIDs fail.
> >
> > (1)
> > Is there a way to keep the original OID values somehow?
>
> From here:
> http://www.postgresql.org/docs/8.4/interactive/app-pgdump.html
>
> -o
> --oids
>
>     Dump object identifiers (OIDs) as part of the data for every table. Use this
> option if your application references the OID columns in some way (e.g., in a
> foreign key constraint). Otherwise, this option should not be used.

Thanks, but the problem is *restoring* OIDs afterwards,
isn't it?  AFAIK the OIDs being restored are not the same
values as the ones being saved, so my internal relations to
those OIDs are all mixed up after a restore.

I'd be happy if someone told me that this was wrong. :-)

 Felix

On Sunday 14 March 2010 1:09:37 pm fkater@googlemail.com wrote:
> Adrian Klaver:
> > > AFAIK the dump/restore does not rebuild the original OID
> > > values, so all relations built accross OIDs fail.
> > >
> > > (1)
> > > Is there a way to keep the original OID values somehow?
> >
> > From here:
> > http://www.postgresql.org/docs/8.4/interactive/app-pgdump.html
> >
> > -o
> > --oids
> >
> >     Dump object identifiers (OIDs) as part of the data for every table.
> > Use this option if your application references the OID columns in some
> > way (e.g., in a foreign key constraint). Otherwise, this option should
> > not be used.
>
> Thanks, but the problem is *restoring* OIDs afterwards,
> isn't it?  AFAIK the OIDs being restored are not the same
> values as the ones being saved, so my internal relations to
> those OIDs are all mixed up after a restore.
>
> I'd be happy if someone told me that this was wrong. :-)

Be happy then. If you do not specify the -o switch the oids are created on
demand when the dump file is restored and you get the situation you describe.
By specifying the -o switch you tell pg_dump to preserve the OIDS used in the
original database. This is why the following is mentioned in the above
description:

"Use this option if your application references the OID columns in some
way (e.g., in a foreign key constraint)."

>
>  Felix



--
Adrian Klaver
adrian.klaver@gmail.com