PostgreSQL support

[mrojas]

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.

[samus]

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.

[mrojas]

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.

[mrojas]

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 :)

[jukyd]

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

[julien1]

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

[mrojas]

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.

[mrojas]

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.

[velthuiz]

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)