Postgres Pro
Downloads
Home   >   mailing lists

Thread: OID assistance

OID assistance

From
"Spiegelberg, Greg"
Date:
Hello,

It's come to our attention that in 14-16 days our OID's will wrap around and
we need some advice.  We're running 7.3.5 in our current production and plan
to eventually move to 7.4.6 which we have in testing.  Production consists
of one database cluster with 26 databases.  All tables are created with the
default, ie. WITH OIDS.  We have many tables that have an OID type column
which is used for storing files.  We intent to migrate away from that but
that too is in testing only.

We have thought about doing a dump and restoring to a new database cluster
but at the current rate we'll be in the same boat in roughly 120 days.  We
have also thought of doing a dump and to 7.4.6 and performing an "ALTER
TABLE SET WITHOUT OIDS" on every table.

Anything else we can do?  Have I read too much into the OID wrap-around
problem?

Greg

--
Greg Spiegelberg
  Product Development Manager
  Cranel, Incorporated.
  Phone: 614.318.4314
  Fax:   614.431.8388
  Email: gspiegelberg@cranel.com
Technology. Integrity. Focus.

Re: OID assistance

From
Tom Lane
Date:
"Spiegelberg, Greg" <gspiegelberg@cranel.com> writes:
> It's come to our attention that in 14-16 days our OID's will wrap around and
> we need some advice.

Are you actually using the OIDs for anything?  If not, don't worry about
it.

Unless you are actually depending on OIDs to be unique in some of your
user tables, wraparound shouldn't matter.  The only possible bad
consequence is that you might once in a while get a collision in the
system tables (eg, a CREATE TABLE fails because the OID generated for
the table conflicts with an existing table).  The odds of that are small
enough that it's probably not going to be as much of a problem as a
dump/reload would be.  Just retry the transaction and it'll usually
work the next time.

            regards, tom lane

Re: OID assistance

From
Greg Spiegelberg
Date:
Tom Lane wrote:
> "Spiegelberg, Greg" <gspiegelberg@cranel.com> writes:
>
>>It's come to our attention that in 14-16 days our OID's will wrap around and
>>we need some advice.
>
>
> Are you actually using the OIDs for anything?  If not, don't worry about
> it.

We use OID's to store files in the database.  Does that qualify?


> Unless you are actually depending on OIDs to be unique in some of your
> user tables, wraparound shouldn't matter.  The only possible bad
> consequence is that you might once in a while get a collision in the
> system tables (eg, a CREATE TABLE fails because the OID generated for
> the table conflicts with an existing table).  The odds of that are small
> enough that it's probably not going to be as much of a problem as a
> dump/reload would be.  Just retry the transaction and it'll usually
> work the next time.

Retrying the transaction isn't something we want to worry about.  Our
code doesn't have that capability built into it.

I read a mailing list article from a while back and there was mentioned in
it the possibility of OID's becoming a INT8.  Does this exist in any of the
new versions?

Greg

--
Greg Spiegelberg
  Product Development Manager
  Cranel, Incorporated.
  Phone: 614.318.4314
  Fax:   614.431.8388
  Email: gspiegelberg@cranel.com
Technology. Integrity. Focus.

Re: OID assistance

From
Tom Lane
Date:
Greg Spiegelberg <gspiegelberg@cranel.com> writes:
> I read a mailing list article from a while back and there was mentioned in
> it the possibility of OID's becoming a INT8.  Does this exist in any of the
> new versions?

Don't hold your breath.

Exactly how are you "using OIDs to store files"?  Do you mean you're
using large objects?

            regards, tom lane

Re: OID assistance

From
Greg Spiegelberg
Date:
Tom Lane wrote:
>
> Exactly how are you "using OIDs to store files"?  Do you mean you're
> using large objects?

The table is

                            Table "public.imgs"
   Column  |  Type  |                      Modifiers
----------+--------+------------------------------------------------------
  id       | bigint | not null default nextval('public.imgs_id_seq'::text)
  file     | text   |
  contents | oid    |
Indexes: imgs_pkey primary key btree (id)

Data is loaded using INSERT's.

insert into imgs values (1,'/path/to/myfile',lo_import('/path/to/myfile'));

Greg

--
Greg Spiegelberg
  Product Development Manager
  Cranel, Incorporated.
  Phone: 614.318.4314
  Fax:   614.431.8388
  Email: gspiegelberg@cranel.com
Technology. Integrity. Focus.

Re: OID assistance

From
Tom Lane
Date:
Greg Spiegelberg <gspiegelberg@cranel.com> writes:
> Tom Lane wrote:
>> Exactly how are you "using OIDs to store files"?  Do you mean you're
>> using large objects?

> The table is

>                             Table "public.imgs"
>    Column  |  Type  |                      Modifiers
> ----------+--------+------------------------------------------------------
>   id       | bigint | not null default nextval('public.imgs_id_seq'::text)
>   file     | text   |
>   contents | oid    |
> Indexes: imgs_pkey primary key btree (id)

> Data is loaded using INSERT's.

> insert into imgs values (1,'/path/to/myfile',lo_import('/path/to/myfile'));

Well, put a unique index on the contents column.  Better to fail an
insert than to get a conflict of LO OIDs.

If you were on 7.4 or later you could do ALTER TABLE SET WITHOUT OIDS so
you'd not be sucking up OIDs for the table rows themselves.  On 7.3 the
only way would be to drop and recreate the larger tables WITHOUT OIDS,
which is probably going to be painful :-(.

            regards, tom lane

Re: OID assistance

From
Greg Spiegelberg
Date:
Tom Lane wrote:
> Well, put a unique index on the contents column.  Better to fail an
> insert than to get a conflict of LO OIDs.

Can't do that.  Our app won't handle it.


> If you were on 7.4 or later you could do ALTER TABLE SET WITHOUT OIDS so
> you'd not be sucking up OIDs for the table rows themselves.  On 7.3 the
> only way would be to drop and recreate the larger tables WITHOUT OIDS,
> which is probably going to be painful :-(.

I figured as much.  We're working to automate the dump-restore-alter table
process now.

This does lead me to 2 questions... first, why is this still an issue and
not fixed in the backend where OID's are managed?  Second, shouldn't there
be something like "use WITHOUT OIDS for data warehouses" be in the FAQ?

Thanks for the quick response.

Greg

--
Greg Spiegelberg
  Product Development Manager
  Cranel, Incorporated.
  Phone: 614.318.4314
  Fax:   614.431.8388
  Email: gspiegelberg@cranel.com
Technology. Integrity. Focus.

Re: OID assistance

From
Tom Lane
Date:
Greg Spiegelberg <gspiegelberg@cranel.com> writes:
> Tom Lane wrote:
>> Well, put a unique index on the contents column.  Better to fail an
>> insert than to get a conflict of LO OIDs.

> Can't do that.  Our app won't handle it.

Actually, I think the lo_import() will fail anyway, whether you like it
or not.  There's a unique index on pg_largeobject.

> This does lead me to 2 questions... first, why is this still an issue and
> not fixed in the backend where OID's are managed?

When you're two major versions behind, you don't have a lot of leeway to
complain about why things are still an issue ;-).  But the short answer
is that making OIDs 8 bytes would permanently break platforms that don't
have int64 support, and be a nontrivial performance hit on those where
int64 is substantially slower than int32.  So I'd say it's a good ways
into the future yet.  Eventually we'll decide we don't care about 32-bit
machines anymore, but not for awhile.

            regards, tom lane