Thread

Commits

Fix interaction of partitioned tables with BulkInsertState.
- b1ecb9b3fcfb 10.0 landed

Problem with declarative partitioning and COPY FROM

Ragnar Ouchterlony <ragnar.ouchterlony@agama.tv> — 2017-01-11T09:11:59Z

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

Re: Problem with declarative partitioning and COPY FROM

Amit Langote <amitlangote09@gmail.com> — 2017-01-11T12:24:43Z

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

Re: Problem with declarative partitioning and COPY FROM

Ragnar Ouchterlony <ragnar.ouchterlony@agama.tv> — 2017-01-11T14:58:14Z

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

Re: Problem with declarative partitioning and COPY FROM

Amit Langote <amitlangote09@gmail.com> — 2017-01-12T01:38:15Z

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

Re: Problem with declarative partitioning and COPY FROM

Ragnar Ouchterlony <ragnar.ouchterlony@agama.tv> — 2017-01-12T15:42:25Z

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