Re: Introduce new multi insert Table AM and improve performance of various SQL commands with it for Heap AM

On Tue, 2024-08-27 at 15:44 +0200, Matthias van de Meent wrote:
> > One solution: when the buffer is flushed, we can return an iterator
> > over the buffered tuples to the caller. The caller can then use the
> > iterator to insert into indexes, return a tuple to the executor,
> > etc.,
> > and then release the iterator when done (freeing the buffer).
> 
> I think that would work, but it'd need to be accomodated in the
> table_modify_buffer_insert path too, not just the _flush path, as the
> heap AM flushes the buffer when inserting tuples and its internal
> buffer is full, so not only at the end of modifications.

I gave this a little more thought and I don't think we need a change
here now. The callback could support RETURNING by copying the tuples
out into the caller's state somewhere, and then the caller can iterate
on its own and emit those tuples.

That's not ideal, because it involves an extra copy, but it's a much
simpler API.

Another thought is that there are already a number of cases where we
need to limit the use of batching similar to copyfrom.c:917-1006. For
instance, before-row triggers, instead-of-row triggers, and volatile
functions in the query. We could also just consider RETURNING another
restriction, which could be lifted later by implementing the logic in
the callback (as described above) without an API change.

Regards,
    Jeff Davis