8 Replies Latest reply on Jan 9, 2004 6:53 AM by Bas Velthuizen

    PostgreSQL support

    mrojas Newbie

      hi, i'm trying to use nukes with postgresql 7.3, but i'm getting some errors.

      i've started with the core, no other modules loaded. the first error is generated when i try to insert a new document in the content manager:

      13:49:19,785 ERROR [FileEJB] Could not create entity
      java.sql.SQLException: ERROR: Cannot insert a duplicate key into unique index nuke_file_pkey

      at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
      at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)
      at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
      at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
      at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:197)
      at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:308)
      ....

      i'v modified the nukes-ds.xml, jbosscmp-jdbc-entity-commands.xml and buidl/local.properties to use postgressql instead of hsqldb.

      thanks in advance for your responses, and best regards.

        • 1. Re: PostgreSQL support
          samus Newbie

          Since there isn't any ddl files for postgres, I assume you made the tables yourself. Did you remember to make the pn_id file a serial column so that you could get the auto incrment functionality? If you did then it sounds like jboss/nukes is not getting the inserted primary key back from postgres. You may do some searches around the other forums for identity columns and such.

          • 2. Re: PostgreSQL support
            mrojas Newbie

            yes i've created my oun scripts based on the hsqldb scripts.

            now, searching a little on the forums i concluded that my error was that in the local.properties file i has declared the nukes.mergedir as postgresql and the directory in nukes/src/resources was named pgsql, so i renamed the directory to postgresql, and now the problem is that when whe i try to add a new document, the exception says:

            TransactionRolledbackLocalException in method: public abstract org.jboss.nukes.core.ejb.FileEJBLocal org.jboss.nukes.core.ejb.FileEJBLocalHome.create(java.lang.String,int,java.lang.String,byte[]) throws javax.ejb.CreateException, causedBy:
            java.sql.SQLException: ERROR: The "nuke_file_pn_id_seq" relation doesn't
            exist
            at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
            at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)
            at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
            at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
            at org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:153)
            at org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:141)
            at org.jboss.resource.adapter.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:213)
            at org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCPostgreSQLCreateCommand.executeInsert(JDBCPostgreSQLCreateCommand.java:67)

            and looking at the description of the postgresql schema, the sequence is named nuke_file_pn_id, without the _seq. looking at the $JBOSS_HOME/server/all/conf/standardjbosscmp-jdbc.xml and $JBOSS_HOME/server/all/conf/standardjbosscmp-jdbc.xml i found a entity command named postgresql-fetch-seq wich has an attribute sequence wich says to uncoment if we need to change the name of the sequence, so i did it on both files, but keep geting the same error.

            any ideas?

            thanks.

            • 3. Re: PostgreSQL support
              mrojas Newbie

              well, finally i've commed up with a simple solution: creating the explicit sequences and assigning them to the rigth field, plus adding a "_seq" suffix to the sequence name.

              so, i'm going to migrate all the modules' scripts this weekend and hope, if anyone is interested, to get them ready by monday.

              by the way, there is any chance to add those scripts to the nukes distribution? maybe there are another postgres users here or new ones can start using nukes now :)

              • 4. Re: PostgreSQL support
                jukyd Newbie

                We're thinking of using PostgreSQL and I would be very interested in seeing your ddl files. Any chance you could send them my way? Or are there plans to include them in the snapshot soon?

                Thanks,

                D

                • 5. Re: PostgreSQL support
                  Viet Master

                  Give me your files in the nukes format :

                  i.e the postgres directory like the mysql and hsqldb directory and I will put them in the codebase.

                  julien

                  • 6. Re: PostgreSQL support
                    mrojas Newbie

                    ok, sorry for the delay, i was quite busy in my work, but this weekend i'll finish the scripts and this monday i'll be sending you the tested files.

                    thanks.

                    • 7. Re: PostgreSQL support
                      mrojas Newbie

                      once again sorry for taking to much time, but i was getting some weird errors with nukes on my home machine, now i've tested in my workplace pc and it works ok, so tomorrow i'll be puting finally the scripts.

                      thanks a lot, and best regards.

                      • 8. Re: PostgreSQL support
                        Bas Velthuizen Newbie

                        I've got nukes working under postgreSQL.
                        if anyone is interrested, I can post a dump of the database. (with or without the tables of the cvs modules, but most of these have postgreSQL support already)