Re: COPY enhancements

On Thu, 2009-10-08 at 12:21 -0400, Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > On Thu, Oct 8, 2009 at 11:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> I wonder whether we could break down COPY into sub-sub
> >> transactions to work around that...
> 
> > How would that work?  Don't you still need to increment the command counter?
> 
> Actually, command counter doesn't help because incrementing the CC
> doesn't give you a rollback boundary between rows inserted before it
> and afterwards.  What I was vaguely imaging was
> 
>     -- outer transaction for whole COPY
> 
>     -- sub-transactions that are children of outer transaction
> 
>     -- sub-sub-transactions that are children of sub-transactions
> 
> You'd eat a sub-sub-transaction per row, and start a new sub-transaction
> every 2^32 rows.
> 
> However, on second thought this really doesn't get us anywhere, it just
> moves the 2^32 restriction somewhere else.  Once the outer transaction
> gets to be more than 2^31 XIDs old, the database is going to stop
> because of XID wraparound.
> 
> So really we have to find some way to only expend one XID per failure,
> not one per row.

I discovered a few days back that ~550 subtransactions is sufficient to
blow max_stack_depth. 1 subtransaction per error doesn't allow many
errors.

-- Simon Riggs           www.2ndQuadrant.com