Thread: Updateable cursors ODBC configuration Partition Issue

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

Though table alias is not required in this case J, if I have table alias it should be included in the resultant SQL which is getting missed when “updateable cursors” is enabled.

I am not seeing this issue with linux. Looks to be specific to windows driver.

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

Hmm,  table alias AFD_. is necessary for the above statement?

regards,
Hiroshi Inoue

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