Horton
Menu

Thread

  1. maximum number of rows in table - what about oid limits?

    John Scott <jmscott@yahoo.com> — 2001-06-06T17:07:35Z

    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?

thanks - jmscottJUSTREMOVEME@yahoo.com

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

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-06-06T17:55:43Z

    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).

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

    John Scott <jmscott@yahoo.com> — 2001-06-06T18:45:08Z

    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/

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

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-06-06T18:50:15Z

    On Wed, 6 Jun 2001, John Scott wrote:

> 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?

They are used for references between system tables.  That's why
you'll get intermittent failures on things like create statements after
rollover (system tables may have unique index on oid).
As far as I know the system doesn't do stuff with the oid on user rows.

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

    John Scott <jmscott@yahoo.com> — 2001-06-06T21:52:34Z

    --- Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
> On Wed, 6 Jun 2001, John Scott wrote:
> 
> > 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?
> 
> They are used for references between system tables.  That's why
> you'll get intermittent failures on things like create statements after
> rollover (system tables may have unique index on oid).
> As far as I know the system doesn't do stuff with the oid on user rows.
> 
> 

hmmm.  guess i'm still not getting a clear picture on what
happens when oid's wrap? just seems like an obvious question.
if i wrap twice, things could get very wicked, indeed.

if i'm not comfortable with oid wrapping, then doesn't this imply
that postgres can only handle 2^32 tuples (or 2^31 - 1) -
this number of rows per table is relatively small by today's PC standards.
just  converting/bulk loading an existing large commercial database into 
postgres would expose this issue ;-)

what am i missing here?  i am comfortable not using oids
in application logic. that' pretty standard.

my question is, since oid's are used by the
postgres kernel, what effect does oid wrapping have upon
the stability of the database.  for example, does oid wrapping mean
i might clash with oid's in the system tables.

i'll poke around the source.  just hoped somebody could
shed some light on what seemed like an obvious question.

thanks-john 


=====
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/

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

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-06-06T21:58:09Z

    > my question is, since oid's are used by the
> postgres kernel, what effect does oid wrapping have upon
> the stability of the database.  for example, does oid wrapping mean
> i might clash with oid's in the system tables.

Let me try again.  I've been having problems trying to write it out
I fear.

AFAIK, the only real clashes you have to worry about are oid clashes
between rows within the same system table that have unique constraints
on oid.  That means that your table rows shouldn't conflict, and you
shouldn't even get conflicts between different system tables.  So,
you could get a conflict when say creating a table between an old
pg_class row and the one that was going to be created and the 
create table would fail and need to be tried again, but if you
had a pg_class row oid 10000, and 100 other rows with oid 10000
in your own tables there's no conflict.

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-06-06T22:37:00Z

    John Scott <jmscott@yahoo.com> writes:
> my question is, since oid's are used by the
> postgres kernel, what effect does oid wrapping have upon
> the stability of the database.

It doesn't affect stability, if by that you mean potential crashes.

What can happen after a wrap is that the OID generated for a
newly-created object might conflict with some already-existing object's
OID.  If that happens, you get a duplicate-key-insertion error on the
OID index of the relevant system catalog (pg_class, pg_type, etc).
There is a unique index on OID for each system catalog wherein OID
is used to identify objects.  It doesn't really matter whether the
same OID is reused in different catalogs or in user tables.

The odds of this happening seem pretty low, if you've got reasonable
numbers of objects (eg, with a couple thousand tables in a database,
you'd have a 2000/4G = 1 in 2 million chance of collision on pg_class
OID).  If it does happen, you can just retry the failed object-creation
command until you get a nonconflicting OID assignment.

This is certainly not ideal, but it's not nearly as big a problem as
transaction ID wraparound.  You can live with it, whereas right now
xact ID wraparound is catastrophic.  That we gotta work on, soon.

			regards, tom lane

  8. Re: maximum number of rows in table - what about oid limits?

    Josh Berkus <josh@agliodbs.com> — 2001-06-06T23:09:09Z

    Tom, Stephan,

> What can happen after a wrap is that the OID generated for a
> newly-created object might conflict with some already-existing
> object's
> OID.  If that happens, you get a duplicate-key-insertion error on the
> OID index of the relevant system catalog (pg_class, pg_type, etc).
> There is a unique index on OID for each system catalog wherein OID
> is used to identify objects.  It doesn't really matter whether the
> same OID is reused in different catalogs or in user tables.

Given this, why bother with system-generated OIDs on user rows at all?
Why not simply reserve the OIDs for the system tables?

Or are you planning to later increase the OIDs to INT8 and start using
them for OODB-analogous behavior on individual records?

> This is certainly not ideal, but it's not nearly as big a problem as
> transaction ID wraparound.  You can live with it, whereas right now
> xact ID wraparound is catastrophic.  That we gotta work on, soon.

Nothing like reassuring us commercial DB users, Tom.  :-P

Can you describe what you're talking about?

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

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

    John Scott <jmscott@yahoo.com> — 2001-06-07T01:08:14Z

    --- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> John Scott <jmscott@yahoo.com> writes:
> > my question is, since oid's are used by the
> > postgres kernel, what effect does oid wrapping have upon
> > the stability of the database.
> 
> It doesn't affect stability, if by that you mean potential crashes.

yes and know.  an application workaround is ok,

> 
> What can happen after a wrap is that the OID generated for a
> newly-created object might conflict with some already-existing object's
> OID.  If that happens, you get a duplicate-key-insertion error on the
> OID index of the relevant system catalog (pg_class, pg_type, etc).
> There is a unique index on OID for each system catalog wherein OID
> is used to identify objects.  It doesn't really matter whether the
> same OID is reused in different catalogs or in user tables.

depends on how your are changing the catalog.
but does retrying fix the problem?  wouldn't the second try just often
see the same oid again.  does a failed attempt increment the oid????

> 
> The odds of this happening seem pretty low, if you've got reasonable
> numbers of objects (eg, with a couple thousand tables in a database,
> you'd have a 2000/4G = 1 in 2 million chance of collision on pg_class
> OID).  If it does happen, you can just retry the failed object-creation
> command until you get a nonconflicting OID assignment.


same argument as above.

all this sounds like a reasonable halfway solution.
i'm comfortable NOT using OID's at the application level for many reasons.
that's not a problem.  i just don't want my imported, test database of 20B
tuples crashing for mysterious reasons, obviously, right out of the gate,
with me mumbling and
waving my hands at the wall.  i can do that now with
commercial systems.


> 
> This is certainly not ideal, but it's not nearly as big a problem as
> transaction ID wraparound.  You can live with it, whereas right now
> xact ID wraparound is catastrophic.  That we gotta work on, soon.

yep,  1000tx/sec ~~ 50 days before wrap.  

by the way, have you written anything up on the txid wrapping problem?
we talked about this at osdn last year in san jose.  i proposed
64 bits, and you discussed a clever wrapping algorithm involving 48 bits and,
if memory serves me, backward compatiablility of the disk format.
i was the one driving the car.

probably time to take this thread private?

cheers-john


> 
> 			regards, tom lane



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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-06-07T02:05:52Z

    John Scott <jmscott@yahoo.com> writes:
> but does retrying fix the problem?  wouldn't the second try just often
> see the same oid again.  does a failed attempt increment the oid????

Yes.

> by the way, have you written anything up on the txid wrapping problem?
> we talked about this at osdn last year in san jose.  i proposed
> 64 bits, and you discussed a clever wrapping algorithm involving 48 bits and,
> if memory serves me, backward compatiablility of the disk format.

The wraparound idea was discussed on pghackers; people didn't like it
much, but I haven't heard a better near-term solution ...

			regards, tom lane

  11. Re: maximum number of rows in table - what about oid limits?

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-06-07T02:13:11Z

    "Josh Berkus" <josh@agliodbs.com> writes:
> Given this, why bother with system-generated OIDs on user rows at all?
> Why not simply reserve the OIDs for the system tables?

An option to not generate OIDs unless requested (on a table-by-table
basis) has been discussed.  It seems like a fine near-term solution
to me.  8-byte OIDs are a longer-term solution, because they'll break
a lot of things (including clients...)

>> This is certainly not ideal, but it's not nearly as big a problem as
>> transaction ID wraparound.  You can live with it, whereas right now
>> xact ID wraparound is catastrophic.  That we gotta work on, soon.

> Nothing like reassuring us commercial DB users, Tom.  :-P
> Can you describe what you're talking about?

It's in the archives: after 4G transactions, your database curls up
and dies.  When your pg_log starts to approach 1Gbyte (2 bits per
transaction) you'd better plan on dump/initdb/reload.

			regards, tom lane

  12. Help me speed things up...

    Paul Tomblin <ptomblin@xcski.com> — 2001-06-07T02:26:01Z

    I'm doing some postgres stuff in perl.  I haven't done much database stuff
in many years, and back then I was doing Oracle in C.  I'm doing a major
query that gets a bunch of fields including a key, and then does other
queries with that key on other tables.  Now with Oracle I would have set
up and compiled a query for the subqueries, and then only substituted in
the key variable and do the query, and repeat ad-nauseum.  (Forgive the
fact that I've forgotten the terminology)  There doesn't seem to be any
way to do that in Pg.pm, unless I'm missing something, and my subqueries
are making the whole thing slow as molasses.  I think the query I just did
took about 45 minutes when I did the subqueries, and about 30 seconds
without.

-- 
Paul Tomblin <ptomblin@xcski.com>, not speaking for anybody
"But I heard him exclaim, as he climbed in the night
Merry Christmas to all, I have traffic in sight" - Phyllis Moses

  13. how to configure --with-tcl in CYGWIN

    guard <guard@ficnet.net> — 2001-06-07T10:50:11Z

    please help me

  14. Re: maximum number of rows in table - what about oid limits?

    Jonathan Bartlett <johnnyb6@sdf.lonestar.org> — 2001-06-07T13:34:24Z

    Besides compatibility, what breaks when you make OIDs/Txn IDs
INT8s?  Maybe there should be a minor fork called Postgres64 which does
this for those needing large tables.  

Jon

johnnyb6@sdf.lonestar.org
SDF Public Access UNIX System - http://sdf.lonestar.org

On Wed, 6 Jun 2001, Tom Lane wrote:

> "Josh Berkus" <josh@agliodbs.com> writes:
> > Given this, why bother with system-generated OIDs on user rows at all?
> > Why not simply reserve the OIDs for the system tables?
> 
> An option to not generate OIDs unless requested (on a table-by-table
> basis) has been discussed.  It seems like a fine near-term solution
> to me.  8-byte OIDs are a longer-term solution, because they'll break
> a lot of things (including clients...)
> 
> >> This is certainly not ideal, but it's not nearly as big a problem as
> >> transaction ID wraparound.  You can live with it, whereas right now
> >> xact ID wraparound is catastrophic.  That we gotta work on, soon.
> 
> > Nothing like reassuring us commercial DB users, Tom.  :-P
> > Can you describe what you're talking about?
> 
> It's in the archives: after 4G transactions, your database curls up
> and dies.  When your pg_log starts to approach 1Gbyte (2 bits per
> transaction) you'd better plan on dump/initdb/reload.
> 
> 			regards, tom lane
> 
> ---------------------------(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
>

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

    Tim Barnard <tbarnard@povn.com> — 2001-06-07T15:08:09Z

    <snip>
If it does happen, you can just retry the failed object-creation
command until you get a nonconflicting OID assignment.
<snip>

Tom, or anyone who'd know for that matter, what is the
exact error I would need to look for?

Tim

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: <john@august.com>
Cc: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>;
<pgsql-general@postgresql.org>; <pgsql-sql@postgresql.org>
Sent: Wednesday, June 06, 2001 3:37 PM
Subject: [GENERAL] Re: [SQL] maximum number of rows in table - what about
oid limits?


> John Scott <jmscott@yahoo.com> writes:
> > my question is, since oid's are used by the
> > postgres kernel, what effect does oid wrapping have upon
> > the stability of the database.
>
> It doesn't affect stability, if by that you mean potential crashes.
>
> What can happen after a wrap is that the OID generated for a
> newly-created object might conflict with some already-existing object's
> OID.  If that happens, you get a duplicate-key-insertion error on the
> OID index of the relevant system catalog (pg_class, pg_type, etc).
> There is a unique index on OID for each system catalog wherein OID
> is used to identify objects.  It doesn't really matter whether the
> same OID is reused in different catalogs or in user tables.
>
> The odds of this happening seem pretty low, if you've got reasonable
> numbers of objects (eg, with a couple thousand tables in a database,
> you'd have a 2000/4G = 1 in 2 million chance of collision on pg_class
> OID).  If it does happen, you can just retry the failed object-creation
> command until you get a nonconflicting OID assignment.
>
> This is certainly not ideal, but it's not nearly as big a problem as
> transaction ID wraparound.  You can live with it, whereas right now
> xact ID wraparound is catastrophic.  That we gotta work on, soon.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

  16. Re: maximum number of rows in table - what about oid limits?

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-06-08T14:38:02Z

    Jonathan Bartlett <johnnyb6@sdf.lonestar.org> writes:
> Besides compatibility, what breaks when you make OIDs/Txn IDs
> INT8s?

OIDs are part of the on-the-wire protocol (column type data returned
during a SELECT is in the form of OIDs).  So extending OIDs to int8
would pose nontrivial client/server compatibility hazards.

> Maybe there should be a minor fork called Postgres64 which does
> this for those needing large tables.  

I've been thinking it should be a compile-time option.  But the protocol
issue is going to be a problem.

			regards, tom lane

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-06-08T14:41:22Z

    "Tim Barnard" <tbarnard@povn.com> writes:
> <snip>
> If it does happen, you can just retry the failed object-creation
> command until you get a nonconflicting OID assignment.
> <snip>

> Tom, or anyone who'd know for that matter, what is the
> exact error I would need to look for?

It'd be a complaint about attempt to insert a duplicate key into
the unique index on one of several system catalogs' OID columns.
There wouldn't be any earlier detection of the problem than that.

			regards, tom lane

  18. Re: maximum number of rows in table - what about oid limits?

    Jonathan Bartlett <johnnyb6@sdf.lonestar.org> — 2001-06-08T15:26:57Z

    > I've been thinking it should be a compile-time option.  But the protocol
> issue is going to be a problem.

The protocol issues shouldn't be a problem if you require separate
drivers.  Force clients to use libpq64.so.

The reason I'm concerned about this is that there are a _lot_ of great
things you can do with OIDs if they are unique.  For example, if you've
worked with Oracle Applications, they have this tacked on to the end of
all of their tables:

LAST_UPDATED_BY Int,
LAST_UPDATED_DATE Date,
...

Which could be replaced by a single table

Table UPDATE_HISTORY
  REFERRED_OBJECT OID,
  LAST_UPDATED_BY OID,
  LAST_UPDATED_DATE Date

which applies to all tables.  You could also have a notes table for
everything -

table NOTES
  REFERRED_OBJECT OID,
  Note Text

And so on and so forth.  However, with OID wrap-around, this would be
problematic.

Jon

> 
> 			regards, tom lane
>

  19. Re: maximum number of rows in table - what about oid limits?

    Josh Berkus <josh@agliodbs.com> — 2001-06-08T15:42:34Z

    Jon,

> Which could be replaced by a single table
> 
> Table UPDATE_HISTORY
>   REFERRED_OBJECT OID,
>   LAST_UPDATED_BY OID,
>   LAST_UPDATED_DATE Date
> 
> which applies to all tables.  You could also have a notes table for
> everything -
> 
> table NOTES
>   REFERRED_OBJECT OID,
>   Note Text

Actually, I'm doing this with one of my applications.  Rather than using
the (problematic) OID, I simply established an independant sequence
('universal_sq') and used that as the primary key for all of my
important data tables.  So long as the total records in these tables
stays < 2.4 billion, I'm doing fine.  Nothing in PostgreSQL prevents you
from using a single independent sequence as the key for multiple tables.

If you are concerned about having > 2.4 billion recs, then perhaps it's
time to hack an INT8 sequence functionality.  I think that adding INT8
sequences to the PostgreSQL database would be a *lot* easier than
modifying OID functionality.  In fact, if it matters to you, why not pay
for it to get done?

-Josh Berkus

P.S. A lot of these concerns affect only developers with high-traffic
web applications and similar.  For example, in my small business
software, it will take <> 1 million days to exhaust the TXN register.
Not something I need to worry about.  

Is there some good way that we can "vote with our pocketbooks" for
various development issues in the PostgreSQL to-do list, short of hiring
a C programmer ourselves?  I, for one, am desperately eager for real
stored procedures, and could get my clients to contribute toward the
development, but not more than 4 figures ...







______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

  20. Re: maximum number of rows in table - what about oid limits?

    Jonathan Bartlett <johnnyb6@sdf.lonestar.org> — 2001-06-08T17:48:06Z

    > Actually, I'm doing this with one of my applications.  Rather than using
> the (problematic) OID, I simply established an independant sequence
> ('universal_sq') and used that as the primary key for all of my
> important data tables.  So long as the total records in these tables
> stays < 2.4 billion, I'm doing fine.  Nothing in PostgreSQL prevents you
> from using a single independent sequence as the key for multiple tables.

The nice thing about OIDs is that if you ever need to merge rows, you
could make a function that searched all OID-type parameters and change the
old reference to the new one.

> If you are concerned about having > 2.4 billion recs, then perhaps it's
> time to hack an INT8 sequence functionality.  I think that adding INT8
> sequences to the PostgreSQL database would be a *lot* easier than
> modifying OID functionality.  In fact, if it matters to you, why not pay
> for it to get done?

Were I doing PostgreSQL for a business, I most assuredly would.  However,
I am currently just toying around with creating a common set of base
tables that would be useful in almost any application, taking advantage of
PostgreSQL's specific capabilities.  However, I asked the question because
if the only real problem is compatibility, I might be able to do it
myself (or maybe not).

> P.S. A lot of these concerns affect only developers with high-traffic
> web applications and similar.  For example, in my small business
> software, it will take <> 1 million days to exhaust the TXN register.
> Not something I need to worry about.  

Agreed, but if people are going to take PostgreSQL seriously, then some
of the top limits need to be expanded. 

> Is there some good way that we can "vote with our pocketbooks" for
> various development issues in the PostgreSQL to-do list, short of hiring
> a C programmer ourselves?  I, for one, am desperately eager for real
> stored procedures, and could get my clients to contribute toward the
> development, but not more than 4 figures ...

You could probably find a college student to do whatever you wanted for a
grand.

Out of curiosity, what do you mean by "Real Stored Procedures"?

Jon

  21. Re: maximum number of rows in table - what about oid limits?

    Josh Berkus <josh@agliodbs.com> — 2001-06-08T18:15:26Z

    Jon,

> The nice thing about OIDs is that if you ever need to merge rows, you
> could make a function that searched all OID-type parameters and
> change the
> old reference to the new one.

The same thing can be done with the 'universal_sq' approach.

> Were I doing PostgreSQL for a business, I most assuredly would.
> However,
> I am currently just toying around with creating a common set of base
> tables that would be useful in almost any application, taking
> advantage of
> PostgreSQL's specific capabilities.  However, I asked the question
> because
> if the only real problem is compatibility, I might be able to do it
> myself (or maybe not).

Great!  Go for it.  Personally, I lack the knowledge ...

> Agreed, but if people are going to take PostgreSQL seriously, then
> some
> of the top limits need to be expanded. 

Expanding the top limits is obviously a Good Thing (tm).  However, I
point out that expanding the top limits may not be the highest priority
for PostgreSQL development (of course, it may be).  Row limits are
mainly important to web apps.

For the kind of app I do (accounting and reporting) things like stored
procedures, nested transaction handling, full support for ALTER TABLE,
etc., are a *lot* more important than OID/TXN limits.  Plus some more
administrative interface projects, and an updated version of PGAccess.

It's my opinion that PostgreSQL has a lot more to gain in terms of
market acceptance as an application database server than a web database
server.  In the web arena, PostgreSQL must compete against MySQL and
Oracle which are quite good; in medium-sized database applications, the
only competition is MS SQL Server.  PostgreSQL is *already* better than
MSSQL, but it's going to have to be *much* better before people will
notice it. IMHO.

> You could probably find a college student to do whatever you wanted
> for a
> grand.

Hey, if you know anyone, please have them e-mail me.

> Out of curiosity, what do you mean by "Real Stored Procedures"?

Since you're undoubtedly familiar with PostgreSQL functions, I will list
only how a stored procedure differes from a PgSQL function:

1. Is precompiled, with a saved plan of execution on the server.
	(not sure about the wisdom of this but it appears to be in the SQL 99
standard)
2. Can return a rowset or multiple rowsets
3. Accepts an indefinite number of parameters
4. Returns an execution state and none to many return values
5. Supports full server control internally, depending on language; thus
SQL and PL/pgSQL procedures should support cursors, locak handling,
transactions and database control language.

I'm not sure that all of the above is in the standard; the text on
Stored Procedures in SQL 99 seems to be somewhat skimpy, the committee
perhaps leaving it up to te vendors to implement (pfaugh!) but all of
the above is consistent with Stored Procedure implementation in MS SQL
Server 7 and Oracle 8.

-Josh Berkus





______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

  22. Re: maximum number of rows in table - what about oid limits?

    Jonathan Bartlett <johnnyb6@sdf.lonestar.org> — 2001-06-08T21:42:09Z

    
> Jon,
> 
> > The nice thing about OIDs is that if you ever need to merge rows, you
> > could make a function that searched all OID-type parameters and
> > change the
> > old reference to the new one.
> 
> The same thing can be done with the 'universal_sq' approach.

How exactly?  The column types would just be INT8, right?

> Since you're undoubtedly familiar with PostgreSQL functions, I will list
> only how a stored procedure differes from a PgSQL function:
> 
> 1. Is precompiled, with a saved plan of execution on the server.
> 	(not sure about the wisdom of this but it appears to be in the SQL 99
> standard)
> 2. Can return a rowset or multiple rowsets
> 3. Accepts an indefinite number of parameters
> 4. Returns an execution state and none to many return values
> 5. Supports full server control internally, depending on language; thus
> SQL and PL/pgSQL procedures should support cursors, locak handling,
> transactions and database control language.
> 

Aha!  The only thing I don't get is what do you mean by "execution state"?
(I'm guessing that you're not referring to Texas).

Jon

  23. Re: maximum number of rows in table - what about oid limits?

    Josh Berkus <josh@agliodbs.com> — 2001-06-08T22:03:15Z

    Jon,

> How exactly?  The column types would just be INT8, right?

Ah.  I see what you mean.  In my application, the columns are all
*named* the same ... clients.usq, orders.usq, etc.  Thus a search on
"usq" through out the application will locate everything.

If you needed and INT8 variable, you could define a custom type on INT8
... and then search on that type.

> Aha!  The only thing I don't get is what do you mean by "execution
> state"?
> (I'm guessing that you're not referring to Texas).

<Snicker>
A variable indicating success or failure.  e.g. In MSSQL, you get a
ReturnValue of INT2 type ... with 0 indicating success, and <> 0 as an
error number.

Speaking of which, one thing I forgot to explicitly mention above is
error trapping of some kind would be extremely useful ...

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

  24. Re: maximum number of rows in table - what about oid limits?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-06-11T04:37:22Z

    > "Josh Berkus" <josh@agliodbs.com> writes:
> > Given this, why bother with system-generated OIDs on user rows at all?
> > Why not simply reserve the OIDs for the system tables?
> 
> An option to not generate OIDs unless requested (on a table-by-table
> basis) has been discussed.  It seems like a fine near-term solution
> to me.  8-byte OIDs are a longer-term solution, because they'll break
> a lot of things (including clients...)
> 
> >> This is certainly not ideal, but it's not nearly as big a problem as
> >> transaction ID wraparound.  You can live with it, whereas right now
> >> xact ID wraparound is catastrophic.  That we gotta work on, soon.
> 
> > Nothing like reassuring us commercial DB users, Tom.  :-P
> > Can you describe what you're talking about?
> 
> It's in the archives: after 4G transactions, your database curls up
> and dies.  When your pg_log starts to approach 1Gbyte (2 bits per
> transaction) you'd better plan on dump/initdb/reload.

I wonder is we should check the size of pg_log on startup and warn the
administrator?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  25. Re: maximum number of rows in table - what about oid limits?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-06-11T04:42:03Z

    > Besides compatibility, what breaks when you make OIDs/Txn IDs
> INT8s?  Maybe there should be a minor fork called Postgres64 which does
> this for those needing large tables.  

We have delayed the problem of oid/xid wraparound by putting out good
releases so people upgrade every year or so.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  26. Re: Help me speed things up...

    Doug McNaught <doug@wireboard.com> — 2001-06-12T19:08:38Z

    Paul Tomblin <ptomblin@xcski.com> writes:

> I'm doing some postgres stuff in perl.  I haven't done much database stuff
> in many years, and back then I was doing Oracle in C.  I'm doing a major
> query that gets a bunch of fields including a key, and then does other
> queries with that key on other tables.  Now with Oracle I would have set
> up and compiled a query for the subqueries, and then only substituted in
> the key variable and do the query, and repeat ad-nauseum.  (Forgive the
> fact that I've forgotten the terminology)  There doesn't seem to be any
> way to do that in Pg.pm, unless I'm missing something, and my subqueries
> are making the whole thing slow as molasses.  I think the query I just did
> took about 45 minutes when I did the subqueries, and about 30 seconds
> without.

It's hard to say what might be going on, but I doubt that the
query-compile overhead is hitting you that hard.  Care to post a
schema and EXPLAIN output for the queries you're doing??

-Doug (a former Monastery inmate..)
-- 
The rain man gave me two cures; he said jump right in,
The first was Texas medicine--the second was just railroad gin,
And like a fool I mixed them, and it strangled up my mind,
Now people just get uglier, and I got no sense of time...          --Dylan

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

    Daniel M . Kurry <grasshacker@over-yonder.net> — 2001-06-12T19:35:13Z

    (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

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

    Tim Barnard <tbarnard@povn.com> — 2001-06-12T20:10:41Z

    <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
>