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

From: Venkatesan, Sekhar [mailto:sekhar(dot)venkatesan(at)emc(dot)com]
FYI: After I enable DB partitioning feature , I see this problem.

I guess your app performed an UPDATE statement which changes the value of the partitioning key columns.

Unfortunately, as Inoue-san said, the ODBC driver cannot handle that case. Changing the partitioning key value causes the database server to move the row to a different partition (= child table). On the other hand, the ODBC driver uses the table name and ctid to position the row to update. But the driver uses the original table name, not the target child table name, failing to find the row to update. The driver cannot know the appropriate child table name. It it due to the lack of real partitioning as in other database products that the client side has to be aware of the child table.

Your case is described in the manual below. As mentioned, the workaround is to use UPDATE trigger.

http://www.postgresql.org/docs/current/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.

Regards
Takayuki Tsunakawa