Re: Updateable cursors ODBC configuration Partition Issue

On 2016/07/01 18:07, Venkatesan, Sekhar wrote:
>
> 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
>