Sequences
-
I wasn't sure whether to put this here or in coding help.
The first number returned by my sequences is reliably the start value, 1.
However, while the second value returned is usually 2, it occasionally randomly returns 21 next instead. There is no difference in how the sequence was created.
-
I don't know Oracle, so I have no idea if this is a WTF or not. What guarantees do sequences have?
-
got some SQL for us to take a gander at?
-
Multiple instances? Restarting between generating numbers?
-
Sounds like you're likely using
CACHE 20
when the sequence is created. I believe any cached values are thrown away if the DB is shut down.EDIT: There are other reasons, I think, that cause the numbers cached to be tossed.
-
There are multiple reasons (another user requests numbers from the sequence, rollbacks, cached values being discarded as @boomzilla mentioned) a sequence may "skip" numbers. Oracle makes no guarantees otherwise, so don't count on them being consecutive.
-
I know there's no guarantees on them being consecutive. But them being consistent between automated test runs would be nice.
I'll look into the cache thing.
-
Oracle makes no guarantees otherwise, so don't count on them being consecutive.
Yeah, but the consistent skip of 20 is suspicious, which is why the cache seems likely.
-
I know there's no guarantees on them being consecutive. But them being consistent between automated test runs would be nice.
Understandably. If you are on a RAC, check out the ORDER option as well since NOORDER is default
-
Yeah sounds like caching.
As stated, no guarantee to be consecutive so no WTF.
-
As stated, no guarantee to be consecutive so no WTF.
I did say coding help not sidebar .
Alternatively I'll get to spend some time on a dumping mechanism that doesn't expose IDs.
-
I hate sequences. We use UUIDs for all our ID needs.
-
Possible, but I'm not sure that'd help with consistent test references. Unless you consider them consistently mismatching an improvement.
-
I don't understand what you're up to, but you could always pre-generate some ahead of time. Not sure why it's important to have consistent IDs in any case, but I'll take your word for it.
-
I know there's no guarantees on them being consecutive. But them being consistent between automated test runs would be nice.
There's either a guarantee or there isn't. If there isn't, then it doesn't matter whether the next value is 21 or 2021 or 20021, as long as it meets the guarantee.
Your test is wrong, not Oracle.
-
Quite possibly.
We're testing a webservice configuration interface.
- Test setup: Create new DB user with default tables
- Run test: perform various webservice calls to create configuration items
- Verification step: dump various configuration tables to ensure stuff got stored correctly.
We're dumping the tables including IDs to tracck parent/child relations. There's currently no functional dumping mechanism available that avoids the IDs.
-
We're dumping the tables including IDs to tracck parent/child relations.
You don't need sequences for that, surely?
-
But them being consistent between automated test runs would be nice.
We're testing a webservice configuration interface.
Your Doing IT Wrong™
That your tests depend on a database is the wrong part. There are several ways to avoid this.
OMG!!!!!111! YOUR TESTING ON ORACLE?!?
-
Verification step: dump various configuration tables to ensure stuff got stored correctly.
Except IDs are irrelevant to the actual data. They're implementation-detail, not data. If you later switch to using GUID IDs, then your test will fail even if all the relations are still correct and the data is stored fine.
EDIT: BTW this belongs in the "I can't believe I had to explain this to a programmer" file.
There's currently no functional dumping mechanism available that avoids the IDs.
Spend ten seconds writing one?
-
Gonna be longer than 10 seconds. But that's probably the wisest.
-
If you're checking the joins, why not query the tables joined on ID to verify that the right rows are connected?
-
I'd use the dumping part of the same webservice, but that got deferred indefinitely, and a functional test is part of Definition of Done. We didn't find out it was unstable till later.
-
Why are you comparing a dump at all? Why not just query the relevant rows from the database live?
-
I may be missing something. We're not using an oracle dumping tool; we run a query (SELECT *) against the table and storing the results in file. The reason we're not using test-specific queries is to limit the amount of test code. We don't have those queries for other purposes since normal processing uses the IDs to correlate in a later stage, so does the web UI, and the web service doesn't have a query component.
So we need either a query component to the webservice or a better backup tool. But this test inconsistency got bumped to a low priority so it'll be a while until I get to spend time at that.
-
Except IDs are irrelevant to the actual data. They're implementation-detail, not data. If you later switch to using GUID IDs, then your test will fail even if all the relations are still correct and the data is stored fine.
EDIT: BTW this belongs in the "I can't believe I had to explain this to a programmer" file.
Can I send you to Pittsburgh to talk to our vendor then? Because they sure need that codesplained to them...
-