0035653: update.database can fail updating src tables with a check constraint where more than one column participates

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0035653

Openbravo ERP

Y. DBSourceManager

public

2017-03-30 14:02

2017-09-26 15:25

Reporter

eduardo_Argal

Assigned To

alostale

Priority

high

Severity

major

Reproducibility

sometimes

Status

closed

Resolution

fixed

Platform

OS Version

Product Version

Target Version

Fixed in Version

Merge Request Status

Review Assigned To

AugustoMauch

OBNetwork customer

OBPS

Web browser

Modules

Core

Support ticket

47951

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

0035653: update.database can fail updating src tables with a check constraint where more than one column participates

Description

Having DB check constraint for a datasource table that is computed based on more than one column, update.database can fail when adding changes to that table.

This can occur if for a record more than one column participating in that contraint gets modified and even the whole changes fulfills the constraint, individual ones don't.

In the case described in steps to reproduce section, checking executed DB statements we can see:

UPDATE OBCQL_QUERY_COLUMN SET UPDATED = $1, UPDATEDBY = $2, HAS_LINK = $3 WHERE OBCQL_QUERY_COLUMN_ID 
= $4
parameters: $1 = '2017-09-12 12:38:30.269373', $2 = '0', $3 = 'Y', $4 = '28F920CD36E14AFBB2025802363E8B02'


UPDATE OBCQL_QUERY_COLUMN SET UPDATED = $1, UPDATEDBY = $2, AD_TAB_ID = $3 WHERE OBCQL_QUERY_COLUMN_ID 
= $4
parameters: $1 = '2017-09-12 12:38:30.280567', $2 = '0', $3 = '186', $4 = '28F920CD36E14AFBB2025802363E8B02'


UPDATE OBCQL_QUERY_COLUMN SET UPDATED = $1, UPDATEDBY = $2, CAN_BE_FILTERED = $3 WHERE OBCQL_QUERY_COLUMN_ID 
= $4
parameters: $1 = '2017-09-12 12:38:30.291775', $2 = '0', $3 = 'Y', $4 = '28F920CD36E14AFBB2025802363E8B02'

Note that changes in individual columns are applied independently, so it's possible to leave row in a temporary inconsistent state.

Steps To Reproduce

1. In a clean pi apply attached reproduce-issue-35653.diff patch
2. execute ant update.database
  -> ERROR:
     ...
     [java] 27738 INFO - Updating Application Dictionary data...
     [java] org.postgresql.util.PSQLException: ERROR: new row for relation "obcql_query_column" violates check constraint "obcql_column_whereclause_chk"
     [java] Detail: Failing row contains (28F920CD36E14AFBB2025802363E8B02, 0, 0, Y, 2015-08-03 12:05:45.870978, 0, 2017-09-12 12:50:33.690805, 0, CFDF8EE593F04CFE9709F5AD19A3A573, qty, Qty, null, 10, null, 20, 29, null, W, Y, 186, Y, null, null).

Proposed Solution

All changes affecting a single row should be applied at once. This would:

1. Solve described issue where database cannot be updated because the record is tried to be set to a temporary incorrect state.
2. Improve performance for some updates by reducing the number of statements required to update.

Additional Information

Tags

No tags attached.

Relationships

related to	defect	0036937	closed	alostale	update.database source data queries current time to DB for each change
related to	defect	0036938	closed	alostale	dbsm test: src changes not applied
causes	defect	0036984	closed	alostale	Upgrading from 2.50 to pi some unique constraints are violated