8 Replies Latest reply on Sep 13, 2001 6:58 PM by Joseph Fifield

    postgresql strange behaviour

    Achilleus Mantzios Newbie

      Hi ,
      i am experiencing a different behaviour in postgresql,
      using
      1) J2EE RI
      2) JBoss 2.4

      In 1) every function seems to run as an atomic operation,
      (with the database queries either nested in begin-end blocks,
      or by themeselfs if they are the only transaction in the function.

      In 2) strangely for the first connection it begins, sets the transaction isolation level, and the *rollbacks*.
      Afterwards, for every function, jboss starts with issuing a begin,
      which unfortunately never ends, and postgresql locks the table.

      I have read in the forums, that one should close() the connection
      in every home method.

      The point is that the code i am playing with is from SUN's
      J2EE tutorial, and it would be great to run in JBoss without,
      having to modify the code.

      Theory says that a commit is done after the end of each method.
      Any thoughts???

        • 1. Re: postgresql strange behaviour
          Joseph Fifield Newbie

          Hmm... I wonder if this could be the same problem I am running into:

          I have 3 BMP entity beans and a postgresql database. I have a simple servlet for creating and editing one of the entities. It seems the beans accept the new values and can be referenced ok, but the data never seems to get written to the database. I added some debug statements and the sql statements are in fact executing...

          The part I don't really understand is why my command line test client that does the same thing works fine...

          Could this be the same issue? If so, any thoughts?

          Joe

          • 2. Re: postgresql strange behaviour
            Achilleus Mantzios Newbie

            Check your transaction attributes, might be an issue.
            >> and the sql statements are in fact executing...
            followed by "end" or "commit"???
            Do you do any explicit sql calls in your servlet?

            My point, is that jboss tx manager seems to call commit
            whenever he wants, so locks and uncommited changes
            are expected.

            Currently i am using BMP entity beans, with tx attribute= "Required",
            and transaction Isolation level "Read Commited".

            here is my samples....
            (It is actually the SavingsAccount Example from SUN's J2EE tutorial)

            test.jsp
            ======
            ...
            SavingsAccount ach = home.create("1", "ach", "bach",0.00);
            ach.credit(1000.0);
            out.println(ach.getBalance());
            ....


            pgsql.log
            ========
            2001-09-11 16:20:54 [8962] DEBUG: connection: host=127.0.0.1 user=postgres database=dynatest
            2001-09-11 16:20:54 [8962] DEBUG: query: set datestyle to 'ISO'; select getdatabaseencoding()
            2001-09-11 16:20:54 [8962] DEBUG: ProcessUtility: set datestyle to 'ISO'; select getdatabaseencoding()
            2001-09-11 16:20:54 [8962] DEBUG: query: begin
            2001-09-11 16:20:54 [8962] DEBUG: ProcessUtility: begin
            2001-09-11 16:20:54 [8962] DEBUG: query: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
            2001-09-11 16:20:54 [8962] DEBUG: ProcessUtility: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
            2001-09-11 16:20:54 [8962] DEBUG: query: rollback
            2001-09-11 16:20:54 [8962] DEBUG: ProcessUtility: rollback
            2001-09-11 16:20:54 [8962] DEBUG: query: begin
            2001-09-11 16:20:54 [8962] DEBUG: ProcessUtility: begin
            2001-09-11 16:20:54 [8962] DEBUG: query: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
            2001-09-11 16:20:54 [8962] DEBUG: ProcessUtility: SET TRANSACTION ISOLATION LEVEL READ COMMITTED

            (the above is just after jboss startup - see the rollback at the begining!)..

            (the folowing is after i call the test.jsp)
            2001-09-11 16:22:52 [8962] DEBUG: query: insert into savingsaccount values (
            '1' , 'ach' , 'bach' , 0.0 )
            2001-09-11 16:22:52 [8962] DEBUG: query: commit
            2001-09-11 16:22:52 [8962] DEBUG: ProcessUtility: commit
            2001-09-11 16:22:52 [8962] DEBUG: query: begin
            2001-09-11 16:22:52 [8962] DEBUG: ProcessUtility: begin
            2001-09-11 16:22:52 [8962] DEBUG: query: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
            2001-09-11 16:22:52 [8962] DEBUG: ProcessUtility: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
            2001-09-11 16:22:52 [8962] DEBUG: query: update savingsaccount set firstname
            = 'ach' ,lastname = 'bach' , balance = 1000.0 where id = '1'
            2001-09-11 16:22:52 [8962] DEBUG: query: update savingsaccount set firstname
            = 'ach' ,lastname = 'bach' , balance = 1000.0 where id = '1'

            It seems that it can create the BMP bean correctly,
            it actually calls ejbStore() twice (!!), but never ends the transaction.

            • 3. Re: postgresql strange behaviour
              Achilleus Mantzios Newbie

              After reading about
              a) Isolation levels
              b) Transaction Attributes
              c) Jboss Commit Options

              i concluded that the last two should affect how and when
              a transaction takes place, and how often db synchronization
              and caching are managed.

              BUT, once a transaction begins it must either rollback, or commit.
              In CM transactions (entity beans case) i think this must be always
              true.

              I dont have a clue of why this is happening...
              Maybe some help from a guru would be really good.

              • 4. Re: postgresql strange behaviour
                Joseph Fifield Newbie

                I do not have any explicit transaction code in my beans. I messed around with the transaction attributes in the dd, but it didn't seem to help.

                >> Do you do any explicit sql calls in your servlet?

                No. I am only using the entity bean.

                This is probably a bit off topic, but how do I get a pgsql.log? I don't seem to have one on my system. I assume I have to set an option somewhere?

                I will spend some time reading up on transactions in JBoss and see what I can come up with.

                As I said, the strange part is it works fine in a stand-alone, command-line client...this is only happening from a servlet (with the exact same bean interaction code).

                • 5. Re: postgresql strange behaviour
                  Achilleus Mantzios Newbie

                  For the pgsql log thing...
                  start your pgsql like

                  #!/bin/sh
                  su postgres -c "pg_ctl -D /var/lib/pgsql/data/ start -l /var/log/pgsql.log"

                  make sure you rotate the logz :)

                  • 6. Re: postgresql strange behaviour
                    Joseph Fifield Newbie

                    Ahhh, thanks. I'll give that a try.

                    I seem to have solved my problem though. When I implemented the bean, I used the method in the Sun J2EE Developer's Guide of getting a connection in setEntityContext and closing it in unsetEntityContext. After reading about the connection pools more, I decided to try getting the connection immediately before and closing it immediately after I execute the sql. That seems to have fixed the problem with the data not being commited. I'm not sure if this new approach is suitable or not.

                    • 7. Re: postgresql strange behaviour
                      Achilleus Mantzios Newbie

                      Thats what i did myself and worked.
                      Are both your ejbCreate and ejbStore that dont do commit???
                      In my case i got a commit only in ejbCreate but NOT in ejbStore.
                      Note that i also tried the SavingsAccount example from SUN's
                      J2EE tutorial (the new one), which also suggests this method
                      of connection caching.

                      Whats your commit options??
                      Your transaction attributes??

                      • 8. Re: postgresql strange behaviour
                        Joseph Fifield Newbie

                        None of the writes to the database were being commited.

                        As far as transaction attributes, I tried setting different ones while trying to solve the problem. Currently I have none specified...so the whatever the defaults are.