analyzedb utility fails when analyzing the relation name containing a hyphen in Pivotal HDB

Products

• Services Suite

Issue/Introduction

When trying to analyze which relation name contains a hyphen (-) with the analyzedb utility, it fails with the error message, ERROR:  syntax error at or near "-".

Error Message

gpadmin@hdm1 ~]$ analyzedb -d gpadmin -t public.mytab1_1_prt_2016-04-27 -a 
20160823:21:03:27:745430 analyzedb:hdm1:gpadmin-[INFO]:-Starting analyzedb with args: -d gpadmin -t public.mytab1_1_prt_2016-04-27 -a
20160823:21:03:27:745430 analyzedb:hdm1:gpadmin-[INFO]:-Getting and verifying input tables...
20160823:21:03:27:745430 analyzedb:hdm1:gpadmin-[INFO]:-Checking for tables with stale stats...
20160823:21:03:28:745430 analyzedb:hdm1:gpadmin-[INFO]:----------------------------------------------------
20160823:21:03:28:745430 analyzedb:hdm1:gpadmin-[INFO]:-Tables or partitions to be analyzed
20160823:21:03:28:745430 analyzedb:hdm1:gpadmin-[INFO]:----------------------------------------------------
20160823:21:03:28:745430 analyzedb:hdm1:gpadmin-[INFO]:-public.mytab1_1_prt_2016-04-27
20160823:21:03:28:745430 analyzedb:hdm1:gpadmin-[INFO]:-public.mytab1
20160823:21:03:28:745430 analyzedb:hdm1:gpadmin-[INFO]:----------------------------------------------------
20160823:21:03:28:745430 analyzedb:hdm1:gpadmin-[INFO]:-Starting analyze with 2 workers...
20160823:21:03:28:745430 analyzedb:hdm1:gpadmin-[INFO]:-[worker0] started analyze public.mytab1_1_prt_2016-04-27
20160823:21:03:28:745430 analyzedb:hdm1:gpadmin-[INFO]:-[worker1] started analyze rootpartition public.mytab1
20160823:21:03:29:745430 analyzedb:hdm1:gpadmin-[INFO]:-[worker1] finished analyze rootpartition public.mytab1. Elapsed time: 0 seconds.
20160823:21:03:29:745430 analyzedb:hdm1:gpadmin-[WARNING]:-ERROR: syntax error at or near "-"
LINE 1: analyze public.mytab1_1_prt_2016-04-27
 ^
20160823:21:03:29:745430 analyzedb:hdm1:gpadmin-[INFO]:-Created /data/hawq/master/gpseg-1//db_analyze/gpadmin/20160823210327
20160823:21:03:29:745430 analyzedb:hdm1:gpadmin-[INFO]:-Writing report file /data/hawq/master/gpseg-1//db_analyze/gpadmin/20160823210327/analyze_20160823210327_report
20160823:21:03:29:745430 analyzedb:hdm1:gpadmin-[INFO]:-Total elapsed time: 0 seconds. Analyzed 1 out of 2 table(s) or partition(s) successfully.
20160823:21:03:29:745430 analyzedb:hdm1:gpadmin-[INFO]:-Done. 

Environment

OS: RHEL 6.x

Resolution

The relation name containing the hyphen must be double-quoted in the SQL statement. However, analyzedb does not handle it this way and this causes the failure. 

1. The issue is already solved in the HDB 2.x release as illustrated below. Upgrading the HDB system to the 2.x release is recommended to pick up the fix.

[gpadmin@hdm1 ~]$ analyzedb -d gpadmin -t public.mytab1_1_prt_2016-04-27 -a
20160824:08:25:32:741382 analyzedb:hdm1:gpadmin-[INFO]:-Starting analyzedb with args: -d gpadmin -t public.mytab1_1_prt_2016-04-27 -a
20160824:08:25:33:741382 analyzedb:hdm1:gpadmin-[INFO]:-Getting and verifying input tables...
20160824:08:25:33:741382 analyzedb:hdm1:gpadmin-[INFO]:-Checking for tables with stale stats...
20160824:08:25:34:741382 analyzedb:hdm1:gpadmin-[INFO]:----------------------------------------------------
20160824:08:25:34:741382 analyzedb:hdm1:gpadmin-[INFO]:-Tables or partitions to be analyzed
20160824:08:25:34:741382 analyzedb:hdm1:gpadmin-[INFO]:----------------------------------------------------
20160824:08:25:34:741382 analyzedb:hdm1:gpadmin-[INFO]:-public.mytab1_1_prt_2016-04-27
20160824:08:25:34:741382 analyzedb:hdm1:gpadmin-[INFO]:-public.mytab1
20160824:08:25:34:741382 analyzedb:hdm1:gpadmin-[INFO]:----------------------------------------------------
20160824:08:25:34:741382 analyzedb:hdm1:gpadmin-[INFO]:-Starting analyze with 2 workers...
20160824:08:25:34:741382 analyzedb:hdm1:gpadmin-[INFO]:-[worker0] started analyze public.mytab1_1_prt_2016-04-27
20160824:08:25:34:741382 analyzedb:hdm1:gpadmin-[INFO]:-[worker1] started analyze rootpartition public.mytab1
20160824:08:25:34:741382 analyzedb:hdm1:gpadmin-[INFO]:-[worker0] finished analyze public.mytab1_1_prt_2016-04-27. Elapsed time: 0 seconds.
20160824:08:25:34:741382 analyzedb:hdm1:gpadmin-[INFO]:-[worker1] finished analyze rootpartition public.mytab1. Elapsed time: 0 seconds.
20160824:08:25:34:741382 analyzedb:hdm1:gpadmin-[INFO]:-Created /data/hawq/master/db_analyze/gpadmin/20160824082533
20160824:08:25:34:741382 analyzedb:hdm1:gpadmin-[INFO]:-Writing ao state file /data/hawq/master/db_analyze/gpadmin/20160824082533/analyze_20160824082533_ao_state_file
20160824:08:25:34:741382 analyzedb:hdm1:gpadmin-[INFO]:-Writing last operation file /data/hawq/master/db_analyze/gpadmin/20160824082533/analyze_20160824082533_last_operation
20160824:08:25:34:741382 analyzedb:hdm1:gpadmin-[INFO]:-Writing column state file /data/hawq/master/db_analyze/gpadmin/20160824082533/analyze_20160824082533_col_state_file
20160824:08:25:34:741382 analyzedb:hdm1:gpadmin-[INFO]:-Writing report file /data/hawq/master/db_analyze/gpadmin/20160824082533/analyze_20160824082533_report
20160824:08:25:34:741382 analyzedb:hdm1:gpadmin-[INFO]:-Total elapsed time: 0 seconds. Analyzed 2 out of 2 table(s) or partition(s) successfully.
20160824:08:25:34:741382 analyzedb:hdm1:gpadmin-[INFO]:-Done.

2. Before upgrading to the HDB 2.x release, the workaround of this issue is to run the analyze SQL command by including the relation name with double quotes through the client application directly instead of using the analyzedb utility. For example:

gpadmin=# analyze public."mytab1_1_prt_2016-04-27";
ANALYZE

Additional Information

For additional information, please use the following resource: 

• User guide of analyzedb