Re: [HACKERS] Declarative partitioning - another take - Mailing list pgsql-hackers
| From | Venkata B Nagothi |
|---|---|
| Subject | Re: [HACKERS] Declarative partitioning - another take |
| Date | |
| Msg-id | CAEyp7J8VvjCvDRZKFnS-hYm=um7r0Tfu4s55tEZjUgA3oHpyrw@mail.gmail.com Whole thread Raw |
| In response to | Re: [HACKERS] Declarative partitioning - another take (Amit Langote <amitlangote09@gmail.com>) |
| Responses |
Re: [HACKERS] Declarative partitioning - another take
|
| List | pgsql-hackers |
Venkata B N
Database Consultant
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:
> Hi,
>
> 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.
postgres=# \c db01
You are now connected to database "db01" as user "dba".
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=# vacuum orders_y1997;
VACUUM
db01=# copy orders from '/data/orders-1993.csv' delimiter '|';
ERROR: could not read block 3942 in file "base/16384/16406": read only 0 of 8192 bytes
CONTEXT: COPY orders, line 75445: "1993510|185287|F|42667.9|1992-08-15|2-HIGH |Clerk#000000079|0| dugouts above the even "
db01=# select relname from pg_class where relfilenode=16406;
relname
--------------
orders_y1992
(1 row)
db01=# copy orders from '/data/orders-1993.csv' delimiter '|';
ERROR: could not read block 3942 in file "base/16384/16406": read only 0 of 8192 bytes
CONTEXT: COPY orders, line 75396: "1993317|260510|F|165852|1992-12-13|5-LOW |Clerk#000003023|0|regular foxes. ironic dependenc..."
db01=# vacuum orders_y1992;
VACUUM
db01=# copy orders from '/data/orders-1993.csv' delimiter '|';
ERROR: could not read block 3708 in file "base/16384/16394": read only 0 of 8192 bytes
CONTEXT: COPY orders, line 178820: "4713957|286270|O|200492|1996-10-01|1-URGENT |Clerk#000001993|0|uriously final packages. slyly "
db01=# select relname from pg_class where relfilenode=16394;
relname
--------------
orders_y1996
(1 row)
db01=# vacuum orders_y1996;
VACUUM
db01=# copy orders from '/data/orders-1993.csv' delimiter '|';
ERROR: could not read block 5602 in file "base/16384/16403": read only 0 of 8192 bytes
CONTEXT: COPY orders, line 147390: "3882662|738010|F|199365|1994-12-26|5-LOW |Clerk#000001305|0|ar instructions above the expre..."
db01=# select relname from pg_class where relfilenode=16403;
relname
--------------
orders_y1994
(1 row)
db01=# vacuum orders_y1994;
VACUUM
db01=# copy orders from '/data/orders-1993.csv' delimiter '|';
ERROR: could not read block 5561 in file "base/16384/16412": read only 0 of 8192 bytes
CONTEXT: COPY orders, line 59276: "1572448|646948|O|25658.6|1997-05-02|4-NOT SPECIFIED|Clerk#000001993|0|es. ironic, regular p"
And finally the error again occurred for 1997 partition
db01=# select relname from pg_class where relfilenode=16412;
relname
--------------
orders_y1997
(1 row)
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 ?
Also, is orders_y1993 the only partition of orders? How about \d+ orders?
Yes, i created multiple yearly partitions for orders table. I wanted to 1993 year's data first and see if the data goes into orders_y1993 partition and itseems that, the CSV contains 1997 data as wellCopy command found a
db01=# \d+ orders
Table "public.orders"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
o_orderkey | integer | | | | plain | |
o_custkey | integer | | | | plain | |
o_orderstatus | character(1) | | | | extended | |
o_totalprice | real | | | | plain | |
o_orderdate | date | | not null | | plain | |
o_orderpriority | character(15) | | | | extended | |
o_clerk | character(15) | | | | extended | |
o_shippriority | integer | | | | plain | |
o_comment | character varying(79) | | | | extended | |
Partition key: RANGE (o_orderdate)
Partitions: orders_y1992 FOR VALUES FROM ('1992-01-01') TO ('1992-12-31'),
orders_y1993 FOR VALUES FROM ('1993-01-01') TO ('1993-12-31'),
orders_y1994 FOR VALUES FROM ('1994-01-01') TO ('1994-12-31'),
orders_y1995 FOR VALUES FROM ('1995-01-01') TO ('1995-12-31'),
orders_y1996 FOR VALUES FROM ('1996-01-01') TO ('1996-12-31'),
orders_y1997 FOR VALUES FROM ('1997-01-01') TO ('1997-12-31'),
orders_y1998 FOR VALUES FROM ('1998-01-01') TO ('1998-12-31')
pgsql-hackers by date: