Обсуждение: [BUGS] Problem with declarative partitioning and COPY FROM
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
Вложения
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
On 2017-01-11 13:24, Amit Langote wrote: > 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 Ok, that is good! I tested the patch as is, but it did not make a difference. Then I read the patch more carefully. + /* + * FIXME: We don't engage the bulk-insert mode for partitioned tables, + * because the the heap relation is most likely change from one row to + * next due to tuple-routing. + */ + if (cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) + bistate = GetBulkInsertState(); The if-statement does not match how I read the comment. That is, shouldn't it be "!=" rather than "=="? I have tested using that change and now I get the results I expect. /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
On Wed, Jan 11, 2017 at 11:58 PM, Ragnar Ouchterlony <ragnar.ouchterlony@agama.tv> wrote: > On 2017-01-11 13:24, Amit Langote wrote: >> >> 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 > > > Ok, that is good! > > I tested the patch as is, but it did not make a difference. Then I read the > patch more carefully. > > + /* > + * FIXME: We don't engage the bulk-insert mode for partitioned > tables, > + * because the the heap relation is most likely change from one row > to > + * next due to tuple-routing. > + */ > + if (cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) > + bistate = GetBulkInsertState(); > > > The if-statement does not match how I read the comment. That is, shouldn't > it be "!=" rather than "=="? Oops, you're right. Will update the patch posted on -hackers. 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
Hi again,
I am testing how declarative partitioning behaves with lots of
partitions.
But when dropping the tables postgresql quickly used up all my memory.
So my test is setting up a table with 10000 partitions. Similar to the
following:
BEGIN;
CREATE TABLE myschema.test_table
(
time bigint,
value bigint
)
PARTITION BY RANGE (time);
CREATE TABLE myschema.test_table_0
PARTITION OF test_table
(
PRIMARY KEY (time)
)
FOR VALUES FROM (0) TO (1000);
CREATE TABLE myschema.test_table_1000
PARTITION OF test_table
(
PRIMARY KEY (time)
)
FOR VALUES FROM (1000) TO (2000);
-- [...]
CREATE TABLE myschema.test_table_9999000
PARTITION OF test_table
(
PRIMARY KEY (time)
)
FOR VALUES FROM (9999000) TO (10000000);
COMMIT;
When I later try to drop the schema ("DROP SCHEMA myschema CASCADE") and
thus that table, the postgres backend use up one CPU core and quickly
allocates a lot of memory (I only have 32GB on my test machine, so I
don't know if it finally stops allocating more memory or not).
It behaves the same if I only try to drop the parent table ("DROP TABLE
test_table CASCADE").
Dropping individual partitions work fine.
I did a "perf record" during this and I have attached what looks like
the most relevant part.
As a side note, I noticed that it is a lot slower to add partitions using
declarative method compared to the inheritance based method. Adding
the 10000 partitions using the sql from above takes about 250 seconds
compared to about 13s for the similar setup but with normal inheritance.
I am not sure how many partitions are supposed to be handled and 10000
partitions may be out of scope, but I report this because it is
unexpected that postgres use that much memory when deleting tables it
was able to add in the first place.
/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