So I posted a question a little while ago about some primary keys changing when the worksapce is created that block the workspace from merging back into LIVE because of a unique constraint.( merge tries to insert because primary keys are different)
I had chalked that up as a one-off thing because we had never seen it before but it has happened a few times since then.
So what actually happens when a new workspace is created? and how is it possible for the PK to change? I know that it is possible to change the primary key in the _LT table but we do not touch that table and worksapce manager prevents the primary key from being modified through the view. So the only way I can see the primary key changing is somehow the primary keys gets nulled out during an insert and picks up the next value from a sequence.
could it have something to do with the latest savepoint? I noticed that the parent save point doesn't actually say "LATEST" I am assuming it inserts the name of the latest point at the time of creation if you set the savepoint to latest.
The creation of a workspace does not update any _LT tables. It only creates the version/workspace within the hierarchy of the existing workspaces. The operation will acquire a lock on the parent workspace, which can be affected by dmls on other tables, but no data is ever modified.
Is there an existing trigger that populates the primary key columns when they are null? The problem with this being the root cause of what you are seeing, is that the triggers are only fired when going through the view. They are not defined on LT, which is the table that Workspace Manager typically uses for our internal operations. They are removed from the physical table by the enableversioning process. There are exceptions to the internal dmls always being done on LT, but they're are not too many.
After looking at the other topic you mentioned, are you sure that there wasn't a delete of the original row and an insert of a new row with a distinct primary key value? Aside from that, not sure how what you are describing can happen, but would be glad to take a closer look if you can reproduce it.
Ok so there was a bug in the application. It does not explicitly go to the latest savepoint when creating a new transaction workspace. If the user navigates the application just right they can view an old transaction, which would set the savepoint, and then navigate to the screen where they create the new transaction. In this case the records were added as part of a later transaction and were not in the parent savepoint for this transaction. The user added that information again as part of this transaction and that is why the merge is blowing up on the unique constraint.
I don't know if i explained that in manner that anyone can follow; but long story short it is 100% our problem.