Re: Declarative partitioning - another take

On 2016/12/14 12:14, Venkata B Nagothi wrote:
> Loading the data into a normal table is not an issue (infact the csv is
> generated from the table itself)
>
> The issue is occurring only when i am trying to load the data from CSV file
> into a partitioned table -
>
> db01=# CREATE TABLE orders_y1992
> PARTITION OF orders2 FOR VALUES FROM ('1992-01-01') TO ('1992-12-31');
> CREATE TABLE
> db01=# copy orders2 from '/data/orders-1993.csv' delimiter '|';
> ERROR: could not read block 6060 in file "base/16384/16407": read only 0
> of 8192 bytes
> CONTEXT: COPY orders2, line 376589:
> "9876391|374509|O|54847|1997-07-16|3-MEDIUM |Clerk#000001993|0|ithely
> regular pack"
>
> Not sure why COPY is failing.

I think I've been able to reproduce this issue and suspect that it's a
bug. I tried to solve it in response to another related report [1], where
it was apparent that the cause was related to how the bulk-insert mode in
the COPY FROM code is not handled correctly for a partitioned table. My
proposed solution [2] was to disable bulk-insert mode completely for
partitioned tables. But it may not be desirable performance-wise (for
example, COPY FROM on partitioned tables would have same performance as
INSERT, whereas in case of regular tables, COPY FROM is much faster than
INSERT due to the bulk insert mode).

I will propose another solution for the same. Meanwhile, could you please
try your test again with the patch posted at [1], although it will not
likely be committed as the fix for this issue.

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/CAFmBtr32FDOqofo8yG-4mjzL1HnYHxXK5S9OGFJ%3D%3DcJpgEW4vA%40mail.gmail.com

[2]
https://www.postgresql.org/message-id/101e2c2d-45d6-fb1a-468c-d3f67572a2f3%40lab.ntt.co.jp