PostgreSQL and Sequence

6 posts, 1 answers
  1. Michael
    Michael avatar
    29 posts
    Member since:
    Nov 2010

    Posted 11 Oct 2012 Link to this post

    Hi,

    It's PostgreSQL 9.1
    I upgraded yestrday to latest OAORM version and now I have HUGH problems with sequences. I can't insert into any table throw OAORM. I noticed it on a new project on which I work. Now I check other projects and they also now don't work anymore! Error is always when inserting and always about sequence : 

    Telerik.OpenAccess.RT.sql.SQLException: ERROR: 42P01: relation "cloud.tabCloudDok_id_cldok_master_seq" does not exist
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.StatementImp.executeQuery(String sql)
       at OpenAccessRuntime.Relational.sql.PostgresSqlDriver.getAutoIncColumnValue(RelationalTable classTable, Connection con, Statement stat)
       at OpenAccessRuntime.Relational.sql.AutoIncRelationalKeyGenerator.generatePrimaryKeyPost(String className, RelationalTable classTable, Object[] data, Connection con, Statement stat)
       at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)
       at OpenAccessRuntime.Relational.RelationalStorageManager.persistPass1(PersistGraph graph)

    In version that I have previus (2011.2 or 2011.1) everything is worked OK!

    Please Help, it's urgent.


    p.s. Here is one table :
    CREATE TABLE cloud."tabCloudDok" (
      id_cldok_master INTEGER DEFAULT nextval('clouddok_id_master_seq'::text::regclass) NOT NULL,
      id_dok INTEGER NOT NULL,
      id_master INTEGER NOT NULL,
      sifvrste CHAR(3),
      vrsta VARCHAR(30),
      broj_cloud INTEGER,
      broj INTEGER NOT NULL,
      brojdoc VARCHAR(30) NOT NULL,
      datum DATE NOT NULL,
      valuta DATE DEFAULT 'now'::text::date,
      psifra VARCHAR(20),
      pnaziv VARCHAR(60),
      padresa VARCHAR(70),
      osifra VARCHAR(15),
      onaziv VARCHAR(60),
      oadresa VARCHAR(60),
      opis VARCHAR(60),
      napomena VARCHAR(200),
      inokurs NUMERIC(14,4),
      inomon VARCHAR(5),
      status SMALLINT DEFAULT 0,
      pecat_i TIMESTAMP WITHOUT TIME ZONE,
      storno BOOLEAN DEFAULT false,
      radnik VARCHAR(32),
      znak CHAR(1),
      isporuka VARCHAR(64) DEFAULT '-'::character varying,
      CONSTRAINT "CloudDok_pkey" PRIMARY KEY(id_cldok_master),
      CONSTRAINT "unique_idx_CloudDok" UNIQUE(id_dok, id_master),
    ) WITHOUT OIDS;
  2. Michael
    Michael avatar
    29 posts
    Member since:
    Nov 2010

    Posted 11 Oct 2012 Link to this post

    I think that problem is here :
    (BeckendError) ERROR: 42P01: relation \"cloud.tabCloudDok_id_cldok_master_seq\" does not exist"}

    if there is schema name than relation and identifier must be in quotation, for example : "cloud"."tabCloudDok_id_cldok_master_seq"

    also if you check table syntax, sequence name is : 'clouddok_id_master_seq' but OAORM capitalize some letters.
  3. DevCraft banner
  4. Answer
    Ralph Waldenmaier
    Admin
    Ralph Waldenmaier avatar
    202 posts

    Posted 11 Oct 2012 Link to this post

    Hi Michael,

    Are you working with a database first scenario? Usually when you specify a class to use a database server generated primary key value we assume that there is a sequence with the format TABLENAME_COLUMNNAME_seq. In your case this would be "tabCloudDok_id_cldok_master_seq". Since in your case, the sequence for this table has a different name, we are not  able to obtain the primary key value generated by the database.
    The reason why we are relying on this particular format is, that when you create a table with a primary key which has the column type serial, then postgres is generating a sequence with the name format as described above.
    The solution for your problem would be to specify the sequence name with the format from above.

    I hope this information is useful for you.
    Feel free to ask if you have any other question.

    Kind regards,
    Ralph
    the Telerik team
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
  5. Michael
    Michael avatar
    29 posts
    Member since:
    Nov 2010

    Posted 11 Oct 2012 Link to this post

    Hi Ralph,

    Yes it is database first scenarion. When OAORM generate rlinq file, table don't have marked "DatabaseServerCalculated" for Identity mechanism?!? I must manualy set this option!

    I changed sequence name how you suggested, delete class from designer and model explorer. Than call "UpdateFromDatabase", change on class that is DatabaseServerCalculated (in previus version I don't need to do that) and error was the same.

    Now I delete rlink file and recreate again, of course set manualy identity mechanism, and error now is something about incorect string input; must check that first.

    p.s. What about adentity mechanism? And when is changed that about sequence, because all my sequence is created by database and non working now? In previus version of OAORM also work.

     
  6. Michael
    Michael avatar
    29 posts
    Member since:
    Nov 2010

    Posted 11 Oct 2012 Link to this post

    OK, now it's working.
    I had to change all the sequences in all tables to make it work. That's strange. All sequence is created by database, I have not changed any. The only difference I can see is that all tables created in PostgreSQL 8.4 and all sequences are in lowercase, regardless if the column has mixed case! It seems that the database, starting from version 9.xcreate sequences in mixed case if column is also in mixed case; and before version 9.x create sequence in small cases regardless if the column has mixed case. I'm doing a lot in PostgreSQL and see this for first time!
    I wonder, how do you (OAORM) work with an PostgreSQL 8.4  in terms of sequence (small cases)? Have you tested with that version of the Database and latest version of OAORM?

    One more thing
    , is there somewhere documented (log changes) which was changed to OAORM works with PostgreSQL sequences, because as I said earlier, I had the day before yesterday OAORM version 2011.2 and everything worked as it should?
  7. Ralph Waldenmaier
    Admin
    Ralph Waldenmaier avatar
    202 posts

    Posted 12 Oct 2012 Link to this post

    Hello Michael,

    I am glad that you were able to fix the problem. Unfortunately it seems that we have missed this particular case in our tests. We will improve them accordingly in order to avoid such things in the future.
    Regarding your question about the log changes. You can find all the release notes of Telerik OpenAccess ORM here

    Please accept my apologies for the inconvenience caused.
    Do come back if you need further assistance.

    All the best,
    Ralph
    the Telerik team
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
Back to Top
DevCraft banner