0 Replies Latest reply on May 20, 2013 10:23 PM by hanynowsky

    Scaffolding from PostgreSQL has IDENTITY issues!

    hanynowsky

      I scaffolded from a PostgreSQL database and got issues when using the UI to populate data. So to make sure, that PostgreSQL support in Forge is faulty, I recreated the same Database in MySQL and scaffolded again and this time everything worked like a charm.

       

      The ISSUE is mainly related to generation of Identifiers:

       

      I noticed that when scaffolding from MySQL, Forge (hbm2java plugin) generates Entities with IDENTITY generation strategy annotation for ID fields; yet it does not when using PostgreSQL.

      Both Databases have Tables with IDs being generated automatically (AUTO_INCREMENT for MYSQL and serial - INTEGER with sequence).

       

      Postgre Scaffold:

       

      - While the entities generated do not have the IDENTITY Strategy annotaion on their IDs (@generatedValue(strategy = IDENTITY)), when you want to create an object using the UI, you get a hibernate exception : Violation of primary constraint ( Duplicate value 0 already exists), and this because hibernate does the same thing it does with Mysql : use the value of 0 for the ID.

      In MySQL, if you do : insert into table SOMETABLE(id,label,rank) values (0, 'TOTO', 20); even though you have an existing record with an id of value 0, MySQL will auto_increment the new id; but if you do the same thing for PostgreSQL, you will get a duplicate key value (0) error. Instead for PostgreSQl you should use the auto_incrementation key word : insert into table SOMETABLE(id,label,rank) values (DEFAULT, 'TOTO', 20);

       

      So I modified the entities by adding the annotation @GeneratedValue on Id fields. Built and redeployed to test. And here comes the suprise!:

      Using the UI, if you want create an Object owned by a parent Object through a foreign key, you get hibernate exception complaing about an UNSAVED TRANSIENT OBJECT (THE PARENT OBJECT). SO basically, it's like instead of just attaching the parent object to the newly created child object, the app tries to create a new instance of the parent object, yet this parent object already exists on database. Did it lose its reference because of the @GeneratedValue and got detached? I guess so!

      I added the CASCADE.ALL attribute on the relationship but in vain.
      I tried with all kind of Identity startegies to test ( IDENTITY, TABLE, AUTO, SEQUENCE) but in vain.
      I also removed the annotation from all entities, and created triggers in the database instead. A Trigger for every table that calls the sequence ID generation instead of 0 value, BEFORE INSERT ON EACH ROW. That allowed me to have correct auto-incrementation on the databse side even though I use 0 as the id value. But unfortunately, this makes newly created objects through the UI lose their values and receive NULL value instead.

       

      If you think I am just being misled by some mistake, I can supply you with the corresponding Postgre Database.

       

      Using PostgreSQL 9.1 and Latest FORGE.