Hi Guys,
I see yet another issue when data partition is enabled and “updateable cursors” option is enabled in odbc DSN.
Updateable cursors performs “select for update” cursor and when the PostgreSQL cursor is created for a select query, the resultant statement is as below:
The original SQL fired from our application is:
“SELECT AFD_.r_object_id,AFD_.name,AFD_.stamp,AFD_.sent_by,AFD_.date_sent,AFD_.due_date,AFD_.event,AFD_.item_name,AFD_.item_id,AFD_.item_type,AFD_.content_type,AFD_.message,AFD_.router_id,AFD_.supervisor_name,AFD_.task_number,AFD_.task_name,AFD_.task_type,AFD_.task_state,AFD_.dependency_type,AFD_.next_tasks_type,AFD_.instruction_page,AFD_.plan_start_date,AFD_.actual_start_date,AFD_.read_flag,AFD_.delete_flag,AFD_.priority,AFD_.position,AFD_.dequeued_by,AFD_.dequeued_date,AFD_.sign_off_required,AFD_.sign_off_user,AFD_.sign_off_date,AFD_.a_content_type,AFD_.a_operations,AFD_.source_docbase,AFD_.target_docbase,AFD_.remote_pending,AFD_.source_event,AFD_.source_stamp,AFD_.task_subject,AFD_.i_event_flags,AFD_.event_detail,AFD_.i_partition,AFD_.i_is_replica,AFD_.i_vstamp FROM dmi_queue_item_s AFD_ WHERE ((AFD_.name=N'Administrator' AND AFD_.read_flag=0) AND AFD_.delete_flag=0)”
whereas this is converted to:
“SELECT
AFD_.r_object_id,AFD_.name,AFD_.stamp,AFD_.sent_by,AFD_.date_sent,AFD_.due_date,AFD_.event,AFD_.item_name,AFD_.item_id,AFD_.item_type,AFD_.content_type,AFD_.message,AFD_.router_id,AFD_.supervisor_name,AFD_.task_number,AFD_.task_name,AFD_.task_type,AFD_.task_state,AFD_.dependency_type,AFD_.next_tasks_type,AFD_.instruction_page,AFD_.plan_start_date,AFD_.actual_start_date,AFD_.read_flag,AFD_.delete_flag,AFD_.priority,AFD_.position,AFD_.dequeued_by,AFD_.dequeued_date,AFD_.sign_off_required,AFD_.sign_off_user,AFD_.sign_off_date,AFD_.a_content_type,AFD_.a_operations,AFD_.source_docbase,AFD_.target_docbase,AFD_.remote_pending,AFD_.source_event,AFD_.source_stamp,AFD_.task_subject,AFD_.i_event_flags,AFD_.event_detail,AFD_.i_partition,AFD_.i_is_replica,AFD_.i_vstamp , "ctid", "tableoid" from "repo50"."p1_dmi_queue_item_s" where ctid = currtid2('"repo50"."p1_dmi_queue_item_s"', '(13, 28)')”
Notice that "p1_dmi_queue_item_s" is a partitioned table. Here the highlighted color is generated by postgresql for faster execution. But the table alias is not added to the partitioned table (AFD_ is not added to p1_dmi_queue_item_s) due to which the SQL fails to execute with the below error:
ERROR: missing FROM-clause entry for table "afd_" at character 8
Is this a known issue? Is there any fix? If so, is it in psqlODBC or in the server itself?
Thanks,
Sekhar