Re: Declarative partitioning - another take

Regards,

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')