Re: COPY enhancements

On Fri, 2009-10-09 at 00:15 +0100, Simon Riggs wrote:
> On Thu, 2009-10-08 at 12:21 -0400, Tom Lane wrote:
> > 
> > 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.

Not meaning to come up with problems, nor direct them at Tom, this is
just a convenient place to put in a few thoughts.

Another thing that has occurred to me is that RI checks are currently
resolved at end of statement and could end up rejecting any/all rows
loaded. If we break down the load into subtransaction pieces we would
really want the RI checks on the rows to be performed during the
subtransaction that makes them. The good thing about that is that it
would lend itself to holding successful checks in a hash table to allow
a fast path optimization of continual re-checks of same values.

-- Simon Riggs           www.2ndQuadrant.com