10 Replies Latest reply on May 27, 2004 9:40 PM by jae

    postgres and cmp issues

    jae Master

      i've tracked down the postgres problem, and it stems from the fact that the db does not support "auto-increment" in the same way that hsqldb (and i'm assuming mysql does) - rather then repost past disucssions, use the following as a search criteria if you want to read up on it "postgres AND create AND primary AND key".

      in order to get around this, the sql-type of the primary key needs to be set to "SERIAL". this will be added to the individual db properties files in the build/etc dir so it can be set per database (every other db should work w/ "INTEGER", but being that i don't have oracle installed, someone will have to test that).

      i'm surprised this issue hasn't come up before, as i don't think any of the modules writing to the db should currently work w/ postgres (i tried the faq module, and it doesn't work either) - or perhaps this is just an issue w/ moving to a pure cmp solution.

      in any case, thx again joe for helping to track this down. i'll post another message once i've completed my changed and checked everything in.

        • 1. Re: postgres and cmp issues
          joe hobson Newbie

          I posted an update with some additional thoughts on the user side, continuing the previous discussion. The dev forum is a better place for CMP issues than the user area of course. See what i speculated on that side...


          ... .joe

          • 2. Re: postgres and cmp issues
            jae Master

            ok - i've checked in some changes, but i'm not sure if they will 100% solve the problem. i can successfully create topics, but i am unable to post a story. it keeps telling me that i am trying to insert null into the topic_id column (but a quick debug session shows that the topicEJBLocal object is there when it does the create).

            take a look at it and tell me if it works for you. perhaps my postgres install is f-ed up - i'm not really all that familiar w/ the admin side of it.

            i added xdoclet tokens the the db fragment files, defining the sql-type for postgres to be "SERIAL" (all others are "INTEGER").

            when you take a look at the xdoclet, i don't think the fact that i'm not using the "unknown-pk" method makes any difference (altho someone pls let me know if this is wrong!) - and either way, the db is controling it, i just wrote a specific getter/setter for it.

            my gut tells me i'm missing some postgres specific configuration, but given my previous statement that i'm not really familiar w/ postgres, it could just be gas. :)

            let me know!

            • 3. Re: postgres and cmp issues
              joe hobson Newbie

              I downloaded your changes for the TopicEJB and tried it out with no luck - same problem as before. Since i didn't completely understand how it would be different from the faq module I dropped my faq tables (manually this time, relied on the installer to do it last time) and rebuilt & redeployed the faq. this time it didn't put the nextval(seq) function into the column default, and threw an error (of course).

              So maybe i was wrong about the faq, but maybe not. I decided to destroy everything i had deployed and start from scratch, deleting the whole db and rebuilding from the installer. When i did that, the faq module had the proper nextval(seq) and worked okay. Maybe it's something extra put in with the installer. I downloaded the CVS snapshot fresh just now so i could be sure it wasn't a weird ddl file in my dev directory. So i started clean again (empty db and all), built from snapshot and ran the installer. The faq tables went in just as before, with the nextval(seq) function in the pn_id default.

              Very odd. I don't really know enough about the CMP to be of much help with the details here. Then again, maybe it's not really CMP. Maybe it's just done with handwritten setup.xml files in the module resources. I know Howard is against that though so I wouldn't suspect it. Would definitely be interested in how it's doing this though... .joe

              • 4. Re: postgres and cmp issues
                Sherman Wood Apprentice

                Sorry I didn't bring this up earlier (am busy keeping the company afloat here) but there are a few issues I can clear up.

                The installer, which Noel wrote initially and I enhanced, uses the setup.xmls to create and populate the database tables for the selected modules, before deploying the module SARs. So I think that CMP is not creating the tables if you are using the installer. Once you get the CMP side of things going, you should not have the installer do the DB creation. I am forgetting how default values were going to go in the DB, which is part of the installer/setup.xml scheme, when you use the CMP auto-create approach. This will have to be done.

                Defining a column as SERIAL in Postgres creates the nextval automatically (see http://www.postgresql.org/docs/7.4/interactive/datatype.html#DATATYPE-SERIAL).

                Looking at http://www.jboss.org/index.html?module=bb&op=viewtopic&t=49702, it looks like you have to use @jboss.unknown-pk.

                Hope this helps,


                • 5. Re: postgres and cmp issues
                  jae Master

                  i tried using the unknown-pk setup as well and i still couldn't get the rows to insert. i have one last thing that i'm going to try, and if i can't get it working i'll create postgre specific ddl.

                  • 6. Re: postgres and cmp issues
                    jae Master

                    ok - in order for this to work correctly w/ postgres, the "insert-after-ejb-post-create" flag needs to be set to true on the entity bean container configuration and "dummy" values need to be set for the primary keys in the "ejbCreate" methods.

                    i'm thinking that it may be a good idea to just enable this directly in the standardjboss.xml file, instead of having each of the individual modules create a specific container config just to enable this.

                    i'll get this updated tonight and post back w/ any final instructions that you would need to perform on your end. i'll also put something on the wiki for this.

                    thx to juha and sesques for pointing me in the direction!

                    • 7. Re: postgres and cmp issues
                      jae Master

                      i've checked in the latest changes that should resolve the postgres compatiblity issues.

                      for now, i've created a specific container configuration for news, instead of going to the global standardjboss.xml file - defining "post-create = true" there causes inserts into the "core" tables not work work properly (those ejbCreate methods need to be updated w/ the "dummy" pk values).

                      i've tested this against postgres and hsqldb, and have found no issues. my fingers are crossed that mysql will have no problems as well (any oracle users out there to test this?).

                      let me know if you have any problems.

                      • 8. Re: postgres and cmp issues
                        joe hobson Newbie


                        "jae77" wrote:
                        i've checked in the latest changes that should resolve the postgres compatiblity issues.
                        let me know if you have any problems.

                        I updated via CVS and it works great on my postgreSQL database. Thanks for your hard work on this one. I was beginning to wonder if CMP was gonna live up to all of the hype or just turn into a headache. Still not sure on the answer to that one though.

                        ... .joe

                        • 9. Re: postgres and cmp issues
                          jae Master

                          in the works of mr. burns.... excellent! :)

                          there's a little bit of a headache here, but nothing that can't be overcome, esp now that i know how to resolve the issue.

                          • 10. Re: postgres and cmp issues
                            jae Master

                            i've recommitted changes that remove the "hacks" and instead define sql/jdbc-type xdoclet tags on the foreign key relations so that the columns can be created null.

                            you should drop your tables and re-create them (both for news and downloads), however you can export and reload any data you may have created b/c the table definitions stayed the same.

                            the only modification you will have to make to the dataset is to update any foreign key columns that have 0 as their value to be null.