Thread: pg_restore and transaction id wraparound
Hi, Hypothetical situation: a table containing, say, 10 billion rows is backed up and then restored with pg_restore. Would this lead to the transaction id wraparound issue since 10B rows are imported in one "batch"? Thanks __________________________________ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/
ow <oneway_111@yahoo.com> writes:
> Hypothetical situation: a table containing, say, 10 billion rows is backed up
> and then restored with pg_restore. Would this lead to the transaction id
> wraparound issue since 10B rows are imported in one "batch"?
No, because it'd only be one transaction.
regards, tom lane
Quoth oneway_111@yahoo.com (ow): > Hypothetical situation: a table containing, say, 10 billion rows is backed up > and then restored with pg_restore. Would this lead to the transaction id > wraparound issue since 10B rows are imported in one "batch"? No. Copying the 10 billion rows in only consumes one xid. - If you're in "autocommit mode," then each SQL statement consumes one xid. - If you type in BEGIN;, then you could have 10 billion SQL statements after that, and they would, altogether, consume 1 xid. -- "aa454","@","freenet.carleton.ca" http://www3.sympatico.ca/cbbrowne/sgml.html To iterate is human; to recurse, divine.
Christopher Browne <cbbrowne@acm.org> writes:
> - If you type in BEGIN;, then you could have 10 billion SQL
> statements after that, and they would, altogether, consume 1 xid.
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.
regards, tom lane
--- 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. > Perhaps noone yet had a table with 4B records in pgSql. Otherwise, how would they dump/restore it? Thanks __________________________________ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/
A long time ago, in a galaxy far, far away, oneway_111@yahoo.com (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. > > Perhaps noone yet had a table with 4B records in pgSql. Otherwise, > how would they dump/restore it? I may have been guilty of hyperbole, by using the number 10 billion, but not of proving this impossible. If you had a table that large, dump/restore wouldn't have any XID problems because the normal dump/restore involves copying the data out (ONE query, ONE XID), and then reading it via the COPY command (again, ONE query, ONE XID). And I think I would be quite displeased if I had a table with that many records, in any case, because dump/restore would take an enormously long time as would reindexing. -- (format nil "~S@~S" "cbbrowne" "ntlug.org") http://cbbrowne.com/info/linuxdistributions.html 16-inch Rotary Debugger: A highly effective tool for locating problems in computer software. Available for delivery in most major metropolitan areas. Anchovies contribute to poor coding style.