Skip site navigation (1) Skip section navigation (2)

Problem with declarative partitioning and COPY FROM

From: Ragnar Ouchterlony <ragnar(dot)ouchterlony(at)agama(dot)tv>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Problem with declarative partitioning and COPY FROM
Date: 2017-01-11 09:11:59
Message-ID: 16d73804-c9cd-14c5-463e-5caad563ff77@agama.tv (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-bugs
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

Attachment: data-1000.csv
Description: text/csv (24.1 KB)

Responses

pgsql-bugs by date

Next:From: Amit LangoteDate: 2017-01-11 12:24:43
Subject: Re: Problem with declarative partitioning and COPY FROM
Previous:From: Marcel RuffDate: 2017-01-11 08:33:25
Subject: Re: BUG #14490: psql and createdb crash with SIGSEGV

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group