Re: [HACKERS] Declarative partitioning - another take
| От | Venkata B Nagothi |
|---|---|
| Тема | Re: [HACKERS] Declarative partitioning - another take |
| Дата | |
| Msg-id | CAEyp7J9WiX0L3DoiNcRrY-9iyw=qP+j=DLsAnNFF1xT2J1ggfQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: [HACKERS] Declarative partitioning - another take (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>) |
| Ответы |
Re: [HACKERS] Declarative partitioning - another take
(Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>)
|
| Список | pgsql-hackers |
On Mon, Dec 12, 2016 at 3:06 PM, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote:
Hi,
On 2016/12/11 10:02, Venkata B Nagothi wrote:
> On Fri, Dec 9, 2016 at 11:11 PM, Amit Langote <amitlangote09@gmail.com>
> wrote:
>> On Fri, Dec 9, 2016 at 3:16 PM, Venkata B Nagothi <nag1010@gmail.com>
>> wrote:[ ... ]>>> I am testing the partitioning feature from the latest master and got the
>>> following error while loading the data -
>>>
>>> db01=# create table orders_y1993 PARTITION OF orders FOR VALUES FROM
>>> ('1993-01-01') TO ('1993-12-31');
>>> CREATE TABLE
>>>
>>> db01=# copy orders from '/data/orders-1993.csv' delimiter '|';
>>> ERROR: could not read block 6060 in file "base/16384/16412": read only
>> 0 of
>>> 8192 bytes
>>> CONTEXT: COPY orders, line 376589:
>>> "9876391|374509|O|54847|1997-07-16|3-MEDIUM
>> |Clerk#000001993|0|ithely
>>> regular pack"
>>
>> Hmm. Could you tell what relation the file/relfilenode 16412 belongs to?
>>
>
> db01=# select relname from pg_class where relfilenode=16412 ;
> relname
> --------------
> orders_y1997
> (1 row)
>
>
> I VACUUMED the partition and then re-ran the copy command and no luck.
>
> db01=# vacuum orders_y1997;
> VACUUM
>
> db01=# copy orders from '/data/orders-1993.csv' delimiter '|';
> ERROR: could not read block 6060 in file "base/16384/16412": read only 0
> of 8192 bytes
> CONTEXT: COPY orders, line 376589:
> "9876391|374509|O|54847|1997-07-16|3-MEDIUM |Clerk#000001993|0|ithely
> regular pack"
>
> I do not quite understand the below behaviour as well. I VACUUMED 1997
> partition and then i got an error for 1992 partition and then after 1996
> and then after 1994 and so on.
>
> db01=# vacuum orders_y1997;
> VACUUM
> db01=# copy orders from '/data/orders-1993.csv' delimiter '|';
> ERROR: could not read block 6060 in file "base/16384/16412": read only 0
> of 8192 bytes
> CONTEXT: COPY orders, line 376589:
> "9876391|374509|O|54847|1997-07-16|3-MEDIUM |Clerk#000001993|0|ithely
> regular pack"
> db01=#
>
> Am i not understanding anything here ?
I could not reproduce this issue. Also, I could not say what might have
gone wrong based only on the information I have seen so far.
Have you tried inserting the same data using insert?
I can load the data into appropriate partitions using INSERT. So, no issues there.
db01=# CREATE TABLE orders2(
o_orderkey INTEGER,
o_custkey INTEGER,
o_orderstatus CHAR(1),
o_totalprice REAL,
o_orderdate DATE,
o_orderpriority CHAR(15),
o_clerk CHAR(15),
o_shippriority INTEGER,
o_comment VARCHAR(79)) partition by (o_orderdate);
db01=# insert into orders2 select * from orders where o_orderdate='1995-10-11';
INSERT 0 3110
create table orders_unpartitioned (like orders);
copy orders_unpartitioned from '/data/orders-1993.csv';
insert into orders select * from orders_unpartitioned;
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.
Regards,
Venkata B N
Database Consultant
В списке pgsql-hackers по дате отправления: