Re: COPY enhancements

On Thu, 2009-10-08 at 12:21 -0400, Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Thu, Oct 8, 2009 at 11:50 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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