Re: COPY FROM WHEN condition

On Mon, 1 Apr 2019 at 08:05, Andres Freund <andres(at)anarazel(dot)de> wrote:
> I'll work on pushing all the other pending tableam patches today -
> leaving COPY the last non pluggable part. You'd written in a private
> email that you might try to work on this on Monday, so I think I'll give
> this a shot on Tuesday if you've not gotten around till then? I'd like
> to push this sooner than the exact end of the freeze...

I worked on this, but I've not got the patch finished yet.

Things not done:

1. Fails regression tests. Possibly due to a bug in tuple conversion.
2. Thought about ERROR line numbers in copy stream. It's possible we
may need an array of uint64s to store the line number per slot.
3. Testing.
4. Code comments.
5. Code sanity check.
6. Thought about if it's a problem if we ERROR during the copy after
having already inserted tuples that come after the tuple causing the
error. (It's possible that the errors would become out of order.)

However, the performance looks pretty good.

$ cat bench.pl
for (my $i=0; $i < 8912891; $i++) {
print "1\n1\n2\n2\n";
}
$ cat bench_same.pl
for (my $i=0; $i < 8912891; $i++) {
print "1\n1\n1\n1\n";
}

create table listp(a int) partition by list(a);
create table listp1 partition of listp for values in(1);
create table listp2 partition of listp for values in(2);

-- Test 1: Change partition every 2nd tuple.

master + v23-0001-tableam-multi_insert-and-slotify-COPY.patch

# copy listp from program $$perl ~/bench.pl$$ delimiter '|';
Time: 17894.625 ms (00:17.895)

master + attached

# copy listp from program $$perl ~/bench.pl$$ delimiter '|';
Time: 10615.761 ms (00:10.616)

-- Test 2: Same partition each time.

master + v23-0001-tableam-multi_insert-and-slotify-COPY.patch
# copy listp from program $$perl ~/bench_same.pl$$ delimiter '|';
Time: 19234.960 ms (00:19.235)

master + attached

# copy listp from program $$perl ~/bench_same.pl$$ delimiter '|';
Time: 9064.802 ms (00:09.065)

Of course, it is possible that some of the bugs account for some of
the improved time... but the rows did seem to be in the table
afterwards.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services