Horton

Re: Declarative partitioning - another take

Amit Langote <langote_amit_f8@lab.ntt.co.jp>

View thread View on postgr.es
From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: Venkata B Nagothi <nag1010@gmail.com>
Cc: Amit Langote <amitlangote09@gmail.com>, Robert Haas <robertmhaas@gmail.com>, Michael Paquier <michael.paquier@gmail.com>, Andres Freund <andres@anarazel.de>, Erik Rijkers <er@xs4all.nl>, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>, Pg Hackers <pgsql-hackers@postgresql.org>, pgsql-hackers-owner@postgresql.org
Date: 2017-01-05T08:10:27Z
Lists: pgsql-hackers 
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

Commits

  1. Fix typo.

  2. Document trigger-firing behavior for inheritance/partitioning.

  3. Fire per-statement triggers on partitioned tables.

  4. Set ecxt_scantuple correctly for tuple routing.

  5. Fix interaction of partitioned tables with BulkInsertState.

  6. Avoid core dump for empty prepared statement in an aborted transaction.

  7. Fix some problems in check_new_partition_bound().

  8. Remove unnecessary arguments from partitioning functions.

  9. Fix reporting of constraint violations for table partitioning.

  10. Fix tuple routing in cases where tuple descriptors don't match.

  11. Invalid parent's relcache after CREATE TABLE .. PARTITION OF.

  12. Doc: improve documentation about inheritance.