Re: Bulk Inserts

2009/9/14 Pierre Frédéric Caillaud <lists(at)peufeu(dot)com>

>
> Replying to myself...
>
> Jeff suggested to build pages in local memory and insert them later in the
> table. This is what's used in CLUSTER for instance, I believe.
>
> It has some drawbacks though :
>
> - To insert the tuples in indexes, the tuples need tids, but if you build
> the page in local memory, you don't know on which page they will be until
> after allocating the page, which will probably be done after the page is
> built, so it's a bit of a chicken and egg problem.
>

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.

>
> - It only works on new pages. Pages which are not empty, but have free
> space, cannot be written in this way.
>

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.
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.

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.

Cheers,

Jeff