Thanks Takayuki for your reply. I got it. Hopefully future versions support this use-case as you mentioned. Keep me posted as and when things happen in this area.
To my other question about fix to resolve child inheritance done by Inoue. I need the fix for windows and linux as part of oDBC driver? Where can I find the new drivers? I understand that Inoue checked-in the fix into GIT but I cannot see any source changes in https://www.postgresql.org/ftp/odbc location?
This fix is urgently needed for our functional testing. Can you please assist expediting this request?
Thanks in advance for all the help in this regard.
-Sekhar
From: Tsunakawa, Takayuki [mailto:tsunakawa.takay@jp.fujitsu.com]
Sent: Wednesday, June 15, 2016 6:36 AM
To: Venkatesan, Sekhar
Cc: Inoue, Hiroshi; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: SQLSetPos fails with SetPos update return error.
Hi, Sekhar,
Sorry for my late response.
From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Venkatesan, Sekhar
To work around the problem, I created UPDATE database triggers on partitioned tables that redirects the UPDATEs as INSERT statement on main table (testpart25_s) and the INSERT trigger on main table would push the data to the partitioned table.
This works as expected. But I’m not sure if all use-cases would work with this approach.
Your approach of creating an UPDATE trigger and making it move the row between partitions is correct. As I mentioned before, the current PostgreSQL doesn't automatically move updated rows to their appropriate partions, which is described here:
https://www.postgresql.org/docs/devel/static/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS
[Excerpt]
The schemes shown here assume that the partition key column(s) of a row never change, or at least do not change enough to require it to move to another partition. An UPDATE that attempts to do that will fail because of the CHECK constraints. If you need to handle such cases, you can put suitable update triggers on the partition tables, but it makes management of the structure much more complicated.
A slightly different alternative would be to make your UPDATE trigger INSERT directly into the appropriate child table (p4_testpart_s) and DELETE from the old child table (p1_testpart_s), only when the partition changes. That would be more efficient because INSERT trigger does not have to be executed. That would complicate the trigger logic, and it's a trade-off.
It would be great if PostgreSQL/ODBC driver itself does this automatic row movement rather than the application handling it. (Other databases like Oracle and SQL Server handle this by itself).
That’s a pain point in the current PostgreSQL, and I really wish it will be solved. Fortunately, the community is addressing the real partitioning feature, which does not use table inheritance, in the next remajor version (9.7 or 10.0) which will be released in September 2017.
Regards
Takayuki Tsunakawa