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
