Re: Bulk Inserts

> Yes, I did not consider that to be a problem because I did not think it
> would be used on indexed tables. I figured that the gain from doing bulk
> inserts into the table would be so diluted by the still-bottle-necked
> index maintenance that it was OK not to use this optimization for
> indexed tables.

I've tested with indexes, and the index update time is much larger than
the inserts time. Bulk inserts still provide a little bonus though, and
having a solution that works in all cases is better IMHO.

> My original thought was based on the idea of still using heap_insert, but
> with a modified form of bistate which would hold the exclusive lock and
> not
> just a pin. If heap_insert is being driven by the unmodified COPY code,
> then it can't guarantee that COPY won't stall on a pipe read or
> something,
> and so probably shouldn't hold an exclusive lock while filling the block.

Exactly, that's what I was thinking too, and reached the same conclusion.

> That is why I decided a local buffer would be better, as the exclusive
> lock
> is really a no-op and wouldn't block anyone. But if you are creating a
> new
> heap_bulk_insert and modifying the COPY to go with it, then you can
> guarantee it won't stall from the driving end, instead.

I think it's better, but you have to buffer tuples : at least a full
page's worth, or better, several pages' worth of tuples, in case inline
compression kicks in and shrinks them, since the purpose is to be able to
fill a complete page in one go.

> Whether any of these approaches will be maintainable enough to be
> integrated into the code base is another matter. It seems like there is
> already a lot of discussion going on around various permutations of copy
> options.

It's not really a COPY mod, since it would also be good for big INSERT
INTO SELECT FROM which is wal-bound too (even more so than COPY, since
there is no parsing to do).