Return to Revisions

Is it expected that this can happen. A newer transaction claiming a lower seq value?

Yes.

The formulation "claiming a lower seq value" is fuzzy. You seem to be thinking "writing a lower sequence number to a table row". But that's already beyond the reach of a SEQUENCE.

What a SEQUENCE guarantees

(Also addressing the discussion in comments.)
Retrieving numbers from a sequence happens strictly sequentially, as the name implies - with some notable exceptions:

• Manual intervention. Users with sufficient privileges can manipulate the SEQUENCE object any way they wish.
• Wraparound. Typically an unlikely event, even for integer number space. Near impossible for the vast realm of bigint - except, again, after manual intervention or code bugs of colossal proportions. You would be able to tell a wraparound when looking at it.
• Sequences with a cache setting greater than 1.

There is an example in the manual:

For example, with a cache setting of 10, session A might reserve values 1..10 and return nextval=1, then session B might reserve values 11..20 and return nextval=11 before session A has generated nextval=2. Thus, with a cache setting of one it is safe to assume that nextval values are generated sequentially; with a cache setting greater than one you should only assume that the nextval values are all distinct, not that they are generated purely sequentially.

Bold emphasis mine.

BUT that's beyond the point

For one, the time when a sequence number is issued is not strictly bound to the time when (or if) it will be written. The way can be long and winding. Doesn't even have to be in the same transaction if client code is written that way. nextval() can be called manually, a PROCEDURE or an anonymous code block with nested COMMIT, ...

More importantly, transactions draw numbers largely independent of when they started. Just because one started earlier does not mean it draws from the sequence earlier. Of course, later transactions can write earlier sequence numbers, or in your terms, a newer transaction can claim lower seq values.

The whole idea falls flat at the most basic level.