pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMigration \ OID question
4+ messages / 2 participants
[nested] [flat]
* Migration \ OID question
@ 2008-12-19 20:07 Lipker, Joseph <Joseph.Lipker@WRECO1.com>
2008-12-20 04:02 ` Re: Migration \ OID question Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Lipker, Joseph @ 2008-12-19 20:07 UTC (permalink / raw)
To: pgsql-admin
Have a question concerning how OID's are generated and assigned to tables in postgres.
The application I inherited relies upon the oid's for primary keys.
I am currently in process of migrating our current Postgres Database from one server [Postgres version 7.2] to another server [version 8.1.9]
PostgreSQL software was installed off the Linux 5 enterprise CD by our server team.
I have been able to perform and pgdump -o of the 7.2 database and restore to the 8.1.9 server without any errors.
However, I have noticed that the oids being generated and assigned when adding new records appear to have restarted from 17901. The current oid assigned from version 7.2 was 203199999.
The question is, after reloading the 7.2 version into the 8.19 version, should the migrated database be starting with the 203199999 as last oid and then assign 203200000 as the next oid?
What it is appears to be doing is restarting from beginning.
What I am concerned about is when the 8.1.9 version assigns an oid that already exists.
Any help on why this is happening would be appreciated.
Thanks,
Joe
Joseph Lipker
Weyerhaeuser Real Estate Company - IT Department
EC3-3C8
Federal Way, WA 98063-9777
Office: 253-924-5994
Cell: 253-249-6819
joseph.lipker@wreco1.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Migration \ OID question
2008-12-19 20:07 Migration \ OID question Lipker, Joseph <Joseph.Lipker@WRECO1.com>
@ 2008-12-20 04:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-12-22 18:07 ` Re: Migration \ OID question Lipker, Joseph <Joseph.Lipker@WRECO1.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2008-12-20 04:02 UTC (permalink / raw)
To: Lipker, Joseph <Joseph.Lipker@WRECO1.com>; +Cc: pgsql-admin
"Lipker, Joseph" <Joseph.Lipker@WRECO1.com> writes:
> The application I inherited relies upon the oid's for primary keys.
It'd be a good idea to move away from that.
> The question is, after reloading the 7.2 version into the 8.19 version, should the migrated database be starting with the 203199999 as last oid and then assign 203200000 as the next oid?
No.
> What I am concerned about is when the 8.1.9 version assigns an oid that already exists.
If you have a unique index on the table's OID column (as one would hope,
else it's not good for much) then 8.1 and up will avoid assigning
duplicate OIDs. There might be some performance glitches if you have
very long runs of consecutive OIDs in the same table, though.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Migration \ OID question
2008-12-19 20:07 Migration \ OID question Lipker, Joseph <Joseph.Lipker@WRECO1.com>
2008-12-20 04:02 ` Re: Migration \ OID question Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-12-22 18:07 ` Lipker, Joseph <Joseph.Lipker@WRECO1.com>
2008-12-23 05:02 ` Re: Migration \ OID question Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Lipker, Joseph @ 2008-12-22 18:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Thanks Tom.
Unfortunately, none of the tables oid column have a unique index on them at this time. Will add them to adjust for this.
You mentioned "performance glitches". What would those be? Errors or system performance slowdowns.
Joseph Lipker
>Weyerhaeuser Real Estate Company - IT Department
>EC3-3C8
>Federal Way, WA 98063-9777
>Office: 253-924-5994
Cell: 253-249-6819
joseph.lipker@wreco1.com
>
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, December 19, 2008 8:02 PM
To: Lipker, Joseph
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Migration \ OID question
"Lipker, Joseph" <Joseph.Lipker@WRECO1.com> writes:
> The application I inherited relies upon the oid's for primary keys.
It'd be a good idea to move away from that.
> The question is, after reloading the 7.2 version into the 8.19 version, should the migrated database be starting with the 203199999 as last oid and then assign 203200000 as the next oid?
No.
> What I am concerned about is when the 8.1.9 version assigns an oid that already exists.
If you have a unique index on the table's OID column (as one would hope, else it's not good for much) then 8.1 and up will avoid assigning duplicate OIDs. There might be some performance glitches if you have very long runs of consecutive OIDs in the same table, though.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Migration \ OID question
2008-12-19 20:07 Migration \ OID question Lipker, Joseph <Joseph.Lipker@WRECO1.com>
2008-12-20 04:02 ` Re: Migration \ OID question Tom Lane <tgl@sss.pgh.pa.us>
2008-12-22 18:07 ` Re: Migration \ OID question Lipker, Joseph <Joseph.Lipker@WRECO1.com>
@ 2008-12-23 05:02 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2008-12-23 05:02 UTC (permalink / raw)
To: Lipker, Joseph <Joseph.Lipker@WRECO1.com>; +Cc: pgsql-admin
"Lipker, Joseph" <Joseph.Lipker@WRECO1.com> writes:
> You mentioned "performance glitches". What would those be? Errors or system performance slowdowns.
Slowdowns --- specifically, the behavior is "generate next sequential
OID, probe index to see if it exists in table, repeat until not".
So if you've got a long run of consecutive OIDs in the same table,
when the OID counter reaches that point it could take a while to
assign the next free OID successfully. This would manifest as a slower
than expected INSERT.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-12-23 05:02 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-12-19 20:07 Migration \ OID question Lipker, Joseph <Joseph.Lipker@WRECO1.com>
2008-12-20 04:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-12-22 18:07 ` Lipker, Joseph <Joseph.Lipker@WRECO1.com>
2008-12-23 05:02 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox