Обсуждение: 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.