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
/Ragnar Ouchterlony, Agama Technologies
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs