Thread: Re: [SQL] maximum number of rows in table - what about oid limits?

On 6 Jun 2001, jmscott@REMOVEMEyahoo.com wrote:

> postgresql docs claim an essentially unlimited number of
> rows per table.
>
>    http://postgresql.crimelabs.net/users-lounge/limitations.html
>
> this doesn't make sense if each row has an oid.
> do more subtle side effects exist if the oid wraps?

In general, unless you're relying on unique oids, you should be fine.
You probably don't want to use oid as a unique key in your tables for that
reason. Of course, sequences aren't sufficient either (also being
int4) but some kind of int8 "sequence" mechanism would do it if you expect
more than the int4 number of rows.

You might have problems with creating system table entries with unique
oids after wraparound, but generally that can be fixed by trying again.
(Some of the system tables have a unique index on oid).

well i wasn't interested in using oids in my application.
i was curious about the relationship oids
and the tuple/row limit.

i guess if what you say is true, the oids are NOT used internally
by postgres.  this seems odd.

so, i guess my question still stands ... what happens when oids wrap?
are oids nothing more than a sequence with an index,
not used at all internally?

i
--- Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
>
> On 6 Jun 2001, jmscott@REMOVEMEyahoo.com wrote:
>
> > postgresql docs claim an essentially unlimited number of
> > rows per table.
> >
> >    http://postgresql.crimelabs.net/users-lounge/limitations.html
> >
> > this doesn't make sense if each row has an oid.
> > do more subtle side effects exist if the oid wraps?
>
> In general, unless you're relying on unique oids, you should be fine.
> You probably don't want to use oid as a unique key in your tables for that
> reason. Of course, sequences aren't sufficient either (also being
> int4) but some kind of int8 "sequence" mechanism would do it if you expect
> more than the int4 number of rows.
>
> You might have problems with creating system table entries with unique
> oids after wraparound, but generally that can be fixed by trying again.
> (Some of the system tables have a unique index on oid).
>


=====
John Scott (john@august.com)
Senior Partner
August Associates

email: john@august.com
  web: http://www.august.com/~jmscott

__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year!  http://personal.mail.yahoo.com/

<snip>
so, i guess my question still stands ... what happens when oids wrap?
<snip>

Answer: Very little. Just remember 2 things if you expect OIDs to wrap
              in your application:

  1. Don't key off of them. Use a sequence of your own.
  2. Watch for creations and insertions to fail due to duplicate
      OIDs. When the failure is due to a duplicate, simply retry
      the operation again.

Tim

----- Original Message -----
From: "John Scott" <jmscott@yahoo.com>
To: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
Cc: <pgsql-general@postgresql.org>; <pgsql-sql@postgresql.org>
Sent: Wednesday, June 06, 2001 11:45 AM
Subject: [GENERAL] Re: [SQL] maximum number of rows in table - what about
oid limits?


> well i wasn't interested in using oids in my application.
> i was curious about the relationship oids
> and the tuple/row limit.
>
> i guess if what you say is true, the oids are NOT used internally
> by postgres.  this seems odd.
>
> so, i guess my question still stands ... what happens when oids wrap?
> are oids nothing more than a sequence with an index,
> not used at all internally?
>
> i
> --- Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
> >
> > On 6 Jun 2001, jmscott@REMOVEMEyahoo.com wrote:
> >
> > > postgresql docs claim an essentially unlimited number of
> > > rows per table.
> > >
> > >    http://postgresql.crimelabs.net/users-lounge/limitations.html
> > >
> > > this doesn't make sense if each row has an oid.
> > > do more subtle side effects exist if the oid wraps?
> >
> > In general, unless you're relying on unique oids, you should be fine.
> > You probably don't want to use oid as a unique key in your tables for
that
> > reason. Of course, sequences aren't sufficient either (also being
> > int4) but some kind of int8 "sequence" mechanism would do it if you
expect
> > more than the int4 number of rows.
> >
> > You might have problems with creating system table entries with unique
> > oids after wraparound, but generally that can be fixed by trying again.
> > (Some of the system tables have a unique index on oid).
> >
>
>
> =====
> John Scott (john@august.com)
> Senior Partner
> August Associates
>
> email: john@august.com
>   web: http://www.august.com/~jmscott
>
> __________________________________________________
> Do You Yahoo!?
> Get personalized email addresses from Yahoo! Mail - only $35
> a year!  http://personal.mail.yahoo.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

(I can't imagine why this would be crossposted, especially to SQL.)

On Wed, Jun 06, 2001 at 11:45:08AM -0700, some SMTP stream spewed forth:
> well i wasn't interested in using oids in my application.
> i was curious about the relationship oids
> and the tuple/row limit.

The only issue would arise when you insert a duplicate id into a unique
oid column. Provided that you are not using oid's in your application,
this could only occur in certain system tables. When the system attempted
to insert a new row with an already-used oid, the insert would fail in
much the same way as if you insert a duplicate value in any other unique
column. What this would affect is dependant on what system tables are
affected. (I do not know offhand which system tables have unique oid
columns.) Basically, Nothing Bad is going to happen to you data, but you
could (would) very well have failures when adding things like tables.
(For example, there is a unique index on pg_class.)


gh

>
> i guess if what you say is true, the oids are NOT used internally
> by postgres.  this seems odd.

They are used for things like identifying tables, etc. Just have a look
at the system table and the indexes on their oid columns.


>
> so, i guess my question still stands ... what happens when oids wrap?
> are oids nothing more than a sequence with an index,
> not used at all internally?
>
> i
> --- Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
> >
> > On 6 Jun 2001, jmscott@REMOVEMEyahoo.com wrote:
> >
> > > postgresql docs claim an essentially unlimited number of
> > > rows per table.
> > >
> > >    http://postgresql.crimelabs.net/users-lounge/limitations.html
> > >
> > > this doesn't make sense if each row has an oid.
> > > do more subtle side effects exist if the oid wraps?
> >
> > In general, unless you're relying on unique oids, you should be fine.
> > You probably don't want to use oid as a unique key in your tables for that
> > reason. Of course, sequences aren't sufficient either (also being
> > int4) but some kind of int8 "sequence" mechanism would do it if you expect
> > more than the int4 number of rows.
> >
> > You might have problems with creating system table entries with unique
> > oids after wraparound, but generally that can be fixed by trying again.
> > (Some of the system tables have a unique index on oid).
> >
>
>
> =====
> John Scott (john@august.com)
> Senior Partner
> August Associates
>
> email: john@august.com
>   web: http://www.august.com/~jmscott