如何创建PostgreSQL分区序列? [英] How to create a PostgreSQL partitioned sequence?
问题描述
是否有一种简单的(即非hacky)且无竞争条件的方式在PostgreSQL中创建分区序列。示例:
Is there a simple (ie. non-hacky) and race-condition free way to create a partitioned sequence in PostgreSQL. Example:
在Issue中使用正常序列:
Using a normal sequence in Issue:
| Project_ID | Issue |
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 2 | 4 |
在Issue中使用分区序列:
Using a partitioned sequence in Issue:
| Project_ID | Issue |
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
推荐答案
我不相信有一种简单的方法是与常规序列一样容易,因为:
I do not believe there is a simple way that is as easy as regular sequences, because:
- 一个序列仅存储一个数字流(下一个值等)。您需要为每个分区分配一个分区。
- 序列具有特殊处理,可绕过当前事务(以避免争用条件)。不使用诸如dblink之类的技巧就很难在SQL或PL / pgSQL级别上复制它。
- DEFAULT列属性可以使用简单的表达式或函数调用,例如
nextval('myseq')
;但是它不能引用其他列来通知函数值应该来自哪个流。
- A sequence stores only one number stream (next value, etc.). You want one for each partition.
- Sequences have special handling that bypasses the current transaction (to avoid the race condition). It is hard to replicate this at the SQL or PL/pgSQL level without using tricks like dblink.
- The DEFAULT column property can use a simple expression or a function call like
nextval('myseq')
; but it cannot refer to other columns to inform the function which stream the value should come from.
您可以制作出有效的方法,但是您可能不会认为这很简单。依次解决上述问题:
You can make something that works, but you probably won't think it simple. Addressing the above problems in turn:
- 使用表存储所有分区的下一个值,其格式类似于
multiseq(partition_id,next_val)
。 -
写一个
multinextval(seq_table,partition_id)
函数执行以下操作:
- Use a table to store the next value for all partitions, with a schema like
multiseq (partition_id, next_val)
. Write a
multinextval(seq_table, partition_id)
function that does something like the following:
- 创建与当前事务无关的新事务(一种方法是通过dblink;我相信其他一些服务器语言可以更轻松地完成此操作。)
- 锁定
seq_table
中提到的表。 - 使用递增的值更新分区ID为
partition_id
的行。 (或者,如果不存在,则插入一个值为2的新行。) - 提交该事务并返回先前存储的ID(或1)。
- Create a new transaction independent on the current transaction (one way of doing this is through dblink; I believe some other server languages can do it more easily).
- Lock the table mentioned in
seq_table
. - Update the row where the partition id is
partition_id
, with an incremented value. (Or insert a new row with value 2 if there is no existing one.) - Commit that transaction and return the previous stored id (or 1).
在项目表上创建一个插入触发器,该触发器使用对 multinextval('projects_table',NEW.Project_ID)$的调用c $ c>用于插入。
我自己并未使用整个计划,但是我尝试了类似的方法每个步骤都单独进行。 multinextval
函数和触发器的示例可以在您尝试执行此操作时提供。
I have not used this entire plan myself, but I have tried something similar to each step individually. Examples of the multinextval
function and the trigger can be provided if you want to attempt this...
这篇关于如何创建PostgreSQL分区序列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!