Wednesday, March 14, 2012

ADF: PS_TXN unique constraint violated passivation

Ok so I had this problem in a few applications and want to solve it the best way once and for all.
java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SCHEMA.PS_TXN_PK) violated or oracle.jbo.PCollException: JBO-28030: Could not insert row into table PS_TXN, collection id . What a pain.

So I have come across a couple of methods to solve this one:

1) Cleanup table regularly - this is a poor solution and is not guaranteed to work all the time. This involves creating a job to run (maybe twice a day) to sort the sequences out and clean up tables. Although it is the easiest to implement and requires no code changes. Can use this as a stop gap measure while your developers are fixing the real problem.

2) use a file store for passivation - better solution this one but I must test the performance impact and what to do if the filestore get corrupt. This involves setting the AM property  jbo.passivationstore = file on all Application Modules. Not convinced this is the best solution but it should do for small apps.

3) using one schema with jbo.server.internal_connection - just specify your datasource name in this property to point to a utility schema and you should be good to go.

4) Catching the error and doing a clean at this point - Lot of work this one better than solution 1 but really not a productive use of time. (I havent tried this one)

I really need to finish the performance testing on our current application with the 11.1.2.1 and unit test before I make the call.

http://www.oracle.com/technetwork/developer-tools/jdev/overview/bc4j-temp-tables-087270.html

6 comments:

  1. I have discovered that if you create the PS_TXN_SEQ as follows:

    create sequence PS_TXN_SEQ MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;

    You will have this problem. Instead, do this:

    CREATE SEQUENCE PS_TXN_SEQ INCREMENT BY 50 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20;

    ADF BC seems to assume that it has the full 50 sequence values to use in session!

    ReplyDelete
  2. Hi Donovan,

    I am using JDev 11.1.1.5.0. I am also getting the same issue, on PS_TXN only in some scenarios of my application. My application uses only one schema having 16 application modules. So I tried to change jbo internal connection to point to the same database. I could not pass thru the error. I tried to increase referenced pool size, still no luck. I tried even clearing ADF BC temporary tables, still the same issue. Please let me know if I am missing anything.

    Thanks,
    Satya

    ReplyDelete
    Replies
    1. Have you tried dropping and recreating the sequence that generates the keys for ps_txn as the above comment from Christopher. Also is the above problem happening on a dev environment where multiple weblogic instances are running against a single database. - very odd that the scripts dont work have you tried dropping the ps_txn table and allowing adf to recreate it? (assuming you are not running in a critical environment)

      Delete
  3. Thanks Donovan for your response. I am trying in my development environment. I tried the purge script provided by ADF team (C:\Oracle\Middleware\oracle_common\common\sql\adfbc_purge_statesnapshots.sql). We have only one managed server running on the admin server.
    Issue: I am trying to open a tab on an entity( say contact id 23), it displays the info with out any error in a tab. Then I tried to open another entity (say contact id 24), I am getting the JBO 28030 exception. It always happening on second request.

    Thanks,
    Satya

    ReplyDelete
    Replies
    1. Sounds familiar, if the clean up script script does not work you have an actual bug and none of the above will help you, what I can imaging is happening is that you have two transactions on a page writing to PS_TXN from different app modules, if you want to recreate the error then zip the log file and email me at the email address on my profile I will take a look.

      Delete