如何创建一个分区的PostgreSQL序列？

Question

有没有一种简单（即非hacky）且无竞争条件的方法在PostgreSQL中创建分区序列。 例如：

在Issue中使用普通序列：

| Project_ID | Issue |
| 1          | 1     |
| 1          | 2     |
| 2          | 3     |
| 2          | 4     |

在Issue中使用分区序列：

| Project_ID | Issue |
| 1          | 1     |
| 1          | 2     |
| 2          | 1     |
| 2          | 2     |

- FooBar

---

我很乐意知道是否有一个优雅的解决方案来解决这个问题。这里的解决方案：https://dev59.com/NVPTa4cB1Zd3GeqPiVYh 不错，但它是否是事务安全的？ - Atorian

Answer 1

我认为没有像常规序列一样简单的方法，因为：

1. 序列仅存储一个数字流（下一个值等），而你需要每个分区都有一个。
2. 序列具有特殊处理方式，可以绕过当前事务（以避免竞争条件）。在SQL或PL/pgSQL级别上复制这一点很难，除非使用诸如dblink之类的技巧。
3. DEFAULT列属性可以使用简单表达式或函数调用，例如nextval('myseq')；但它不能引用其他列来通知函数应该从哪个流中获取值。

你可以创建一个可行的方案，但可能不会觉得它简单。依次解决上述问题：

1. 使用一个表来存储所有分区的下一个值，其模式类似于multiseq (partition_id, next_val)。

2. 编写一个multinextval(seq_table, partition_id)函数，执行以下操作：

   1. 创建一个新事务，独立于当前事务（其中一种方法是通过dblink；我相信其他服务器语言可以更轻松地完成此操作）。
   2. 锁定seq_table中提到的表。
   3. 更新分区ID为partition_id的行，增加一个值。（如果不存在现有行，则插入新行并设置值为2。）
   4. 提交该事务并返回先前存储的ID（或1）。

3. 在你的项目表上创建一个插入触发器，使用调用multinextval('projects_table', NEW.Project_ID)进行插入。

我自己没有使用过整个计划，但我已经尝试过每个步骤类似的方案。如果你想尝试这个方案，可以提供multinextval函数和触发器的示例...

- Edmund

Answer 2

我有同样的需求，我的解决方案基本上与@Edmund上面说的一样。

到目前为止我发现唯一的缺点是，由于我的查询查找序列列的MAX()值，在理论上，如果您删除最新行（通过这样做，您会减少最大记录的序列号）并添加一个新行，则该数字可能会被重新使用。

在将来的某个迭代中，我可能会创建一个专用表来存储每个单独序列的最大值，但我想对如何在重负载下工作进行一些性能测试。我正在有效地替换本机序列而没有它们的所有优化，这必须带来缺点。

---

在我的情况下，我在许多具有不同分区列的列上使用序列号，因此我创建了一个通用函数，我从触发器中调用它。

以下是该函数。您需要hstore扩展程序才能运行它。

CREATE OR REPLACE FUNCTION nssequence_nextval()
RETURNS trigger AS $$
DECLARE
    query text;
    nextval bigint;
BEGIN
    IF TG_NARGS != 2 THEN
        RAISE EXCEPTION '% did not supply nssequence_nextval with the required arguments.', TG_NAME;
    END IF;

    query := 'SELECT COALESCE(MAX(%I)::text, ''0'')::bigint + 1 FROM %I.%I WHERE organisation_id = $1.organisation_id AND %I = $1.%I'::text; -- In my context, every row has an organisation_id as my app is multi-tenant-capable. Adjust this query to your needs.
    EXECUTE format(
        query,
        TG_ARGV[0], -- MAX(%I)::text, the column holding the sequence
        TG_TABLE_SCHEMA, TG_TABLE_NAME, -- FROM %I.%I - the table we are working with
        TG_ARGV[1], TG_ARGV[1] -- %I = $1.%I -- the column with the sequence namespace. Ideally a parent entity's UUID.
    ) USING NEW INTO nextval;

    -- The property name is the first argument of the trigger function, so we need hstore to set it.
    -- See https://dev59.com/Imsz5IYBdhLWcg3wsaPN#7782641
    NEW := NEW #= hstore(TG_ARGV[0]::text, nextval::text);

    RETURN NEW;
END
$$ LANGUAGE 'plpgsql';

这是每列的触发器（将方括号中的内容替换为实际名称）:

CREATE OR REPLACE TRIGGER [nssequence_trigger_name]
BEFORE INSERT ON [schema.table]
FOR EACH ROW
EXECUTE PROCEDURE nssequence_nextval('[sequence_column_name]', '[partition_column_name]')

顺便说一下，我已经自动化了这个过程。我在每个序列列上使用一个列注释，例如nssequence:partition_column_name。我还编写了一个脚本，查找所有具有此类注释的列，并为它们添加触发器。

以下是查找所有序列列的查询：

SELECT
    DISTINCT pgd.objoid, -- only used for filtering out duplicates
    c.table_schema,
    c.table_name,
    c.column_name,
    pgd.description
FROM pg_catalog.pg_statio_all_tables as st
INNER JOIN pg_catalog.pg_description pgd ON (
    pgd.objoid = st.relid
)
INNER JOIN information_schema.columns c ON (
    pgd.objsubid   = c.ordinal_position
    AND c.table_schema = st.schemaname
    AND c.table_name   = st.relname
)
INNER JOIN information_schema.tables t ON (
    t.table_type = 'BASE TABLE'
)
WHERE
    t.table_schema != 'information_schema'
    AND t.table_schema NOT LIKE 'pg_%'
    AND pgd.description LIKE 'nssequence%'

最后，我还设置了一个清理机制，用于查找在不存在的列上创建的触发器或不再表示为序列号的触发器，但这可能对本答案来说过于详细了。

- Jan Klan