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

Hi Takayuki,

I have the following constraints for the table in question w.r.t data partitioning.

dm_repo51_docbase=> \d+ testpart25_s

                           Table "repo51.testpart25_s"

    Column    |     Type      | Modifiers | Storage  | Stats target | Descriptio

n

--------------+---------------+-----------+----------+--------------+-----------

--

r_object_id  | character(16) |           | extended |              |

c1           | integer       |           | plain    |              |

i_is_replica | smallint      |           | plain    |              |

i_vstamp     | integer       |           | plain    |              |

i_partition  | integer       |           | plain    |              |

Indexes:

    "d_1f0013ea80000502" UNIQUE, btree (r_object_id, i_partition)

Triggers:

    tr_fn_trigg_testpart25_s BEFORE INSERT ON testpart25_s FOR EACH ROW EXECUTE

PROCEDURE trigg_testpart25_s()

Child tables: p1_testpart25_s,

              p2_testpart25_s,

              p3_testpart25_s,

              p4_testpart25_s

dm_repo51_docbase=> \d+ p1_testpart25_s

                          Table "repo51.p1_testpart25_s"

    Column    |     Type      | Modifiers | Storage  | Stats target | Descriptio

n

--------------+---------------+-----------+----------+--------------+-----------

--

r_object_id  | character(16) |           | extended |              |

c1           | integer       |           | plain    |              |

i_is_replica | smallint      |           | plain    |              |

i_vstamp     | integer       |           | plain    |              |

i_partition  | integer       |           | plain    |              |

Check constraints:

    "chk_2" CHECK (i_partition < 2)

Inherits: testpart25_s

dm_repo51_docbase=> \d+ p4_testpart25_s

                          Table "repo51.p4_testpart25_s"

    Column    |     Type      | Modifiers | Storage  | Stats target | Descriptio

n

--------------+---------------+-----------+----------+--------------+-----------

--

r_object_id  | character(16) |           | extended |              |

c1           | integer       |           | plain    |              |

i_is_replica | smallint      |           | plain    |              |

i_vstamp     | integer       |           | plain    |              |

i_partition  | integer       |           | plain    |              |

Check constraints:

    "chk_10" CHECK (i_partition >= 8 AND i_partition < 10)

Inherits: testpart25_s

I enabled logs for the specific use-case for UPDATE statement and below is the snippet of the error (in postgresql.conf):

++++++++++++

2016-06-06 11:57:58 IST LOG:  statement: RELEASE _EXEC_SVP_0000000001569810

2016-06-06 11:57:58 IST LOG:  statement: SAVEPOINT _EXEC_SVP_0000000001569810

2016-06-06 11:57:58 IST LOG:  execute <unnamed>:  UPDATE testpart25_s  SET i_partition=   9,i_vstamp=   1 WHERE (r_object_id=$1           AND i_vstamp=$2           )

2016-06-06 11:57:58 IST DETAIL:  parameters: $1 = '000013ea80000500', $2 = '0'

2016-06-06 11:57:58 IST ERROR:  new row for relation "p1_testpart25_s" violates check constraint "chk_2"

2016-06-06 11:57:58 IST DETAIL:  Failing row contains (000013ea80000500, 56, 0, 1, 9).

2016-06-06 11:57:58 IST STATEMENT:   UPDATE testpart25_s  SET i_partition=   9,i_vstamp=   1 WHERE (r_object_id=$1           AND i_vstamp=$2           )

2016-06-06 11:57:58 IST LOG:  statement: ROLLBACK to _EXEC_SVP_0000000001569810

2016-06-06 11:57:58 IST LOG:  statement: RELEASE _EXEC_SVP_0000000001569810

2016-06-06 11:57:58 IST LOG:  statement: ROLLBACK

++++++++++++

The partitioning key (check constraint) defined is on “i_partition” column.

As you can see, update statement is fired on first partition  (p1_testpart25_s) rather than p4_testpart25_s table (as i_partition value of 9 should move the data to p4_testpart25_s table).

I expected automatic row movement to happen to p4_testpart25_s table from PostgreSQL itself which isn’t the case.

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.

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).

Let me know what you think.

Thanks,

Sekhar

From: Venkatesan, Sekhar [mailto:sekhar.venkatesan@emc.com]
 When partition is enabled in my application and I change the partitioning key in the table to save the new data into another partition, I get error like this:

ERROR: new row for relation "testpart_s" violates check constraint " p1_testpart12_s"

I enabled constraint_exclusion in postgresql.conf.

I followed the document you shared to enable row movement across partitions by writing triggers on partition tables. It works up to a point but not confident it will work for all use-cases.

Did this error begin to happen with the unreleased driver, or already happen with the officially released driver?

SQLSetPos(SQL_UPDATE) issues an UPDATE statement.  Could you identify the statement causing the error by logging the statements, i.e. adding log_statement = ‘all’ in postgresql.conf?  Does the statement seem to be wroing when you consider the check constraint name, i.e. is the target table correct?

Are you sure your trigger specifies the appropriate table for the new key value?

Regards

Takayuki Tsunakawa