Max number of rows in a table

ow <oneway_111@yahoo.com> writes:

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
Actually you can only have 4 billion SQL commands per xid, because the
CommandId datatype is also just 32 bits.  I've never heard of anyone
running into that limit, though.

Wouldn't the above put a limit on a number of records one could have
in table?

No.

regards, tom lane

--- Tom Lane <tgl@sss.pgh.pa.us> wrote: 

Wouldn't the above put a limit on a number of records one could have
in table?

No.

If I understand correctly, a table that has 4B+ rows cannot be restored after
the dump and that, in turn, may/will affect the ability to upgrade to new
versions of pgSql.

This is somewhat similar to saying "yes" to the question "Can I drive this car
at 55 mph?" and then forgetting to mention that the brakes will fail if the car
reaches 55 mph.

Thanks

__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

On Mon, 1 Dec 2003, ow wrote:

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
Actually you can only have 4 billion SQL commands per xid, because the
CommandId datatype is also just 32 bits.  I've never heard of anyone
running into that limit, though.

Wouldn't the above put a limit on a number of records one could have in table?
One have to use pg_dump/pg_restore to be able to upgrade between pgSql releases
and/or to backup data. If one cannot backup data and/or upgrade between pg
releases, then 4B records is the effective limit on the number of records in
pgSql ... or am I missing something?

I'd expect copy to be a single command, no matter how many rows were
copied.

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:

I'd expect copy to be a single command, no matter how many rows were
copied.

It might prevent you from using pg_dump --inserts ?

--
greg

ow <oneway_111@yahoo.com> writes:

If I understand correctly, a table that has 4B+ rows cannot be restored after
the dump and that, in turn, may/will affect the ability to upgrade to new
versions of pgSql.

You don't understand correctly.

regards, tom lane

Clinging to sanity, oneway_111@yahoo.com (ow) mumbled into her beard:

--- Tom Lane <tgl@sss.pgh.pa.us> wrote: 

Wouldn't the above put a limit on a number of records one could have
in table?

No.

If I understand correctly, a table that has 4B+ rows cannot be restored after
the dump and that, in turn, may/will affect the ability to upgrade to new
versions of pgSql.

This is somewhat similar to saying "yes" to the question "Can I drive this car
at 55 mph?" and then forgetting to mention that the brakes will fail if the car
reaches 55 mph.

No, you are NOT understanding correctly.

Restoring a table from pg_dump generally involves _ONE_ command.

For instance, the following is what pg_dump generates for my table, "stocks."

COPY stocks (symbol, description, exchange) FROM stdin;
AADBX AADBX NYSE
AADEX AADEX NYSE
AAIEX AAIEX NYSE
BTS.A BTS.A TSX
CTSTK TD Canadian Equity CTE
CASH CASH TSX
CTAMER TD AmeriGrowth RSP CTE
CTASIA TD AsiaGrowth RSP CTE
CTEMER TD Emerging Markets RSP CTE
CTEURO TD European Growth RSP CTE
CTIBND TD Global RSP Bond CTE
FDIVX FDIVX NYSE
FDRXX FDRXX NYSE
FUSEX FUSEX NYSE
MOT MOT NYSE
NCX NOVA Chemicals Corporation TSX
NT NT NYSE
PCA Petro Canada TSX
RY Royal Bank of Canada TSX
TOC Thomson Corporation TSX
TRP TransCanada PipeLines Limited TSX
WORKVE Working Ventures OTHER
CTSPEC TD SPECIAL EQUITY CTE
CTUSEQ TD US EQUITY CTE
CTMM TD MONEY MARKET PL CTE
CTCBOND TD Canadian Bond CTE
\.

Recovery from this involves the SQL processor using ONE transaction
ID, and ONE SQL statement. If there were 8 billion rows in the table,
whatever other challenges there might be, it would still use ONE
transaction ID and ONE SQL statement.

What is there about "This involves just one SQL statement" that isn't
making sense?
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','acm.org').
http://www.ntlug.org/~cbbrowne/lisp.html
Pagers are cases for holding dead batteries. -Richard Wolff

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

ow <oneway_111@yahoo.com> writes:

If I understand correctly, a table that has 4B+ rows cannot be restored

after

the dump and that, in turn, may/will affect the ability to upgrade to new
versions of pgSql.

You don't understand correctly.

I see. Thanks

__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

Greg Stark <gsstark@mit.edu> writes:

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:

I'd expect copy to be a single command, no matter how many rows were
copied.

It might prevent you from using pg_dump --inserts ?

Not even that, unless you *also* modified the dump output to wrap
BEGIN/END around it. Otherwise each INSERT is a separate xid.

(Of course you could definitely take a pretty long coffee break while
waiting for a 4-billion-row table to be restored with INSERTs. Also
I think it would be necessary to run VACUUM partway through to avoid
transaction wraparound issues. pg_autovacuum could be expected to
take care of that for you, if it were running. But in practice anyone
sane would use COPY for this, anyway.)

regards, tom lane