SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.1] bug in Schema Compare working with PostgreSQL

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.1] bug in Schema Compare working with PostgreSQL
Author Message
limpus



Joined: 10 Mar 2017
Posts: 1
Country: Germany

Post [SA 9.1] bug in Schema Compare working with PostgreSQL Reply with quote
Schema Compare displays Check Contraints always as differing between two PostgreSQL servers (both running 9.5.5) though there are definitely no differences in those Constraints when I compare the output of pg_dump.

I suspect that SQL Assistant might not be prepared that PostgreSQL allows the same name for a Constraint to appear in different tables with different Check code in their body. Schema Compare mixes up the different code inside the Constraints having the same name, and therefore always displays differences.

In the following examples, the same Check Constraint names appear in both tables: chk_abgr_range, chk_idps_range, and chk_iduz_range. PostgreSQL is perfectly fine with that.

Table1:
Code:

CREATE TABLE datbasis (
    idmonat     SMALLINT NOT NULL,
    idtz1       SMALLINT NOT NULL,
    idtz2       SMALLINT NOT NULL,
    iduz        INTEGER NOT NULL,
    idps        SMALLINT NOT NULL,
    idvweg      SMALLINT NOT NULL,
    abgr        SMALLINT NOT NULL,
    mdiff       SMALLINT NOT NULL,
    idtz1v      SMALLINT NOT NULL,
    idtz2v      SMALLINT NOT NULL,
    idpsv       SMALLINT NOT NULL,
    iduzv       INTEGER NOT NULL,
    errnr       SMALLINT NOT NULL,
    status      SMALLINT NOT NULL,
    regel       SMALLINT NOT NULL,
    tarif       SMALLINT NOT NULL,
    CONSTRAINT chk_abgr_range CHECK(abgr >= 0 AND abgr <= 99),
    CONSTRAINT chk_errnr_range CHECK(errnr >= '-99'::INTEGER AND errnr <= 99),
    CONSTRAINT chk_idmonat_range CHECK(idmonat >= 0 AND idmonat <= 999),
    CONSTRAINT chk_idps_range CHECK(idps >= '-9999'::INTEGER AND idps <= 9999),
    CONSTRAINT chk_idpsv_range CHECK(idpsv >= 0 AND idpsv <= 9999),
    CONSTRAINT chk_idtz1_range CHECK(idtz1 >= '-9999'::INTEGER AND idtz1 <= 9999),
    CONSTRAINT chk_idtz1v_range CHECK(idtz1v >= '-9999'::INTEGER AND idtz1v <= 9999),
    CONSTRAINT chk_idtz2_range CHECK(idtz2 >= '-9999'::INTEGER AND idtz2 <= 9999),
    CONSTRAINT chk_idtz2v_range CHECK(idtz2v >= '-9999'::INTEGER AND idtz2v <= 9999),
    CONSTRAINT chk_iduz_range CHECK(iduz >= '-999999999'::INTEGER AND iduz <= 999999999),
    CONSTRAINT chk_iduzv_range CHECK(iduzv >= 0 AND iduzv <= 999999999),
    CONSTRAINT chk_idvweg_range CHECK(idvweg >= 0 AND idvweg <= 99),
    CONSTRAINT chk_mdiff_range CHECK(mdiff >= 0 AND mdiff <= 14),
    CONSTRAINT chk_regel_range CHECK(regel >= '-999'::INTEGER AND regel <= 999),
    CONSTRAINT chk_status_range CHECK(status >= '-999'::INTEGER AND status <= 999),
    CONSTRAINT chk_tarif_range CHECK(tarif >= '-9'::INTEGER AND tarif <= 9)
) ;


Table2:
Code:

CREATE TABLE datroh (
    idmonat      SMALLINT NOT NULL,
    idtz1        SMALLINT NOT NULL,
    idtz2        SMALLINT NOT NULL,
    iduz         INTEGER NOT NULL,
    idprod       SMALLINT NOT NULL,
    idps         SMALLINT NOT NULL,
    verkdiff     SMALLINT NOT NULL,
    geltdiff     SMALLINT NOT NULL,
    idvweg       SMALLINT NOT NULL,
    status       SMALLINT NOT NULL,
    abgr         SMALLINT DEFAULT 0 NOT NULL,
    tarif        SMALLINT DEFAULT 0 NOT NULL,
    CONSTRAINT chk_abgr_range CHECK(abgr >= 0 AND abgr <= 12),
    CONSTRAINT chk_geltdiff_range CHECK(geltdiff >= '-99'::INTEGER AND geltdiff <= 99),
    CONSTRAINT chk_idmonat_range CHECK(idmonat >= 0 AND idmonat <= 999),
    CONSTRAINT chk_idprod_range CHECK(idprod >= 0 AND idprod <= 9999),
    CONSTRAINT chk_idps_range CHECK(idps >= 0 AND idps <= 9999),
    CONSTRAINT chk_idtz1_range CHECK(idtz1 >= '-9999'::INTEGER AND idtz1 <= 9999),
    CONSTRAINT chk_idtz2_range CHECK(idtz2 >= '-9999'::INTEGER AND idtz2 <= 9999),
    CONSTRAINT chk_iduz_range CHECK(iduz >= 0 AND iduz <= 999999999),
    CONSTRAINT chk_idvweg_range CHECK(idvweg >= '-99'::INTEGER AND idvweg <= 99),
    CONSTRAINT chk_status_range CHECK(status >= 0 AND status <= 9999),
    CONSTRAINT chk_tarif_range CHECK(tarif >= '-9'::INTEGER AND tarif <= 9),
    CONSTRAINT chk_verkdiff_range CHECK(verkdiff >= '-99'::INTEGER AND verkdiff <= 99)
) ;


Please fix Schema Compare to handle PostgreSQL constraints correctly.
Fri Mar 10, 2017 11:18 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6509

Post Reply with quote
Thank you very much for reporting this. I have logged this issue in our bug tracking system.
Sat Mar 11, 2017 5:46 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.