Menu
▾
▴
#2345 Enable Native Sequences not working
Hi,
I've just tried to run Enable Native Sequences and I got a lot of errors mainly because the ENABLE_NATIVE_SEQUENCE flag is set on the start of process and during the process ADempiere tries to create new rows is tables like AD_Sequence, try to use the native sequence, but that sequence is not already created at that point.
In order to work correct we need to create the native sequences for some tables.
More, if there is a native sequence already created for a table ADempiere will fail. To fix that problem i modified the DB_Postgresql.createSequence method to check if sequence is already created. If it's created we alter it if not we create it.
Best regards,
Teo Sarca
Hi Teo, good catch, this is a promising although underdeveloped functionality. I think this can improve performance and solve many locking issues.
Attached a patch BF2970200.patch to fix this problem (I tested it in oracle and postgresql).
The main issue maybe was the lack of "throw" keyword in the process, and, as you mentioned the process failed if the sequence already existed (and adempiere seed has 4 sequences inherited from compiere).
For oracle the tables with name > 26 characters represent a problem:
select tablename, length(tablename) from ad_table where length(tablename) > 26 and isview = 'N'
A_Depreciation_Table_Header
A_Depreciation_Table_Detail
So, I changed to create
S[TableName]
Insted aof
TableName[_Seq]
NOTE: Following the same thinking I changed the housekeeping process to use H instead of Hst_.
And maybe it can be a good idea to change the length of TableName in dictionary from 30 to 29 to cope with this oracle restriction.
>>>>
Now, this functionality cannot be considered complete without the corresponding process "Sequence Check".
We need that process "Sequence Check" update the native sequences to max(ID) and create the missing native sequences.
Maybe this can be easily done with this same algorithm - in Sequence Check process we can drop and recreate all sequences but this time using MAX(ID) of each table.
WDYT?
Regards,
Carlos Ruiz
Revision: 11660
http://adempiere.svn.sourceforge.net/adempiere/?rev=11660&view=rev
Author: teo_sarca
Date: 2010-03-14 21:22:57 +0000 (Sun, 14 Mar 2010)
Log Message:
-----------
BF [ 2970200 ] Enable Native Sequences not working
Link to SF Tracker: http://sourceforge.net/support/tracker.php?aid=2970200
Modified Paths:
--------------
trunk/base/src/org/compiere/db/DB_Oracle.java
trunk/base/src/org/compiere/db/DB_PostgreSQL.java
trunk/base/src/org/eevolution/process/EnableNativeSequence.java
Hi Carlos,
Thanks for the great review. I've just commited the fix that I've already developed. I've also merged your patch for DB_Oracle class.
Regarding changing the sequence name from <TableName>_SEQ to S<TableName>, I think this will bring some backward compatibility issues since there are people which use them in production. I think is better to discuss this on forums.
About changing the naming conventions for history tables, I also think is better to discuss them on forums and to fix in other tracker.
What do you think ?
Best regards,
Teo Sarca
Hi Teo, good refactoring.
The contribution is broken (not because you broke it, it's broken since contributed).
It doesn't work in oracle as is today - and every thing committed in release must cover both databases supported.
You're right - there must be people using this functionality in postgresql - so I think a migration script to rename the sequences can do the trick (as well as a migration script to rename hst_ tables)
Last but not least - I insist Sequence Check functionality is absolutely needed to declare this complete.
Regards,
Carlos Ruiz
arrived to /release with revision 11665
not closing as there are two pending issues
- not working for oracle
- Sequence Check needed for native sequences