Updateable cursors ODBC configuration Partition Issue

From: "Venkatesan, Sekhar" <sekhar(dot)venkatesan(at)emc(dot)com>
To: "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>, "Inoue, Hiroshi" <h-inoue(at)dream(dot)email(dot)ne(dot)jp>
Cc: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, "pgsql-odbc(at)postgresql(dot)org" <pgsql-odbc(at)postgresql(dot)org>
Subject: Updateable cursors ODBC configuration Partition Issue
Date: 2016-07-01 09:07:08
Message-ID: F84DE43FDACD4C45AA84E2DA016FAE2F1D104CAA@MX205CL01.corp.emc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

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

Responses

Browse pgsql-odbc by date

  From Date Subject
Next Message Jan Espen Kleivane 2016-07-01 10:01:34 psqlodbc_x64 timeout available?
Previous Message Tsunakawa, Takayuki 2016-07-01 05:38:04 Re: PostgreSQL: SQLSetPos fails with SetPos update return error.