RFC: sequence_nogap

[thomaspaulb]

Odoo's ir.sequence nogap implementation is problematic, it can block the system. There's even a module that changes all the sequences by default to standard for this reason, so that nogap is avoided altogether. But, the standard implementation cannot guarantee sequential numbering, which is mandatory in some jurisdictions.

Together with someone who has worked with other enterprise software implentations requiring "nogap" sequences, we came up with the following design to implement a truly "nogap" sequence type.

• Create a new module sequence_nogap
• This module adds a new type of sequence, something like "true_nogap"
• Create a new model/table, ir.sequence.nogap with fields of sequence_id (Many2one to ir.sequence), next_number (Integer), state, free_ids (Array), reserved_ids (Array). Override _init of the model so that we can create this table ourselves to our liking, using the right Postgres field types.

The idea is that the next number is always next_number, unless there is any id in free_ids, then that one is given out first. Upon giving out, it's added to 'reserved_ids' together with the Postgres transaction id that's using it, like so:

[reserved_id, txid, reserved_id, txid, ...]

We implement this by overriding _next_do() and have it call our own function for "true_nogap" sequences:

• Get the transaction id of the current transaction by running select txid_current().
• Creates a new env with a separate cr and database connection, outside of the current transaction. This is called autonomous transaction.
• Using the new cr, takes a row lock on the corresponding ir_sequence_nogap table row. If it can't take it now, wait for it, because it will be just a couple of milliseconds until release.
• Checks if there are currently any reserved numbers in transaction by running select txid_status(). If they are still in transaction, leave them be, if not, then depending on the result status (committed/rolled back), remove them as done, or move them over to the free array. All this could be a single update query. Note This require postgres 10.0+, we can find alternative or check for it in a pre-init hook of the module
• Reserve the first free number, or reserve the next_number.
• Release the row lock and commit the parallel mini-transaction
• Return this number from next_do.

We can then write a simple test:

• Create sequence
• Create 10 transactions
• In each transaction, ask for a new number
• Cancel the uneven transactions
• Commit the even transactions
• Ask for 5 more numbers
• Check if the final result is 10 numbers in sequence.

Note: we have to pay some attention to correct handling of next_number when a sequence switches from one type to another.

[hbrunn]

as we discussed privately, this can cause invoices of an earlier date have a higher number than one of a later date, which might or might not be a problem depending on the legislation this happens in. If that's fine, I think your solution it beautiful technically.

Taking a step back, this is a technical problem created by law with how we do transactions and their rollbacks. If the letter of the law says: 'There must be an invoice document for every number', can't we simply when rolling back a transaction that has reserved a number create a 0 EUR invoice to some dummy partner with this number? This way we could just keep using the standard sequences, have fast transactions and adhere to the letter of the law. But before doing such a thing I'd like to hear from a person knowledgeable about some legislation where no gap is enforced if this would be accepted by accountants/tax office.

[thomaspaulb]

Have set out the question to financial people in several legislations. Copying the text of my question here for reference:

What we know is that the tax office in Netherlands says that invoice numbers must be consecutive within their sequence, and that no gaps may exist between them. We also assume that other countries have similar legislation.

But what we don't know is whether that legislation allows for leaving gaps, and filling those gaps at a later point in time, so for example:

10:14 INV1234 is created
10:20 INV1235 is created
10:24 Program claims the number INV1236 for 10 minutes. Tries to create INV1236, but fails to do so for some reason. Database transaction is rolled back.
10:25 INV1237 is created
10:30 INV1238 is created
10:34 The INV1236 number is freed up again and is now the first eligible free number in the pool
10:38 INV1236 is created
10:41 INV1239 is created

The end result will be a consecutive list of invoices in the system, but there will have been some "smuggling" done, which on closer inspection would be visible from the creation dates of the records. Is this, or is this not allowed? Could you point us to the legal texts in which the requirement is described?

Another suggestion by a colleague was to instead go for the simpler solution of:

10:14 INV1234 is created
10:20 INV1235 is created
10:24 Program tries to create INV1236, but fails to do so for some reason. Database transaction is rolled back. INV1236 is still created in the system, but as a dummy 0.00 invoice to a dummy customer, or even as a Cancelled invoice.
10:25 INV1237 is created
10:30 INV1238 is created
10:41 INV1239 is created

@hbrunn Note that the latter of the two solutions that was proposed by you, should we go for Cancelled invoices, is actually almost similar to a situation that you would encounter in real life with a nogap sequence. Transactions/invoices get cancelled all the time. And the records would be truly consecutive in this case, so it avoids the legal discussion as to which is the exact letter of the law. It could also be quite simple to implement that technically:

• With a separate cursor, create the invoice record as a cancelled record. Wait for completion of this transaction.
• Then in the regular process, start a transaction, delete the cancelled record and creating a new invoice record in its place, with the same number.
• Should the transaction get cancelled, the cancelled invoice remains in its place and carries the number.
• Should the transaction go through, the invoice becomes a regular valid invoice, with the number.

[thomaspaulb]

I've received reply from at least one accountant who prefers the second option:

The way I see it is that we can compare it to a manual invoice book (before systems) which will have been numbered in sequence. If I now skip a page (and invoice number) I will have record that there is no sales on that invoice number and would possibly strike through with a “cancelled” on the page to indicate that it was not used. Therefor I do not foresee a problem if we have the full sequence of our invoices with a start and end number for the period, and what we would see as “gaps” shows as “invoice not completed” with an audit trail. What I want to avoid is the system going back to a gap and then issuing it at a later transaction date, simply to fill the gaps.