[jboss-jira] [JBoss JIRA] Commented: (JBMESSAGING-1664) Ordering Group is broken on server restart when messages are paged and reloaded

[Howard Gao (JIRA)]

    [ https://jira.jboss.org/jira/browse/JBMESSAGING-1664?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12477267#action_12477267 ] 

Howard Gao commented on JBMESSAGING-1664:
-----------------------------------------

Currently I think I have two options.

Option 1 is based on my current fix. To limit the memory when we fetch all the messages for a queue from DB, we can use Statement.setFetchSize().

Option 2 is that we split the query into two queries. First we query up to fullSize the messages for a Channel (queue), of which we can safely put those messages whose PAGE_ORD are null values into memory queue. Then we remember the number of messages (say n) having been loaded. Second we query all messages that have null PAGE_ORD values and, after dropping the first n messages, update their PAGE_ORD values.

Option 1 is better. Option 2 is not so simple, as the first query may introduce new SQLs that have to use DB specific syntax. Here is the list of SQL for different DBs:

SELECT * FROM T LIMIT 10 OFFSET 20 	MySQL, PostgreSQL (also supports the standard, since version 8.4), SQLite, H2
SELECT * from T WHERE ROWNUM <= 10 	Oracle (also supports the standard, since Oracle8i)
SELECT FIRST 10 * from T 	Ingres
SELECT FIRST 10 * FROM T order by a 	Informix
SELECT SKIP 20 FIRST 10 * FROM T order by c, d 	Informix (row numbers are filtered after order by is evaluated. SKIP clause was introduced in a v10.00.xC4 fixpack)
SELECT * FROM T FETCH FIRST 10 ROWS ONLY 	DB2 (also supports the standard, in Linux, Windows, and Unix since DB2 v8, z/OS support added in v9)
SELECT TOP 10 * FROM T 	MS SQL Server (also supports the standard, since SQL Server 2005), Sybase ASE, MS Access
SELECT TOP 10 START AT 20 * FROM T 	Sybase SQL Anywhere (also supports the standard, since version 9.0.1)
SELECT FIRST 10 SKIP 20 * FROM T 	Interbase, Firebird
SELECT * FROM T ROWS 20 TO 30 	Firebird (since version 2.1)

http://en.wikipedia.org/wiki/Select_(SQL)


> Ordering Group is broken on server restart when messages are paged and reloaded
> -------------------------------------------------------------------------------
>
>                 Key: JBMESSAGING-1664
>                 URL: https://jira.jboss.org/jira/browse/JBMESSAGING-1664
>             Project: JBoss Messaging
>          Issue Type: Bug
>          Components: Messaging Core
>    Affects Versions: 1.4.0.SP3.CP08, 1.4.4.GA
>            Reporter: Howard Gao
>            Assignee: Howard Gao
>             Fix For: 1.4.0.SP3.CP09, 1.4.5.GA
>
>
> When messages are paged into the DB and reloaded on server restart, the strict is broken.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira