Re: Problem with declarative partitioning and COPY FROM

On Wed, Jan 11, 2017 at 6:11 PM, Ragnar Ouchterlony
<ragnar(dot)ouchterlony(at)agama(dot)tv> wrote:
> Hi,
>
> I have started to test and evaluate the new declarative partitioning for
> postgresql 10. I encountered a problem in relation to COPY FROM and
> declarative partitioning.
>
> I used a snapshot of postgresql from today to test this
> (https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2).
>
> My test case:
>
> First the sql for the tables:
>
> BEGIN;
> CREATE TABLE test_copy_bug_table
> (
> time bigint,
> value bigint
> )
> PARTITION BY RANGE (time);
>
> CREATE TABLE test_copy_bug_table_0
> PARTITION OF test_copy_bug_table
> (
> PRIMARY KEY (time)
> )
> FOR VALUES FROM (0) TO (500);
>
> CREATE TABLE test_copy_bug_table_500
> PARTITION OF test_copy_bug_table
> (
> PRIMARY KEY (time)
> )
> FOR VALUES FROM (500) TO (1000);
> COMMIT;
>
> Then I inserted this using COPY FROM using a psql command line and the
> attached data:
>
> COPY test_copy_bug_table (time, value) FROM 'data-1000.csv'
> WITH (FORMAT 'csv', HEADER, NULL '', DELIMITER ',', QUOTE '"')
>
> Then I checked the data to see what is in the respective partition:
>
> db=# select count(time), min(time), max(time) from test_copy_bug_table_0;
> count | min | max
> -------+-----+-----
> 555 | 0 | 554
> (1 row)
>
> db=# select count(time), min(time), max(time) from
> test_copy_bug_table_500;
> count | min | max
> -------+-----+-----
> 445 | 555 | 999
> (1 row)
>
> This does not look right. The _0 partition should only contain times
> 0-499 and the _500 partition should contain 500-999.
>
> I now also have odd behavior in terms of index scanning:
>
> db=# select * from test_copy_bug_table where time=543;
> time | value
> ------+-------
> (0 rows)
>
> But I see that row if I do a "select * from test_copy_bug_table".
>
> In fact, when I had a larger test case using 10 million rows and 10
> partitions, I got errors instead:
>
> db=# select * from test_copy_bug_table time = 1000109;
> ERROR: could not read block 5405 in file "base/16384/17762": read only 0
> of 8192 bytes

There's a bug and has been reported on the -hackers mailing list as
well. Please try your example after applying the patch 0002 attached
with the following email:
https://www.postgresql.org/message-id/01bc4745-bac8-a033-96a1-8a42b45d2fc1%40lab.ntt.co.jp

Thanks,
Amit