auto-increment problem on PostgreSQL

[daveespo]

I think I read every last post, every last set of instructions, every snippet of anything I could find in the forums as well as some external articles published on how to get auto-increment (auto generated primary keys to work) but none of them seemed to work with my configuration:

JBOSS 4.0
Postgres 7.3.x

JbossCMP would not generate a new key so the insert to the database would fail with a not null constraint violation ...

I tried all of the instructions about removing the cmp-field for my primary key and using prim-key-class of java.lang.Object and changing my bean class' ejbCreate to return an Object ... still, no luck ... but I stumbled onto this workaround by accident and hope that it will help some other people ... It basically comes down to using the 'pk-sql' EntityCommand instead of the 'postgresql-fetch-seq' EntityCommand ... Here is my example:


ejb-jar.xml

<ejb-jar>
 <enterprise-beans>
 <entity>
 <ejb-name>Patron</ejb-name>
 <home>xxx.PatronHome</home>
 <remote>xxx.Patron</remote>
 <ejb-class>xxx.PatronBean</ejb-class>
 <persistence-type>Container</persistence-type>
 <prim-key-class>java.lang.Integer</prim-key-class>
 <reentrant>False</reentrant>
 <cmp-version>2.x</cmp-version>
 <abstract-schema-name>patron</abstract-schema-name>

 <cmp-field><field-name>patron_id</field-name></cmp-field>
 <cmp-field><field-name>organization_id</field-name></cmp-field>
 <cmp-field><field-name>patron_status</field-name></cmp-field>

 <primkey-field>patron_id</primkey-field>

[snip]
 </entity>

 </enterprise-beans>

</ejb-jar>

jbosscmp-jdbc.xml

<jbosscmp-jdbc>
 <defaults>
 <datasource>@jndi_datasource@</datasource>
 <datasource-mapping>PostgreSQL</datasource-mapping>

 <create-table>false</create-table>
 <remove-table>false</remove-table>

 <entity-command name="pk-sql">
 <attribute name="pk-sql">SELECT NEXTVAL('SEQ_PATRON') AS next_id</attribute>
 </entity-command>
 </defaults>


 <enterprise-beans>
 <entity>
 <ejb-name>Patron</ejb-name>
 <cmp-field>
 <field-name>patron_id</field-name>
 <auto-increment/>
 </cmp-field>
 </entity>
 </enterprise-beans>
</jbosscmp-jdbc>

I did a cursory review of the JDBCPkSqlCreateCommand and JDBCPostgreSQLCreateCommand classes and it appears as though they implement different methods ... so I can't explain why my new value from the sequence is being inserted properly into the SQL that goes to the DB using the pk-sql entity command and why it isn't working for the postgresql-fetch-seq command ...

So anyway, hopefully this workaround will save other people hours of searching too ...