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

Thanks Takayuki and Inoue for the update.
We want to support data partitioning in our application for PostgreSQL.

In this case, I have partitioned all the tables, the application uses in the database and have written triggers to move the data from the original tables to partitioned tables so that triggers manage the data migration.

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?

Can you please point  me to any samples if any ?

Thanks for all the help,

Sekhar

From: Venkatesan, Sekhar [mailto:sekhar.venkatesan@emc.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