PostgreSQL: Sequences Not Tied to DB Transactions

Overview

A 'Sequence' in a RDBMS is a named auto-incrementing value that can be incremented either by application logic (=requesting the incrementing to be done programmatically) or by being tied to a DB Column; the latter of which will get unique numbers assigned automatically as records get added to a DB Table that has got such a column. A 'Surrogate Key' of a DB Table is often tied to a Sequence, for example. The Value of a Sequence is persisted in the DB.

PostgreSQL has got an implementation of such Sequences and OpenPetra makes use of Sequences!
A list of all of them can be found right at the end of petra.xml and one can find out if a particular one is used for a DB column's auto-incrementing by searching for sequence="xxx" in the petra.xml file, replacing xxx with the name of a Sequence (e.g. sequence="seq_login_process_id"); that particular Sequence is used in DB Table s_login on the "s_login_process_id_r" Column.

This article highlights a particular 'quirk' of the implementation in PostgreSQL - namely that Sequences are not tied to DB Transactions - and explains the consequences and what can be done to not run into unintended behaviour in OpenPetra. One would like to think that the numbers that one gets from a Sequence and then stores in a DB table would always be contiguous, but that isn't guaranteed with PostgreSQL's implementation of Sequences. What is guaranteed, however, is that the Value that one obtains from a Sequence are unique.

NB: That behaviour isn't a bug of PostreSQL but an intended feature of the implementation of Sequences in PostgreSQL, and there are good reasons for it – so we have to make sure that this implementation does not affect OpenPetra in a way that is 'wrong' – at least in a Business Rule sense (e.g. contiguous numbering required in bookkeeping records).

>> DB Transaction Rollback Not Affecting a Value of a Sequence if it Got Increased! <<

Please first read the PostgreSQL Reference for this: [1] - search on that page for ‘nextval’ and read the paragraph titled 'Important'.

OpenPetra uses the 'nextval' operation to increase any Sequence. The crux of the implementation in PostgreSQL (at least for the matter raised here) is this: 'This means that aborted transactions might leave unused "holes" in the sequence of assigned values.'

The consequence of this is that whenever OpenPetra relies on a Sequence for supplying unique values for some purpose then this is fine only if gaps or holes in the values are acceptable - both from an application logic standpoint (that the application wouldn't break if holes are encountered) and from a business rule standpoint too - that if such values are somehow meaningful to users then these then must be OK to have gaps/holes. This needs to be considered especially for any such numbers that are shown to users or get printed somehow, and/or that must fulfil some external requirement to never have gaps/holes (eg. cheque numbers).

If this isn't acceptable/tolerable for some reason then a Sequence must not be used for such purposes because a DB Rollback could be issued (either by application logic or because of an error/exception) and then the Value of the Sequence in question will stay incremented rather than going back to what it was before. The next time the Sequence would get incremented during the process that rolled back earlier - and this time the process runs fine without issuing a DB Rollback - the process will use the next Value of the Sequence in question, which means that the one Value where the Rollback was issued will never make it into our data, i.e. there is a gap in the numbers that we obtained from a Sequence that we aren't aware of and we can't easily do anything about it at this stage!

Note: The IsolationLevel of a DB Transaction has no bearing on the fact that Sequences are not tied to DB Transactions, i.e. the behaviour described in this paragraph applies regardless of the IsolationLevel!

What to do If Contiguousness of Unique Numbers is Required!

The previous paragraph explains that gaps/holes can occur in data when we rely on Sequences for supplying unique numbers. This paragraph explains how to get around this if this is not acceptable for certain numbers.

There is no 'switch' or configuration option in PostgreSQL that would change the behaviour of Sequences so there isn't a way around the issue other than not using a Sequence when contiguousness of unique numbers is a requirement!

The only solution to fulfil that requirement is then to store the value of the number that is required to be unique and contiguous in a DB Table and to 'carefully guard' the incrementing of that value in 'carefully crafted, airtight' business logic. Such is happening e.g. for the 'Batch Number' in the Finance Module - it is stored in the Column a_ledger.a_last_batch_number_i (per Ledger).
Why does this solution work if correctly implemented in the application logic? Because the incrementing of such a DB Column's value is tied to a DB Transaction and hence the value will not be left increased in the DB Column if a DB Transaction is rolled back.

Bugs

Bug #5519 got created in the Mantis Tracker to keep track of any issues that arise of the PostgreSQL implementation of Sequences and the use of them in OpenPetra.

Questions?

Contact christiank if you need further information or clarification.