FWD » Core Development » Database

I have done some investigations and here is what I found out:
The Hibernate Dialect base class defines the following sequence-related methods:

1. boolean supportsSequences()
2. boolean supportsPooledSequences()
3. String getSequenceNextValString(String sequenceName)
4. String getSelectSequenceNextValString(String sequenceName)
5. String getQuerySequencesString()
6. String getDropSequenceString(String sequenceName)
7. String getCreateSequenceString(String sequenceName, int initialValue, int incrementSize)

• offers full support for sequences (min and max values, variable increment and cycling property).
• errors in query the sequence's value should be interpreted as unknown values.
• there are statements to create/drop the sequences.
• there are 3 functions that handle them: currvalue(seq), nextvalue(seq), setvalue(seq, val). There is a catch, however. The currvalue is relative to current session and will fail if called before a call to nextvalue or setvalue.

• only offers basic support, sequences only features a current value and an increment.
• currvalue(seq) and nextvalue(seq) are available but to reset the sequence an ALTER SEQUENCE seq RESTART WITH n statement is to be used.
• I don't know how can we emulate cycling sequences or min/max boundries.

The last approach would be to manage sequences ourselves using a dedicated table and implement the entire sequence functionality using SQL statements / triggers.

Ovidiu Maxiniuc wrote:

[...]

PostgreSQL

• offers full support for sequences (min and max values, variable increment and cycling property).
• errors in query the sequence's value should be interpreted as unknown values.
• there are statements to create/drop the sequences.
• there are 3 functions that handle them: currvalue(seq), nextvalue(seq), setvalue(seq, val). There is a catch, however. The currvalue is relative to current session and will fail if called before a call to nextvalue or setvalue.

Does this session-specific behavior differ from that of current-value in Progress?

H2

• only offers basic support, sequences only features a current value and an increment.
• currvalue(seq) and nextvalue(seq) are available but to reset the sequence an ALTER SEQUENCE seq RESTART WITH n statement is to be used.
• I don't know how can we emulate cycling sequences or min/max boundries.

The last approach would be to manage sequences ourselves using a dedicated table and implement the entire sequence functionality using SQL statements / triggers.

Can we go with a hybrid approach, where we use the native sequences to the degree possible, but blend this with the Progress-like features in our P2JXXXDialect classes? For example, enforce the boundaries in the dialect, but use the backing sequence to get the sequence values within those boundaries. And use H2's alter sequence to simulate cycling when we hit the cycle boundary.

I think there is value in having the sequences there in the database, but we have to weigh this against the value of having a single implementation that works for every database (but only through the P2J APIs). Thoughts?

Please also research sequences in MS SQL Server, which we have to support for this project.

1. There is no problem for Progress/OE having a simple code like:

display current-value(my-seq-1).

2. This hybrid approach implies storing additional information about sequence's state into a dedicate table (cycling, min / max, if the sequence has passed the limit and should return ?). Maybe using triggers or stored procedures ? I don't know the effort needed nor if H2 supports this type of server-side functionality.

• Creation of sequences. Native, for example:
  

  CREATE SEQUENCE "p2j_test.my-seq-1" AS INT 
     START WITH 10 INCREMENT BY 3 
     MINVALUE 1 MAXVALUE 20
     CYCLE
  

• Current value. There is no direct support to query the current value, instead a small hack can be used:
  

  SELECT current_value FROM "sys.Sequences" WHERE name='p2j_test.my-seq-1'
  

  
  The good part is that all sequence properties can be read from this system table, but write access only by SQL statements.
• Next value. Native support:
  

  SELECT NEXT VALUE FOR "p2j_test.my-seq-1" 
  

• Set value. Native, using a small workaround:
  

  ALTER SEQUENCE "p2j_test.my-seq-1" RESTART WITH 5
  SELECT NEXT VALUE FOR "p2j_test.my-seq-1" 
  

  
  Note the extra NEXT VALUE called here. It is needed to emulate the 4GL behavior. As in MSSQL, next value returns 5, but Progress will return 6. The same would be needed in PostgreSQL but there is an extra form of SETVAL which accept a 3rd boolean parameter that fits the Progress way of handling Sequences.
• Removing. Using native sql statements:
  

  DROP SEQUENCE "p2j_test.my-seq-1" 
  

I discovered a way to access the current value in PostgreSQL without calling CURRVAL which fails if it's called before NEXTVAL.
The solution is similar with MSSQL, ie. access SQL internal structures directly:

SELECT last_value FROM "my-seq-1";

An issue related to PSQL, but not only:

On a finite, non cycling sequences, calling nextVal will return an error if passing over the upper/lower bound which will be interpreted as unknown (?). This is fine. However, there is no native way extract this value (?) afterward using currentValue as this will always return the last VALID value.
I would go for an implementation where I use a P2J private table, to memorize if the last nextVal passed the bounds and the actual value is ?. It would be reseted when setValue is invoked. However, this also involves transactions / locking and I don't know if it is justified as performance, as for each currentVal this table is first queried and updated on nextVal and setVal.

What do you say about this ?

If we need transaction processing separate from the current (application-defined) database transaction, it sounds like this would have to happen on a dedicated service thread which would manage its own transactions. I don't know what the performance impact would be, but we have to put correctness (i.e., Progress compatibility) above performance.

Let's try this approach. If the performance is simply awful, we will need to figure out another way. Please document this thoroughly in the code, since it is not intuitive.

The implementation a service thread for handling sequence primitives (current/next/set) like this:
- the current value is directly returned as no modifications are done into database
- the thread is lazy created & started, only when it is truly needed (this posed a small problem because staring a new thread may take a while and the calling thread had to be blocked during this)
- normally the thread sleeps. When a next/set request arrives (code is passed as a Runnable) it stored into a thread field, then it is notified/waked-up and it will run the code then notify back the caller thread and enter wait() again.
- the calling thread (the one that handles "client" code) will be blocked while the procedure executes.

I have the following strange problem. I believe I'm doing something wrong. When the Runnable.run() gets to execute it will take up 100% CPU, blocking the JVM. What I do:
- I obtain a thread-local Persistence object by calling ConnectionManager.getPersistence(ldbName). This should be a 'clean' Persistence object associated with the new thread/context
- then I start a sequence-specific transaction: persistence.beginTransaction()
I investigated the javadoc of Persistence and found no obvious issues, except the following:
"Only one transaction per client context may be active at a time."
Can this be the cause of my problem ?
However, does this means that if some P2J code is already in a transaction, I cannot use another transaction to manage the sequences defined into a user-table in database ?

I spoke with Greg about this and he suggested a much more efficient (and simpler) solution:

Add flags to the directory for each database/sequence combination, which indicate whether or not we have crossed the upper/lower boundary of each sequence and we currently are in the "out-of-bounds" state. At server startup, these would be read by a service object in the persist package and stored in memory. Provide an API through this service object that allows these in-memory flags to be checked at each nextVal call. When we change state (i.e., exceed an upper/lower boundary OR set the current value back within the allowed range), the in-memory flags would be changed AND the new value of the appropriate flag would be written to the directory (for persistence across server restarts).

You'll need to think about the best way to design this API to work with the use of sequences. The above is just meant to convey the general idea. This approach avoids the overhead of database access and the complexity of a separate thread.

See com.goldencode.p2j.directory.RemapTestDriver1 for examples of how to make updates to the directory. Basically, the cycle is:

• bind
• update specific values
• unbind

In terms of where in the directory we would store this new information, I would suggest the path server/{site}/database/{database name}/p2j/sequence/{sequence name}/out_of_bounds.

I have discovered something at least interesting today. The Progress/4GL 9 and OpenEdge/ABL 11 Reference books, both say (pages 223, respectively 294/557) that (DYNAMIC-)CURRENT-VALUE will return "The Unknown value (?) if the sequence has exceeded its minimum or maximum and is not cycling".

As both documents uphold this I took it for granted. While I reworked my testcases for sequences I discovered that this is not true. After reaching the upper/lower limit, the current value (both dynamic and static) will never be returned as Unknown value (?), but as the last valid before invoking the NEXT-VALUE. The fact is, this is the exact way PostgreSQL handles sequences and this should be a good news as we will need to write special code for sequences only for H2 dialect.

On the other hand, as both reference books affirms the opposite, I wonder if there isn't a bug in the OE version from windev01.

Ovidiu Maxiniuc wrote:

...The fact is, this is the exact way PostgreSQL handles sequences and this should be a good news as we will need to write special code for sequences only for H2 dialect.

Indeed good news! How do you see the H2 implementation working?

On the other hand, as both reference books affirms the opposite, I wonder if there isn't a bug in the OE version from windev01.

More likely a bug in the documentation; we have found many occasions where the documentation does not match the behavior.

The actual implementation of sequences does change their name to a SQL-legal name and the old "historical" name is temporarily saved as annotation. When using static sequence calls this is OK, the original name can be swapped with sql-valid at conversion time. The problem is when using dynamic forms of the functions/statements, the sql name is not know at conversion time so some kind of mapping should be maintained.

Example:
For the 4GL sequence name: "my-seq-1" the name will be morphed to "my_seq_1" to be sql-compliant.
When using static calls like

CURRENT-VALUE(my-seq-1)

SequenceManager.currentValue("my_seq_1")

DYNAMIC-CURRENT-VALUE("my-" + "seq-1", "p2j_test")

Taking into consideration that some other data are also managed in P2J using the legacy-name (for example the second (optional) parameter - the database name), I let all method (static & dynamic) to be called the same consistent way. And the binding to actual sql-legal name is done at runtime.
Generally, they work now, edge-cases are in tests now.

I am having the following issue.

message dynamic-current-VALUE("my-seq-9", "p2j_test1").
display dynamic-current-VALUE("my-seq-9", "p2j_test2").
display "3!".

Database p2j_test1 does not exist or is not open. (3141)

After pressing the space bar it prints in status bar:

Database p2j_test2 does not exist or is not open. (3141)

An then it prints

Procedure complete. Press space bar to continue.

and the 3 line is never executed.
Is there a way to detect the message and use ErrorManeger.recordOrShowError and use the ErrorManeger.recordOrThrowError in all other cases ?

I discovered something strange with sequences.
If you define a sequence with negative increment Progress will automatically create a MAX-VALUE equals to the initial value. This is the export of a sequence that starts at 0 and goes down (it does not matter if it has a limit or whether it is cycling):

ADD SEQUENCE "seq-desc" INITIAL 0 INCREMENT -10 CYCLE-ON-LIMIT yes MIN-VAL -1000 MAX-VAL 0

current-value(seq-desc) = 10

ADD SEQUENCE "seq-asc" INITIAL 0 INCREMENT 10 CYCLE-ON-LIMIT yes MAX-VAL 1000

current-value(seq-asc) = -1000000

Because PSQL is symmetric from this point of view, at this moment, I cannot rely on PSQL to fully handle sequence operations.
When a sequence is cycling PSQL will warp from MAX to MIN values (or the reverse) it does not keep the "start with" value.
Progress, instead will always cycle back to initial value.

Note 26 continued.
From my quick tests, it seems that message is more permissive about errors occurred while evaluating its parameters. It will display the error but will not stop the procedure from running.
The very same will happen with

    MESSAGE INT("a").
    DISPLAY INT("b") /*no-error*/.
    MESSAGE "3!".

• I am in doubt that a new service thread is the best approach for atomizing/synchronizing accesses to sequences.
• I don't know if Hibernate allows parallel transactions from a dedicated/separate connection - but I believe this solution is rather complicated.
• If I have a clear understanding of the architecture, there can be multiple clients connected to the server application but only one server application connected to SQL server. This means that we can use java locking mechanism (on server application) to synchronize access to each sequences of the database.
• Any solution we adopt, It will be hard enough to effectively prove it work without dead-locking or any other surprises.

Ovidiu Maxiniuc wrote:

Note 26 continued.
...

Greg: I need your input here.
Ovidiu: since this is an error/exceptional condition, please continue with all other aspects of the implementation in the meantime.

Note 28
...

Of the 50+ sequences defined for the current project, none have negative increments. Please document (in the code) this behavioral difference as a limitation of the current implementation, which may need to be addressed at a future time, then move on.

Code review 20130712a:

• Please add a WARNING to schema conversion if we encounter a sequence definition with a negative increment, given your findings. This will remind us to deal with the limitation discussed above if we encounter it in a future project.
• In the dialect-specific sequence handlers, please replace the System.{err/out}.println calls with runtime logging. Search for LogHelper.getLogger across the project for examples of use. If some of these printlns are just develop-time debugging, please remove them when you finalize this code.
• A minor thing, but in some of the inline comments regarding sequence cycling, you wrote "warps/warping/warped" where I think you meant "wraps/wrapping/wrapped"?
• In the SequenceManager.getSequenceHandler factory method, rather than testing for every dialect type using instanceof, let's leverage polymorphism and add a getSequenceHandler method to the P2JDialect interface. Each dialect must implement this method to return a handler instance or null. If null, the SequenceManager.getSequenceHandler method uses its default. We will be adding dialects over time, and I think this approach is cleaner. Also, replace the System.err.println call here with logging.

On Friday, the runtime test failed with an abend in gso_ctrlc_3way, failure in step 11, and about 10 other issues in gso_tests and tc_tests:

I re-runned the test with same update and I got no abends, only 3 issues in main part. Constantin had a quick look at the harness result and he said it looks ok.

I am waiting for your review when you have the time.

Code review 20130801a:

Looks good. There is a TODO comment in the PostgreSQLSequenceHandler c'tor. Is there something left to do, or can it be removed?

In regard to note 26 and note 30, this is a known flaw in our processing for the MESSAGE statement. We don't need to fix that in this task.

I removed the TODO comment that eclipse inserted automatically.

Regarding the desc sequences, I believe they are not an issue for PSQL dialect any more since I changed the implementation in note 32. The second file attached there contains a set of tests dedicated to this PSQL issue.

However, since the latest revisions from bzr repository overlapped some files I need to redo the regression tests this weekend.

The regression test went flawlessly for the main part (ignoring the usual 'Unexpected EOF in actual at page # 1.' from tc_job_002).
However, I got 4 fails in gso_ctrlc_3way_tests, of which one abend in step 9 of test_5_ctrlc_11_session3. As the last time, the client ended abruptly with:

java.lang.IllegalStateException: Focused widget does not belong to container
        at com.goldencode.p2j.ui.client.widget.AbstractContainer.focusWorker(AbstractContainer.java:821)
        at com.goldencode.p2j.ui.client.widget.AbstractContainer.nextFocus(AbstractContainer.java:463)
        at com.goldencode.p2j.ui.client.FocusManager.focusChange(FocusManager.java:1168)
        at com.goldencode.p2j.ui.chui.ThinClient.nextFocus(ThinClient.java:14802)
        at com.goldencode.p2j.ui.client.Browse.onKeyPressed(Browse.java:1890)
        at com.goldencode.p2j.ui.client.Browse.processEvent(Browse.java:2379)
        at com.goldencode.p2j.ui.client.widget.TitledWindow.processEvent(TitledWindow.java:144)
        at com.goldencode.p2j.ui.chui.ThinClient.processEventsWorker(ThinClient.java:12600)
        at com.goldencode.p2j.ui.chui.ThinClient.pop(ThinClient.java:11268)
        at com.goldencode.p2j.ui.chui.ThinClient.eventBracket(ThinClient.java:11251)
        at com.goldencode.p2j.ui.chui.ThinClient.eventDrawingBracket(ThinClient.java:11177)
        at com.goldencode.p2j.ui.chui.ThinClient.applyWorker(ThinClient.java:10948)
        at com.goldencode.p2j.ui.chui.ThinClient.apply(ThinClient.java:3104)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at com.goldencode.p2j.util.MethodInvoker.invoke(MethodInvoker.java:76)
        at com.goldencode.p2j.net.Dispatcher.processInbound(Dispatcher.java:675)
        at com.goldencode.p2j.net.Conversation.block(Conversation.java:316)
        at com.goldencode.p2j.net.Conversation.waitMessage(Conversation.java:254)
        at com.goldencode.p2j.net.Queue.transactImpl(Queue.java:1107)
        at com.goldencode.p2j.net.Queue.transact(Queue.java:575)
        at com.goldencode.p2j.net.BaseSession.transact(BaseSession.java:178)
        at com.goldencode.p2j.net.HighLevelObject.transact(HighLevelObject.java:163)
        at com.goldencode.p2j.net.RemoteObject$RemoteAccess.invokeCore(RemoteObject.java:1406)
        at com.goldencode.p2j.net.InvocationStub.invoke(InvocationStub.java:97)
        at com.sun.proxy.$Proxy3.standardEntry(Unknown Source)
        at com.goldencode.p2j.main.ClientCore.start(ClientCore.java:216)
        at com.goldencode.p2j.main.ClientCore.start(ClientCore.java:76)
        at com.goldencode.p2j.main.ClientDriver.start(ClientDriver.java:223)
        at com.goldencode.p2j.main.CommonDriver.process(CommonDriver.java:423)
        at com.goldencode.p2j.main.ClientDriver.process(ClientDriver.java:115)
        at com.goldencode.p2j.main.ClientDriver.main(ClientDriver.java:241)

Since this is a merged code base which is different from your last run, please run regression testing one more time (run_ctrlc.sh only). If the same tests do not fail, check it in.

Correction: I went back through my email and found that these tests are intermittently failing due to a non-P2J problem. Go ahead and check in the update.