Re: [BUGS] Problem with declarative partitioning and COPY FROM

On Wed, Jan 11, 2017 at 6:11 PM, Ragnar Ouchterlony
<ragnar.ouchterlony@agama.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


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs