Project:
| View Revisions: Issue #30397 | [ All Revisions ] [ Back to Issue ] | ||
| Summary | 0030397: DB inconsistencies in PG9.5 (alpha1) | ||
| Revision | 2015-07-22 08:31 by alostale | ||
| Description | Exporting database in PostgreSQL 9.5 results in formal changes. See attached a diff with the list of all changes. There are 2 types of changes: 1. Changes in check constraints =============================== For example: - <check name="M_INOUT_CURRENCY_CHECK"><![CDATA[((FREIGHTAMT = 0) OR (FREIGHTAMT IS NULL)) OR (FREIGHT_CURRENCY_ID IS NOT NULL)]]></check> + <check name="M_INOUT_CURRENCY_CHECK"><![CDATA[(FREIGHTAMT = 0) OR (FREIGHTAMT IS NULL) OR (FREIGHT_CURRENCY_ID IS NOT NULL)]]></check> They occur for check constraints like: "expr1 OR expr2 OR expr3" Previous to 9.5 they where internally stored as "(((expr1) OR (expr2)) OR (expr2))" but in 9.5 they are "((expr1) OR (expr2) OR (expr3))" This can be tested with: create table test (a text, b text, c text); ALTER TABLE test ADD CONSTRAINT test_chk CHECK (a is not null or b is not null or c is not null); select version(), consrc, pg_get_constraintdef(oid, true) from pg_constraint where conname ='test_chk'; version | consrc | pg_get_constraintdef ----------+-----------------------------------------------------------+--------------------------------------------------------- 8.4.22 | (((a IS NOT NULL) OR (b IS NOT NULL)) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) 9.2.11 | (((a IS NOT NULL) OR (b IS NOT NULL)) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) 9.3.7 | (((a IS NOT NULL) OR (b IS NOT NULL)) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) 9.5alpha1 | ((a IS NOT NULL) OR (b IS NOT NULL) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) 2. Changes in views =================== Some views are changed in two ways 2.1 Some numeric values enclosed between brackets ------------------------------------------------- - <view name="M_RM_SHIPMENT_PICK_EDIT"><![CDATA[SELECT ... CASE WHEN sd.m_locator_id IS NULL THEN (-1) * ol.qtyordered ...]]></view> + <view name="M_RM_SHIPMENT_PICK_EDIT"><![CDATA[SELECT ... CASE WHEN sd.m_locator_id IS NULL THEN '-1' * ol.qtyordered ...]]></view> Internally the query was in previous versions: ... WHEN sd.m_locator_id IS NULL THEN (-1)::numeric * ol.qtyordered ... and in 9.5 is: ... WHEN sd.m_locator_id IS NULL THEN '-1'::integer::numeric * ol.qtyordered ... 2.2 Some ORDER BY fields enclosed between brackets -------------------------------------------------- - <view name="FIN_DOUBTFUL_DEBT_V"><![CDATA[SELECT ... GROUP BY ... trunc(now()) - trunc(ps.duedate) ... ]]></view> + <view name="FIN_DOUBTFUL_DEBT_V"><![CDATA[SELECT ... GROUP BY ... (trunc(now()) - trunc(ps.duedate)) ... ]]></view> It is also caused by how the view is internally translated and stored. |
||
| Revision | 2015-07-22 08:30 by alostale | ||
| Description | Exporting database in PostgreSQL 9.5 results in formal changes. See attached a diff with the list of all changes. There are 2 types of changes: 1. Changes in check constraints =============================== For example: - <check name="M_INOUT_CURRENCY_CHECK"><![CDATA[((FREIGHTAMT = 0) OR (FREIGHTAMT IS NULL)) OR (FREIGHT_CURRENCY_ID IS NOT NULL)]]></check> + <check name="M_INOUT_CURRENCY_CHECK"><![CDATA[(FREIGHTAMT = 0) OR (FREIGHTAMT IS NULL) OR (FREIGHT_CURRENCY_ID IS NOT NULL)]]></check> They occur for check constraints like: "expr1 OR expr2 OR expr3" Previous to 9.5 they where internally stored as "(((expr1) OR (expr2)) OR (expr2))" but in 9.5 they are "((expr1) OR (expr2) OR (expr3))" This can be tested with: create table test (a text, b text, c text); ALTER TABLE test ADD CONSTRAINT test_chk CHECK (a is not null or b is not null or c is not null); select version(), consrc, pg_get_constraintdef(oid, true) from pg_constraint where conname ='test_chk'; version | consrc | pg_get_constraintdef ----------+-----------------------------------------------------------+--------------------------------------------------------- 8.4.22 | (((a IS NOT NULL) OR (b IS NOT NULL)) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) 9.2.11 | (((a IS NOT NULL) OR (b IS NOT NULL)) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) 9.3.7 | (((a IS NOT NULL) OR (b IS NOT NULL)) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) 9.5alpha1 | ((a IS NOT NULL) OR (b IS NOT NULL) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) 2. Changes in views =================== Some views are changed in two ways 2.1 Some numeric values enclosed between brackets ------------------------------------------------- - <view name="M_RM_SHIPMENT_PICK_EDIT"><![CDATA[SELECT ... CASE WHEN sd.m_locator_id IS NULL THEN (-1) * ol.qtyordered ...]]></view> + <view name="M_RM_SHIPMENT_PICK_EDIT"><![CDATA[SELECT ... CASE WHEN sd.m_locator_id IS NULL THEN '-1' * ol.qtyordered ...]]></view> Internally the query was in previous versions: ... WHEN sd.m_locator_id IS NULL THEN (-1)::numeric * ol.qtyordered ... and in 9.5 is: ... WHEN sd.m_locator_id IS NULL THEN '-1'::integer::numeric * ol.qtyordered ... 2.2 Some ORDER BY fields enclosed between brackets -------------------------------------------------- - <view name="FIN_DOUBTFUL_DEBT_V"><![CDATA[SELECT ... GROUP BY ... trunc(now()) - trunc(ps.duedate) ... ]]></view> + <view name="FIN_DOUBTFUL_DEBT_V"><![CDATA[SELECT ... GROUP BY ... (trunc(now()) - trunc(ps.duedate)) ... ]]></view> |
||
| Revision | 2015-07-22 08:26 by alostale | ||
| Description | Exporting database in PostgreSQL 9.5 results in formal changes. See attached a diff with the list of all changes. There are 2 types of changes: 1. Changes in check constraints =============================== For example: - <check name="M_INOUT_CURRENCY_CHECK"><![CDATA[((FREIGHTAMT = 0) OR (FREIGHTAMT IS NULL)) OR (FREIGHT_CURRENCY_ID IS NOT NULL)]]></check> + <check name="M_INOUT_CURRENCY_CHECK"><![CDATA[(FREIGHTAMT = 0) OR (FREIGHTAMT IS NULL) OR (FREIGHT_CURRENCY_ID IS NOT NULL)]]></check> They occur for check constraints like: "expr1 OR expr2 OR expr3" Previous to 9.5 they where internally stored as "(((expr1) OR (expr2)) OR (expr2))" but in 9.5 they are "((expr1) OR (expr2) OR (expr3))" This can be tested with: create table test (a text, b text, c text); ALTER TABLE test ADD CONSTRAINT test_chk CHECK (a is not null or b is not null or c is not null); select version(), consrc, pg_get_constraintdef(oid, true) from pg_constraint where conname ='test_chk'; version | consrc | pg_get_constraintdef ----------+-----------------------------------------------------------+--------------------------------------------------------- 8.4.22 | (((a IS NOT NULL) OR (b IS NOT NULL)) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) 9.2.11 | (((a IS NOT NULL) OR (b IS NOT NULL)) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) 9.3.7 | (((a IS NOT NULL) OR (b IS NOT NULL)) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) 9.5alpha1 | ((a IS NOT NULL) OR (b IS NOT NULL) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) 2. Changes in views =================== Some views are changed in two ways 2.1 Some numeric values enclosed between brackets ------------------------------------------------- - <view name="M_RM_SHIPMENT_PICK_EDIT"><![CDATA[SELECT ... CASE WHEN sd.m_locator_id IS NULL THEN (-1) * ol.qtyordered ...]]></view> + <view name="M_RM_SHIPMENT_PICK_EDIT"><![CDATA[SELECT ... CASE WHEN sd.m_locator_id IS NULL THEN '-1' * ol.qtyordered ...]]></view> Internally the query was in previous versions: ... WHEN sd.m_locator_id IS NULL THEN (-1)::numeric * ol.qtyordered ... and in 9.5 is: ... WHEN sd.m_locator_id IS NULL THEN '-1'::integer::numeric * ol.qtyordered ... 2.2 Some ORDER BY fields enclosed between brackets -------------------------------------------------- |
||
| Revision | 2015-07-21 13:28 by alostale | ||
| Description | Exporting database in PostgreSQL 9.5 results in formal changes. See attached a diff with the list of all changes. There are 2 types of changes: 1. Changes in check constraints =============================== For example: - <check name="M_INOUT_CURRENCY_CHECK"><![CDATA[((FREIGHTAMT = 0) OR (FREIGHTAMT IS NULL)) OR (FREIGHT_CURRENCY_ID IS NOT NULL)]]></check> + <check name="M_INOUT_CURRENCY_CHECK"><![CDATA[(FREIGHTAMT = 0) OR (FREIGHTAMT IS NULL) OR (FREIGHT_CURRENCY_ID IS NOT NULL)]]></check> They occur for check constraints like: "expr1 OR expr2 OR expr3" Previous to 9.5 they where internally stored as "(((expr1) OR (expr2)) OR (expr2))" but in 9.5 they are "((expr1) OR (expr2) OR (expr3))" This can be tested with: create table test (a text, b text, c text); ALTER TABLE test ADD CONSTRAINT test_chk CHECK (a is not null or b is not null or c is not null); select version(), consrc, pg_get_constraintdef(oid, true) from pg_constraint where conname ='test_chk'; version | consrc | pg_get_constraintdef ----------+-----------------------------------------------------------+--------------------------------------------------------- 8.4.22 | (((a IS NOT NULL) OR (b IS NOT NULL)) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) 9.2.11 | (((a IS NOT NULL) OR (b IS NOT NULL)) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) 9.3.7 | (((a IS NOT NULL) OR (b IS NOT NULL)) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) 9.5alpha1 | ((a IS NOT NULL) OR (b IS NOT NULL) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) 2. Changes in views =================== Some views are changed - <view name="M_RM_SHIPMENT_PICK_EDIT"><![CDATA[SELECT ... CASE WHEN sd.m_locator_id IS NULL THEN (-1) * ol.qtyordered ...]]></view> + <view name="M_RM_SHIPMENT_PICK_EDIT"><![CDATA[SELECT ... CASE WHEN sd.m_locator_id IS NULL THEN '-1' * ol.qtyordered ...]]></view> Internally the query was in previous versions: ... WHEN sd.m_locator_id IS NULL THEN (-1)::numeric * ol.qtyordered ... and in 9.5 is: ... WHEN sd.m_locator_id IS NULL THEN '-1'::integer::numeric * ol.qtyordered ... |
||
| Revision | 2015-07-21 13:23 by alostale | ||
| Description | Exporting database in PostgreSQL 9.5 results in formal changes. See attached a diff with the list of all changes. There are 2 types of changes: 1. Changes in check constraints =============================== For example: - <check name="M_INOUT_CURRENCY_CHECK"><![CDATA[((FREIGHTAMT = 0) OR (FREIGHTAMT IS NULL)) OR (FREIGHT_CURRENCY_ID IS NOT NULL)]]></check> + <check name="M_INOUT_CURRENCY_CHECK"><![CDATA[(FREIGHTAMT = 0) OR (FREIGHTAMT IS NULL) OR (FREIGHT_CURRENCY_ID IS NOT NULL)]]></check> They occur for check constraints like: "expr1 OR expr2 OR expr3" Previous to 9.5 they where internally stored as "(((expr1) OR (expr2)) OR (expr2))" but in 9.5 they are "((expr1) OR (expr2) OR (expr3))" This can be tested with: create table test (a text, b text, c text); ALTER TABLE test ADD CONSTRAINT test_chk CHECK (a is not null or b is not null or c is not null); select version(), consrc, pg_get_constraintdef(oid, true) from pg_constraint where conname ='test_chk'; version | consrc | pg_get_constraintdef ----------+-----------------------------------------------------------+--------------------------------------------------------- 8.4.22 | (((a IS NOT NULL) OR (b IS NOT NULL)) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) 9.2.11 | (((a IS NOT NULL) OR (b IS NOT NULL)) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) 9.3.7 | (((a IS NOT NULL) OR (b IS NOT NULL)) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) 9.5alpha1 | ((a IS NOT NULL) OR (b IS NOT NULL) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL) |
||
| Revision | 2015-07-21 13:14 by alostale | ||
| Description | Exporting database in PostgreSQL 9.5 results in formal changes. See attached a diff with the list of all changes. There are 2 types of changes: 1. Changes in check constraints =============================== For example: - <check name="M_INOUT_CURRENCY_CHECK"><![CDATA[((FREIGHTAMT = 0) OR (FREIGHTAMT IS NULL)) OR (FREIGHT_CURRENCY_ID IS NOT NULL)]]></check> + <check name="M_INOUT_CURRENCY_CHECK"><![CDATA[(FREIGHTAMT = 0) OR (FREIGHTAMT IS NULL) OR (FREIGHT_CURRENCY_ID IS NOT NULL)]]></check> They occur for check constraints like: "expr1 OR expr2 OR expr3" Previous to 9.5 they where internally stored as "(((expr1) OR (expr2)) OR (expr2))" but in 9.5 they are "((expr1) OR (expr2) OR (expr3))" This can be tested with: create table test (a text, b text, c text); ALTER TABLE test ADD CONSTRAINT test_chk CHECK (a is not null or b is not null or c is not null); select version(), consrc, pg_get_constraintdef(oid, true) from pg_constraint where conname ='test_chk'; |
||
| Revision | 2015-07-21 13:08 by alostale | ||
| Description | Exporting database in PostgreSQL 9.5 results in formal changes. | ||
| Copyright © 2000 - 2009 MantisBT Group |
 |