12 Replies Latest reply on Jun 20, 2005 7:31 AM by tbech

    postgres and foreign key relation problems

    jae Master

      i'm having a problem getting a pure cmp (having jboss create the tables, instead of using a ddl) solution working w/ postgres.

      i am unable to insert a row into a table that has a foreign key constraint with another table. the entity does exist, but the database keeps coming back and telling me that i'm trying to insert a null value into a non-null column.

      does anyone have any ideas on this? i realize this is somewhat sparse information, so pls let me know what further details i could provide.

      here is a small snippet of the exception trace that is printed:

      2004-05-18 10:22:36,109 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.TopicEJB#findByPrimaryKey]
       Executing SQL: SELECT topic_id FROM nuke_news_topics WHERE topic_id=?
      2004-05-18 10:22:36,109 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCPostgreSQLCreateCommand.StoryEJB]
      Executing SQL: INSERT INTO nuke_news_stories (allow_comments, state, admin_notes, summary, ext_text, title, views, create_date
      , ip_addr, author_id, topic_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      
      2004-05-18 10:22:36,109 ERROR [org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCPostgreSQLCreateCommand.StoryEJB]
       Could not create entity
      org.postgresql.util.PSQLException: ERROR: null value in column "topic_id"
      violates not-null constraint
       at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
       at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152)
       at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100)
       at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
       at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:517)
       at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
       at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:273)
       at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:324)
       at org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCPostgreSQLCreateCommand.executeInsert(JDBCPostgreSQLCreateCommand.java:59)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractCreateCommand.performInsert(JDBCAbstractCreateCommand.java:287)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractCreateCommand.execute(JDBCAbstractCreateCommand.java:138)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.createEntity(JDBCStoreManager.java:554)
       at org.jboss.ejb.plugins.CMPPersistenceManager.createEntity(CMPPersistenceManager.java:208)
       at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.createEntity(CachedConnectionInterceptor.java:269)
       at org.jboss.ejb.EntityContainer.createLocalHome(EntityContainer.java:581)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:324)
       at org.jboss.ejb.EntityContainer$ContainerInterceptor.invokeHome(EntityContainer.java:1043)
       at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:88)
       at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invokeHome(EntitySynchronizationInterceptor.java:197)
       at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invokeHome(CachedConnectionInterceptor.java:214)
       at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:88)
       at org.jboss.ejb.plugins.EntityInstanceInterceptor.invokeHome(EntityInstanceInterceptor.java:89)
       at org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome(EntityLockInterceptor.java:61)
       at org.jboss.ejb.plugins.EntityCreationInterceptor.invokeHome(EntityCreationInterceptor.java:28)
       at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:88)
       at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:267)
       at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxInterceptorCMT.java:98)
       at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:92)
       at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:120)
       at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invokeHome(ProxyFactoryFinderInterceptor.java:93)
       at org.jboss.ejb.EntityContainer.internalInvokeHome(EntityContainer.java:483)
       at org.jboss.ejb.Container.invoke(Container.java:720)
       at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invokeHome(BaseLocalProxyFactory.java:293)
       at org.jboss.ejb.plugins.local.LocalHomeProxy.invoke(LocalHomeProxy.java:110)
       at $Proxy126.create(Unknown Source)
      



        • 1. Re: postgres and foreign key relation problems
          jae Master

          here are the relevant portions of the jbosscmp-jdbc.xml file

           <entity>
           <ejb-name>StoryEJB</ejb-name>
           <create-table>true</create-table>
           <remove-table>true</remove-table>
           <pk-constraint>true</pk-constraint>
           <table-name>nuke_news_stories</table-name>
          
           <cmp-field>
           <field-name>allowComments</field-name>
           <column-name>allow_comments</column-name>
           <not-null/>
          
           </cmp-field>
           <cmp-field>
           <field-name>state</field-name>
           <column-name>state</column-name>
           <not-null/>
          
           </cmp-field>
           <cmp-field>
           <field-name>storyAdminNotes</field-name>
           <column-name>admin_notes</column-name>
          
           </cmp-field>
           <cmp-field>
           <field-name>storyID</field-name>
           <column-name>story_id</column-name>
           <not-null/>
          
           <jdbc-type>INTEGER</jdbc-type>
           <sql-type>SERIAL</sql-type>
          
           <auto-increment/>
           <dbindex/>
           </cmp-field>
           <cmp-field>
           <field-name>storySummary</field-name>
           <column-name>summary</column-name>
           <not-null/>
          
           </cmp-field>
           <cmp-field>
           <field-name>storyText</field-name>
           <column-name>ext_text</column-name>
          
           </cmp-field>
           <cmp-field>
           <field-name>storyTitle</field-name>
           <column-name>title</column-name>
           <not-null/>
          
           </cmp-field>
           <cmp-field>
           <field-name>views</field-name>
           <column-name>views</column-name>
           <not-null/>
          
           </cmp-field>
           <cmp-field>
           <field-name>creationDate</field-name>
           <column-name>create_date</column-name>
           <not-null/>
          
           </cmp-field>
           <cmp-field>
           <field-name>IP</field-name>
           <column-name>ip_addr</column-name>
           <not-null/>
          
           </cmp-field>
          
           <!-- finders removed -->
           <entity-command name="nukes-entity-command">
           </entity-command>
           </entity>
          
           <entity>
           <ejb-name>TopicEJB</ejb-name>
           <create-table>true</create-table>
           <remove-table>true</remove-table>
           <pk-constraint>true</pk-constraint>
           <table-name>nuke_news_topics</table-name>
          
           <cmp-field>
           <field-name>state</field-name>
           <column-name>state</column-name>
           <not-null/>
          
           </cmp-field>
           <cmp-field>
           <field-name>topicID</field-name>
           <column-name>topic_id</column-name>
           <not-null/>
          
           <jdbc-type>INTEGER</jdbc-type>
           <sql-type>SERIAL</sql-type>
          
           <auto-increment/>
           <dbindex/>
           </cmp-field>
           <cmp-field>
           <field-name>topicImage</field-name>
           <column-name>image</column-name>
          
           </cmp-field>
           <cmp-field>
           <field-name>topicName</field-name>
           <column-name>name</column-name>
           <not-null/>
          
           </cmp-field>
           <cmp-field>
           <field-name>topicText</field-name>
           <column-name>text</column-name>
           <not-null/>
          
           </cmp-field>
           <cmp-field>
           <field-name>views</field-name>
           <column-name>views</column-name>
           <not-null/>
          
           </cmp-field>
           <cmp-field>
           <field-name>creationDate</field-name>
           <column-name>create_date</column-name>
           <not-null/>
          
           </cmp-field>
           <cmp-field>
           <field-name>IP</field-name>
           <column-name>ip_addr</column-name>
           <not-null/>
          
           </cmp-field>
          
           <!-- finders removed>
           <entity-command name="nukes-entity-command">
           </entity-command>
           </entity>
          
           <ejb-relation>
           <ejb-relation-name>Topic-Story</ejb-relation-name>
          
           <ejb-relationship-role>
           <ejb-relationship-role-name>Story-belongsto-Topic</ejb-relationship-role-name>
           <key-fields/>
          
           </ejb-relationship-role>
           <ejb-relationship-role>
           <ejb-relationship-role-name>Topic-has-Stories</ejb-relationship-role-name>
           <key-fields>
           <key-field>
           <field-name>topicID</field-name>
           <column-name>topic_id</column-name>
           </key-field>
           </key-fields>
          
           </ejb-relationship-role>
           </ejb-relation>
          


          • 2. Re: postgres and foreign key relation problems
            Juha Lindfors Master

            do you have the <insert-after-ejb-post-create> thingy configured?

            • 3. Re: postgres and foreign key relation problems
              jae Master

              no - i do not. i was thinking about that myself b/c i've seen this type of problem before using oracle, but we decided to "defer" the integrity check on the database side of things, rather then using the "post create insert".

              do you happen to know the xdoclet tag for that? (i'm looking now, but can't find it).

              • 4. Re: postgres and foreign key relation problems
                jae Master

                d'oh - that's an option on the container configuration, so there wouldn't be any xdoclet tag for it. :)

                thx for your help juha, i hope this solves the issue.

                • 5. Re: postgres and foreign key relation problems
                  sesques Novice

                  Hi jae,

                  Have a look to this topic which treats this big subject (and the JBoss limitations)
                  http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3831917

                  Pascal

                  • 6. Re: postgres and foreign key relation problems
                    jae Master

                    thx! your solution setting the pk in the ejbCreate method to a dummy value seems to work like a charm.

                    is this a possible bug? i'm not really sure why this addresses the problem b/c the dummy value that is given is replaced w/ the value generated by the database on insert.

                    • 7. Re: postgres and foreign key relation problems
                      Mark Miller Newbie

                      I just recently converted from using hypersonic to postgres, and I was having the same problem with cmr fields with foreign keys. I foun dthat the probelm was caused because when the tables were created they were set to make the fk columns non-nullable. I believe this was because when no jdbc-type and sql-type are specified for the key-field in the ejb-relationship-role then the jdbc-type and sql-type from the primary key field are used. This does not work in postgres if you are using auto-generated primary keys because the pk has a sql-type of SERIAL while the fk should be INTEGER. By adding the jdbc-type and sqj-type to the key-field I was able to fix this:

                      <ejb-relationship-role>

                      <ejb-relationship-role-name>node-has-tcpPorts</ejb-relationship-role-name>
                      <key-fields>
                      <key-field>
                      <field-name>nodeId</field-name>
                      <column-name>NODEID</column-name>
                      <jdbc-type>INTEGER</jdbc-type>
                      <sql-type>INTEGER</sql-type>
                      </key-field>
                      </key-fields>

                      </ejb-relationship-role>

                      Note if you are using XDoclet that these tags are not supported in XDoclet 1.2 but they are in 1.2.1 released earlier this month.

                      I do not beleive I am having to set dummy primary keys and I now have auto generated primary keys and cmr foreign keys working with postgres.

                      P.S.
                      I posted a posting yesterday the 25th of May before I figured this out (I could not query and find this posting yesterday when all of th eold posting were missing), an dI was going to reply to it after I figured it out, but now it and all the other posting from yesterday are gone. Does anybody know if there are any plans to rescue yesterdays posting?

                      • 8. Re: postgres and foreign key relation problems
                        jae Master

                        this is correct - you don't have to set dummy values for any of the primary keys. this would be good info for a wiki faq page, any takers?

                        • 9. Re: postgres and foreign key relation problems
                          cronex2000 Newbie

                           


                          do you have the <insert-after-ejb-post-create> thingy configured?


                          Can I ask where do you set this?

                          • 11. Re: postgres and foreign key relation problems
                            Juha Lindfors Master

                            Have a look at jboss_3_2.dtd

                            • 12. Re: postgres and foreign key relation problems
                              tbech Newbie

                              IMHO the method of setting fk field as nullable is the best and easiest solution. See page before how to achieve it in XDoclet.