Error when using copy in partitions

[SunjinPeng2017]

Hello,
sql:
1.create partitions
select create_range_partitions('gem_s1u_http_kpi_1h_statistics'::regclass,'statistical_time','2017-02-08 00:00:00'::timestamp,interval '1 hour',96,false );
2. set_enable_parent
select set_enable_parent('gem_s1u_http_kpi_1h_statistics'::regclass,false);
3.set_auto
select set_auto('gem_s1u_http_kpi_1h_statistics'::regclass,false);

4.now,when i use copy to insert datas,it lists:
ERROR: attribute(statistical_time) 11 has wrong type. details：Table has type character varying, but query expects timestamp without time zone. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)...
5.if i drop all patitions ,it works well.

What i should do to solve this problem?

[ildus]

Can you provide information about table structure (from \d+)? Are you using 'master' branch?

[SunjinPeng2017]

I cloned codes from master about a week ago and i also tried to set pg_pathman.override_copy = off ,the result showed it had no errors when copying but database had no datas actually.
here is the structure:

GEMPILE_DATA=# \d+ gem_s1u_http_kpi_1h_statistics;
Table "public.gem_s1u_http_kpi_1h_statistics"
Column | Type | Modifiers | Storage |
-------------------------------+-----------------------------+---------------+----------+-
whole_network | character varying | | extended |
sgw | character varying | | extended |
tac | character varying | | extended |
enodeb_id | character varying | | extended |
cell_id | character varying | | extended |
imsi | character varying | | extended |
ue_brand | character varying | | extended |
ue_type | character varying | | extended |
business_type | character varying | | extended |
detail_business | character varying | | extended |
statistical_time | timestamp without time zone | not null | plain |
created_time | timestamp without time zone | default now() | plain |
tcp_up_link_request_count | bigint | | plain |
tcp_up_link_success_count | bigint | | plain |
tcp_up_link_success_ratio | numeric | | main |
tcp_up_link_timedelay | bigint | | plain |
tcp_dl_link_request_count | bigint | | plain |
tcp_dl_link_success_count | bigint | | plain |
tcp_dl_link_success_ratio | numeric | | main |
tcp_dl_link_timedelay | bigint | | plain |
tcp_link_success_ratio | numeric | | main |
tcp_link_timedelay | bigint | | plain |
Child tables: gem_s1u_http_kpi_1h_statistics_1,
gem_s1u_http_kpi_1h_statistics_10,
gem_s1u_http_kpi_1h_statistics_11,
gem_s1u_http_kpi_1h_statistics_12,
gem_s1u_http_kpi_1h_statistics_13,
gem_s1u_http_kpi_1h_statistics_14,
gem_s1u_http_kpi_1h_statistics_15,
gem_s1u_http_kpi_1h_statistics_16,
gem_s1u_http_kpi_1h_statistics_17,
gem_s1u_http_kpi_1h_statistics_18,
gem_s1u_http_kpi_1h_statistics_19,
gem_s1u_http_kpi_1h_statistics_2,
gem_s1u_http_kpi_1h_statistics_20,
gem_s1u_http_kpi_1h_statistics_21,
gem_s1u_http_kpi_1h_statistics_22,
gem_s1u_http_kpi_1h_statistics_23,
gem_s1u_http_kpi_1h_statistics_24,
gem_s1u_http_kpi_1h_statistics_25,
gem_s1u_http_kpi_1h_statistics_26,
gem_s1u_http_kpi_1h_statistics_27,
gem_s1u_http_kpi_1h_statistics_28,
gem_s1u_http_kpi_1h_statistics_29,
gem_s1u_http_kpi_1h_statistics_3,
gem_s1u_http_kpi_1h_statistics_30,
gem_s1u_http_kpi_1h_statistics_31,
gem_s1u_http_kpi_1h_statistics_32,
gem_s1u_http_kpi_1h_statistics_33,
gem_s1u_http_kpi_1h_statistics_34,
gem_s1u_http_kpi_1h_statistics_35,
gem_s1u_http_kpi_1h_statistics_36,
gem_s1u_http_kpi_1h_statistics_37,
gem_s1u_http_kpi_1h_statistics_38,
gem_s1u_http_kpi_1h_statistics_39,
gem_s1u_http_kpi_1h_statistics_4,
gem_s1u_http_kpi_1h_statistics_40,
gem_s1u_http_kpi_1h_statistics_41,
gem_s1u_http_kpi_1h_statistics_42,
gem_s1u_http_kpi_1h_statistics_43,
gem_s1u_http_kpi_1h_statistics_44,
gem_s1u_http_kpi_1h_statistics_45,
gem_s1u_http_kpi_1h_statistics_46,
gem_s1u_http_kpi_1h_statistics_47,
gem_s1u_http_kpi_1h_statistics_48,
gem_s1u_http_kpi_1h_statistics_49,
gem_s1u_http_kpi_1h_statistics_5,
gem_s1u_http_kpi_1h_statistics_50,
gem_s1u_http_kpi_1h_statistics_51,
gem_s1u_http_kpi_1h_statistics_52,
gem_s1u_http_kpi_1h_statistics_53,
gem_s1u_http_kpi_1h_statistics_54,
gem_s1u_http_kpi_1h_statistics_55,
gem_s1u_http_kpi_1h_statistics_56,
gem_s1u_http_kpi_1h_statistics_57,
gem_s1u_http_kpi_1h_statistics_58,
gem_s1u_http_kpi_1h_statistics_59,
gem_s1u_http_kpi_1h_statistics_6,
gem_s1u_http_kpi_1h_statistics_60,
gem_s1u_http_kpi_1h_statistics_61,
gem_s1u_http_kpi_1h_statistics_62,
gem_s1u_http_kpi_1h_statistics_63,
gem_s1u_http_kpi_1h_statistics_64,
gem_s1u_http_kpi_1h_statistics_65,
gem_s1u_http_kpi_1h_statistics_66,
gem_s1u_http_kpi_1h_statistics_67,
gem_s1u_http_kpi_1h_statistics_68,
gem_s1u_http_kpi_1h_statistics_69,
gem_s1u_http_kpi_1h_statistics_7,
gem_s1u_http_kpi_1h_statistics_70,
gem_s1u_http_kpi_1h_statistics_71,
gem_s1u_http_kpi_1h_statistics_72,
gem_s1u_http_kpi_1h_statistics_73,
gem_s1u_http_kpi_1h_statistics_74,
gem_s1u_http_kpi_1h_statistics_75,
gem_s1u_http_kpi_1h_statistics_76,
gem_s1u_http_kpi_1h_statistics_77,
gem_s1u_http_kpi_1h_statistics_78,
gem_s1u_http_kpi_1h_statistics_79,
gem_s1u_http_kpi_1h_statistics_8,
gem_s1u_http_kpi_1h_statistics_80,
gem_s1u_http_kpi_1h_statistics_81,
gem_s1u_http_kpi_1h_statistics_82,
gem_s1u_http_kpi_1h_statistics_83,
gem_s1u_http_kpi_1h_statistics_84,
gem_s1u_http_kpi_1h_statistics_85,
gem_s1u_http_kpi_1h_statistics_86,
gem_s1u_http_kpi_1h_statistics_87,
gem_s1u_http_kpi_1h_statistics_88,
gem_s1u_http_kpi_1h_statistics_89,
gem_s1u_http_kpi_1h_statistics_9,
gem_s1u_http_kpi_1h_statistics_90,
gem_s1u_http_kpi_1h_statistics_91,
gem_s1u_http_kpi_1h_statistics_92,
gem_s1u_http_kpi_1h_statistics_93,
gem_s1u_http_kpi_1h_statistics_94,
gem_s1u_http_kpi_1h_statistics_95,
gem_s1u_http_kpi_1h_statistics_96

[PatrickYung]

Here is a simple test case to recreate this issue.

I created a csv file with only 1 timestamp value.
bash-4.1$ echo "2017-04-21 20:00:00" > /tmp/test.csv

Firstly I copy to a table WITHOUTH partitioning, result is good:

DATA=# copy table_without_partition(created_time) from '/tmp/test.csv' with csv;
COPY 1

But it goes wrong when copy to a table partitioned with field created_time.

DATA=# copy table_with_partition(created_time) from '/tmp/test.csv' with csv;
ERROR:  attribute 11 has wrong type
DETAIL:  Table has type character varying, but query expects timestamp without time zone.

Also I tried input differnt timestamp format in the csv file, all result in the same error message above.

bash-4.1$ echo "2017-02-09 20:00:00 GMT" > test.csv
bash-4.1$ echo "2017-02-09 20:00:00.000000" > test.csv
bash-4.1$ echo "2017-02-09 20:00:00+00" > test.csv
bash-4.1$ echo "2017-02-09 20:00:00.00" > test.csv

[ildus]

Hi, you have created partitions by statictical_time and trying to insert only 'created_time' column. There is no way how pg_pathman will determine which partition should it use to insert data.

[ildus]

I tried to reproduce your case. In my case all ok:

SET search_path = 'public';

CREATE SCHEMA pathman;
CREATE EXTENSION pg_pathman SCHEMA pathman;
CREATE SCHEMA test;

CREATE TABLE IF NOT EXISTS test.gem_s1u_http_kpi_1h_statistics (
	whole_network varchar(50),
	sgw varchar(50),
	tac varchar(50),
	created_time timestamp without time zone not null
);
select pathman.create_range_partitions('test.gem_s1u_http_kpi_1h_statistics',
	'created_time',
	'2017-02-08 00:00:00'::timestamp,
	interval '1 hour', 96, false);

from psql:

d1=# select * from test.gem_s1u_http_kpi_1h_statistics;
 whole_network | sgw | tac | created_time 
---------------+-----+-----+--------------
(0 rows)

d1=# copy test.gem_s1u_http_kpi_1h_statistics(created_time) from '/tmp/test.csv' with csv;
PATHMAN COPY 1
d1=# select tableoid::regclass, * from test.gem_s1u_http_kpi_1h_statistics;
                 tableoid                 | whole_network | sgw | tac |    created_time     
------------------------------------------+---------------+-----+-----+---------------------
 test.gem_s1u_http_kpi_1h_statistics_1749 |               |     |     | 2017-04-21 20:00:00
(1 row)

I suspect it can be related with dropped columns. Can you provide result of this query:

select * from pg_attribute where attrelid = 'gem_s1u_http_kpi_1h_statistics'::regclass;

[SunjinPeng2017]

Here is the result and thank you for your response;

GEMPILE_DATA=# select * from pg_attribute where attrelid = 'gem_s1u_http_kpi_1h_statistics'::regclass;
attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions
----------+-------------------------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+--------------+------------+-------------+--------------+--------+------------+---------------
65881 | tableoid | 26 | 0 | 4 | -7 | 0 | -1 | -1 | t | p | i | t
| f | f | t | 0 | 0 | | |
65881 | cmax | 29 | 0 | 4 | -6 | 0 | -1 | -1 | t | p | i | t
| f | f | t | 0 | 0 | | |
65881 | xmax | 28 | 0 | 4 | -5 | 0 | -1 | -1 | t | p | i | t
| f | f | t | 0 | 0 | | |
65881 | cmin | 29 | 0 | 4 | -4 | 0 | -1 | -1 | t | p | i | t
| f | f | t | 0 | 0 | | |
65881 | xmin | 28 | 0 | 4 | -3 | 0 | -1 | -1 | t | p | i | t
| f | f | t | 0 | 0 | | |
65881 | ctid | 27 | 0 | 6 | -1 | 0 | -1 | -1 | f | p | s | t
| f | f | t | 0 | 0 | | |
65881 | ........pg.dropped.1........ | 0 | 0 | -1 | 1 | 0 | -1 | -1 | f | p | i | f
| f | t | t | 0 | 0 | | |
65881 | whole_network | 1043 | -1 | -1 | 2 | 0 | -1 | -1 | f | x | i | f
| f | f | t | 0 | 100 | | |
65881 | sgw | 1043 | -1 | -1 | 3 | 0 | -1 | -1 | f | x | i | f
| f | f | t | 0 | 100 | | |
65881 | tac | 1043 | -1 | -1 | 4 | 0 | -1 | -1 | f | x | i | f
| f | f | t | 0 | 100 | | |
65881 | enodeb_id | 1043 | -1 | -1 | 5 | 0 | -1 | -1 | f | x | i | f
| f | f | t | 0 | 100 | | |
65881 | cell_id | 1043 | -1 | -1 | 6 | 0 | -1 | -1 | f | x | i | f
| f | f | t | 0 | 100 | | |
65881 | imsi | 1043 | -1 | -1 | 7 | 0 | -1 | -1 | f | x | i | f
| f | f | t | 0 | 100 | | |
65881 | ue_brand | 1043 | -1 | -1 | 8 | 0 | -1 | -1 | f | x | i | f
| f | f | t | 0 | 100 | | |
65881 | ue_type | 1043 | -1 | -1 | 9 | 0 | -1 | -1 | f | x | i | f
| f | f | t | 0 | 100 | | |
65881 | business_type | 1043 | -1 | -1 | 10 | 0 | -1 | -1 | f | x | i | f
| f | f | t | 0 | 100 | | |
65881 | detail_business | 1043 | -1 | -1 | 11 | 0 | -1 | -1 | f | x | i | f
| f | f | t | 0 | 100 | | |
65881 | statistical_time | 1114 | -1 | 8 | 12 | 0 | -1 | -1 | t | p | d | t
| f | f | t | 0 | 0 | | |
65881 | created_time | 1114 | -1 | 8 | 13 | 0 | -1 | -1 | t | p | d | f
| t | f | t | 0 | 0 | | |
65881 | tcp_up_link_request_count | 20 | -1 | 8 | 14 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | tcp_up_link_success_count | 20 | -1 | 8 | 15 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | tcp_up_link_success_ratio | 1700 | -1 | -1 | 16 | 0 | -1 | -1 | f | m | i | f
| f | f | t | 0 | 0 | | |
65881 | tcp_up_link_timedelay | 20 | -1 | 8 | 17 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | tcp_dl_link_request_count | 20 | -1 | 8 | 18 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | tcp_dl_link_success_count | 20 | -1 | 8 | 19 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | tcp_dl_link_success_ratio | 1700 | -1 | -1 | 20 | 0 | -1 | -1 | f | m | i | f
| f | f | t | 0 | 0 | | |
65881 | tcp_dl_link_timedelay | 20 | -1 | 8 | 21 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | tcp_link_success_ratio | 1700 | -1 | -1 | 22 | 0 | -1 | -1 | f | m | i | f
| f | f | t | 0 | 0 | | |
65881 | tcp_link_timedelay | 20 | -1 | 8 | 23 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | up_tcp_retrans_count | 20 | -1 | 8 | 24 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | up_tcp_retrans_ratio | 1700 | -1 | -1 | 25 | 0 | -1 | -1 | f | m | i | f
| f | f | t | 0 | 0 | | |
65881 | dl_tcp_retrans_count | 20 | -1 | 8 | 26 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | dl_tcp_retrans_ratio | 1700 | -1 | -1 | 27 | 0 | -1 | -1 | f | m | i | f
| f | f | t | 0 | 0 | | |
65881 | up_ip_packet_slicing_count | 20 | -1 | 8 | 28 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | up_ip_packet_slicing_ratio | 1700 | -1 | -1 | 29 | 0 | -1 | -1 | f | m | i | f
| f | f | t | 0 | 0 | | |
65881 | dl_ip_packet_slicing_count | 20 | -1 | 8 | 30 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | dl_ip_packet_slicing_ratio | 1700 | -1 | -1 | 31 | 0 | -1 | -1 | f | m | i | f
| f | f | t | 0 | 0 | | |
65881 | up_tcp_disorder_count | 20 | -1 | 8 | 32 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | up_tcp_disorder_ratio | 1700 | -1 | -1 | 33 | 0 | -1 | -1 | f | m | i | f
| f | f | t | 0 | 0 | | |
65881 | dl_tcp_disorder_count | 20 | -1 | 8 | 34 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | dl_tcp_disorder_ratio | 1700 | -1 | -1 | 35 | 0 | -1 | -1 | f | m | i | f
| f | f | t | 0 | 0 | | |
65881 | tcp_dropping_count | 20 | -1 | 8 | 36 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | tcp_dropping_ratio | 1700 | -1 | -1 | 37 | 0 | -1 | -1 | f | m | i | f
| f | f | t | 0 | 0 | | |
65881 | get_request_count | 20 | -1 | 8 | 38 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | get_success_count | 20 | -1 | 8 | 39 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | get_success_ratio | 1700 | -1 | -1 | 40 | 0 | -1 | -1 | f | m | i | f
| f | f | t | 0 | 0 | | |
65881 | get_resp_timedelay | 20 | -1 | 8 | 41 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | post_request_count | 20 | -1 | 8 | 42 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | post_success_count | 20 | -1 | 8 | 43 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | post_success_ratio | 1700 | -1 | -1 | 44 | 0 | -1 | -1 | f | m | i | f
| f | f | t | 0 | 0 | | |
65881 | post_resp_timedelay | 20 | -1 | 8 | 45 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | http_request_count | 20 | -1 | 8 | 46 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | http_success_count | 20 | -1 | 8 | 47 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | http_success_ratio | 1700 | -1 | -1 | 48 | 0 | -1 | -1 | f | m | i | f
| f | f | t | 0 | 0 | | |
65881 | http_resp_timedelay | 20 | -1 | 8 | 49 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | http_dlup_data_more_than_500k | 20 | -1 | 8 | 50 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | http_timedelay_more_than_500k | 20 | -1 | 8 | 51 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | http_speed_more_than_500k | 20 | -1 | 8 | 52 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | rtsp_playing_request_count | 20 | -1 | 8 | 53 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | rtsp_playing_success_count | 20 | -1 | 8 | 54 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | rtsp_playing_success_ratio | 1700 | -1 | -1 | 55 | 0 | -1 | -1 | f | m | i | f
| f | f | t | 0 | 0 | | |
65881 | rtsp_playing_waiting_time | 20 | -1 | 8 | 56 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | ul_ip_packet | 20 | -1 | 8 | 57 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
65881 | dl_ip_packet | 20 | -1 | 8 | 58 | 0 | -1 | -1 | t | p | d | f
| f | f | t | 0 | 0 | | |
(64 rows)

[ildus]

Thank you for your report. There was an actual bug, I pushed a fix to 'master' branch.

[SunjinPeng2017]

1.Right now ,we recreate this table and it inserts datas successfully but we don't know why.

2.We also find another problem : After inserting datas ,hadoop will return a status of COPY ,but pg_pathman returns PATHMAN COPY. we come up with two ways to solve this problem:
1).We override this class to match PATHMAN COPY
2).You refactor master to match hadoop's COPY

I'll be glad to hear from you!

[ildus]

1. Yes, new table will not have dropped column and that's why it worked. Anyway it fixed now.
2. We will think about it.