Re: PostgreSQL: SQLSetPos fails with SetPos update return error.

Hi Takayuki,

Thanks for your patience and in depth response to my email.

WE are trying to certify our product with PostgreSQL DB. One of the features which our product supports is data partitioning on all RDBMS like SQL Server, Oracle, etc.
We want to support it for PostgreSQL DB too.
We cannot certify our product using partitioning with postgeSQL if the current issue is not solved.
I would like to understand that if I implement “Enabling Row Movement in a Partitioned Table”, will odbc driver handle this case?
Or odbc driver needs to be modified (as you mentioned in your previous response) to handle updates on partitioning keys.
Any samples to achieve automatic row movement would help in this case if it can be done.

Thanks,
Sekhar

From: Tsunakawa, Takayuki [mailto:tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com]
Sent: Wednesday, April 20, 2016 11:20 AM
To: Venkatesan, Sekhar; Inoue, Hiroshi
Cc: Adrian Klaver; Joshua D. Drake; pgsql-odbc(at)postgresql(dot)org
Subject: RE: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.

From: pgsql-odbc-owner(at)postgresql(dot)org<mailto:pgsql-odbc-owner(at)postgresql(dot)org> [mailto:pgsql-odbc-owner(at)postgresql(dot)org] On Behalf Of Venkatesan, Sekhar
Also I would like to understand which partition key is being referred here. IS there any information in MyLog output that indicates that?
This is just understand the use-case. Let me know.

No, it's an inspiration from your use of partitioning.

From: Venkatesan, Sekhar [mailto:sekhar(dot)venkatesan(at)emc(dot)com]
I need to understand how to write update triggers which you mentioned as the workaround.
Any examples/samples which I can refer would really help. Since all the tables are partitioned, I assume I have to make sure the odbc driver knows the partitioned table name’s row to update. This has to be implemented as part of the trigger. Isn’t it?

Sorry, I misunderstood. I'm afraid there's no workaround.

The ODBC driver probably has to handle the migration of a row between partitions. Currently it tracks the row position by fetching the new ctid value with "INSERT/UPDATE ... RETURNING ctid". The table name is fixed.

Instead, the driver has to fetch tableoid as well as ctid, and store it in memory with each row. In addition, the driver has to fetch the table name which corresponds to the tableoid, and store the mapping of tableoid to table name. It uses the map to build UPDATE statements. I'm not sure yet if it's feasible and how difficult, as I'm new to psqlODBC.

Until when does this problem need to be solved? What's the effect if it is not solved? Does your product fail to certify against PostgreSQL, or some function is unavailable?

Regards
Takayuki Tsunakawa