Re: Declarative partitioning - another take

Regards,

Venkata B N
Database Consultant

On Fri, Dec 9, 2016 at 11:11 PM, Amit Langote <amitlangote09(at)gmail(dot)com>
wrote:

> On Fri, Dec 9, 2016 at 3:16 PM, Venkata B Nagothi <nag1010(at)gmail(dot)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')