PostgreSQL生成没有间隙的序列 [英] postgresql generate sequence with no gap

问题描述

我必须/必须为发票创建唯一的ID.我有一个表ID和此唯一编号的另一列.我使用序列化隔离级别.使用

I must / have to create unique ID for invoices. I have a table id and another column for this unique number. I use serialization isolation level. Using

  var seq = @"SELECT invoice_serial + 1 FROM  invoice WHERE ""type""=@type ORDER BY invoice_serial DESC LIMIT 1";

没有帮助，因为即使使用FOR UPDATE，它也不会像序列化级别那样读取正确的值.

Doesn't help because even using FOR UPDATE it wont read correct value as in serialization level.

只有解决方案似乎会放一些重试代码.

Only solution seems to put some retry code.

推荐答案

序列不会生成无间隙的数字集，实际上没有办法使它们这样做，因为回滚或错误会使用"序列号

Sequences do not generate gap-free sets of numbers, and there's really no way of making them do that because a rollback or error will "use" the sequence number.

不久前，我写了一篇关于此的文章.它是针对Oracle的，但实际上是关于无间隙数字的基本原理，我认为这在这里同样适用.

I wrote up an article on this a while ago. It's directed at Oracle but is really about the fundamental principles of gap-free numbers, and I think the same applies here.

好吧，这又发生了.有人问如何实现产生差距的要求无数的数字和成群的反对者纷纷要求他们说(在这里我稍作解释)这将破坏系统性能，这几乎不是一个有效的要求，写要求的人都是白痴等等.............................................................

Well, it’s happened again. Someone has asked how to implement a requirement to generate a gap-free series of numbers and a swarm of nay-sayers have descended on them to say (and here I paraphrase slightly) that this will kill system performance, that’s it’s rarely a valid requirement, that whoever wrote the requirement is an idiot blah blah blah.

正如我在线程上指出的那样，有时真正的法律要求是生成无间隙的一系列数字.在英国注册超过200万个增值税(营业税)的组织的发票号有此要求，其原因很明显:这使得隐藏来自税务部门的收入更加困难.我已经看到有评论说这是西班牙和葡萄牙的一项要求，如果在其他许多国家也没有要求，我也不会感到惊讶.

As I point out on the thread, it is sometimes a genuine legal requirement to generate gap-free series of numbers. Invoice numbers for the 2,000,000+ organisations in the UK that are VAT (sales tax) registered have such a requirement, and the reason for this is rather obvious: that it makes it more difficult to hide the generation of revenue from tax authorities. I’ve seen comments that it is a requirement in Spain and Portugal, and I’d not be surprised if it was not a requirement in many other countries.

因此，如果我们接受这是一个有效的要求，那么在什么情况下无差数序列*是个问题?集体思维通常会让您相信它一直存在，但实际上，这只是在非常特殊的情况下的潜在问题.

So, if we accept that it is a valid requirement, under what circumstances are gap-free series* of numbers a problem? Group-think would often have you believe that it always is, but in fact it is only a potential problem under very particular circumstances.

1. 这一系列数字必须没有空格.
2. 多个流程创建与该号码相关联的实体(例如发票).
3. 编号必须在创建实体时生成.

如果必须满足所有这些要求，那么您的应用程序中就有一个序列化点，稍后我们将进行讨论.

If all of these requirements must be met then you have a point of serialisation in your application, and we’ll discuss that in a moment.

首先，让我们讨论一下实现一系列数字要求的方法，如果您可以放弃其中的任何一个要求.

First let’s talk about methods of implementing a series-of-numbers requirement if you can drop any one of those requirements.

如果您的数字序列可能有间隔(并且您有多个需要立即生成数字的过程)，请使用Oracle Sequence对象.它们具有很高的性能，并且已经很好地讨论了可以预料到的差距的情况.通过设计来最大程度地减少在生成数字和提交事务之间出现过程失败的可能性的设计努力来最小化跳过的数字的数量并不是很困难.

If your series of numbers can have gaps (and you have multiple processes requiring instant generation of the number) then use an Oracle Sequence object. They are very high performance and the situations in which gaps can be expected have been very well discussed. It is not too challenging to minimise the amount of numbers skipped by making design efforts to minimise the chance of a process failure between generation of the number and commiting the transaction, if that is important.

如果您没有多个创建实体的流程(并且您需要立即生成一系列无间隙的数字)(例如批量生成发票的情况)，那么您已经知道序列化.这本身可能不是问题，并且可能是执行所需操作的有效方法.在这种情况下，生成无间隙数非常简单.您可以使用多种技术读取当前最大值，并将增量值应用于每个实体.例如，如果您要从临时工作表向发票表中插入新的一批发票，则可能:

If you do not have multiple processes creating the entities (and you need a gap-free series of numbers that must be instantly generated), as might be the case with the batch generation of invoices, then you already have a point of serialisation. That in itself may not be a problem, and may be an efficient way of performing the required operation. Generating the gap-free numbers is rather trivial in this case. You can read the current maximum value and apply an incrementing value to every entity with a number of techniques. For example if you are inserting a new batch of invoices into your invoice table from a temporary working table you might:

insert into
  invoices
    (
    invoice#,
    ...)
with curr as (
  select Coalesce(Max(invoice#)) max_invoice#
  from   invoices)
select
  curr.max_invoice#+rownum,
  ...
from
  tmp_invoice
  ...

当然，您可以保护您的流程，以便一次只能运行一个实例(如果使用的是Oracle，则可能使用DBMS_Lock)，并使用唯一的密钥禁忌来保护invoice#，并可能使用以下命令检查缺失值:如果您真的很在意的话，请单独编写代码.

Of course you would protect your process so that only one instance can run at a time (probably with DBMS_Lock if you're using Oracle), and protect the invoice# with a unique key contrainst, and probably check for missing values with separate code if you really, really care.

如果不需要立即生成数字(但是您需要它们无间隙且多个进程生成实体)，则可以允许生成实体并提交事务，然后将数字生成保留给单个批处理作业.实体表的更新，或插入到单独的表中.

If you do not need instant generation of the numbers (but you need them gap-free and multiple processes generate the entities) then you can allow the entities to be generated and the transaction commited, and then leave generation of the number to a single batch job. An update on the entity table, or an insert into a separate table.

那么，如果我们需要通过多个过程立即生成无间隙数列的三连冠呢?我们所能做的就是尽量减少序列化的过程，我提供以下建议，并欢迎任何其他建议(或者当然是反建议).

So if we need the trifecta of instant generation of a gap-free series of numbers by multiple processes? All we can do is to try to minimise the period of serialisation in the process, and I offer the following advice, and welcome any additional advice (or counter-advice of course).

1. 将当前值存储在专用表中.请勿使用序列.
2. 通过将其封装在函数或过程中，确保所有进程使用相同的代码来生成新数字.
3. 使用DBMS_Lock序列化访问数字生成器，确保每个系列都有其自己的专用锁.
4. 通过释放提交锁，将锁保持在系列生成器中，直到完成实体创建事务为止
5. 将数字的生成延迟到最后一个可能的时刻.
6. 考虑在生成数字之后并在提交完成之前发生意外错误的影响-应用程序会正常回滚并释放锁，还是将锁保持在序列生成器上，直到会话稍后断开连接?无论使用哪种方法，如果事务失败，则必须将序列号返回到池".
7. 您可以将整个内容封装在实体表的触发器中吗?您可以将其封装在表格或其他API调用中，以插入行并自动提交插入吗?

原始文章

这篇关于PostgreSQL生成没有间隙的序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！