On Mon, Dec 12, 2016 at 3:06 PM, Amit Langote <langote_amit...@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 <amitlangot...@gmail.com> > > wrote: > >> On Fri, Dec 9, 2016 at 3:16 PM, Venkata B Nagothi <nag1...@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