Postgres序列不同步

Question

我正在使用Bucardo和Postgres运行多主服务器设置。

我发现我的一些表序列彼此不同步。特别是自动递增的ID。

例如：

db1 - table1

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')

新行的ID为1。 db2 - table1

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')

新行的ID为1。

在db2上，新行的ID应该是2，因为Bucardo已经将数据从db1复制到了db2，但是db2的自增基于：

nextval('oauth_sessions_id_seq'::regclass)

如果我们查看"oauth_sessions_id_seq"，我们会发现最后一个值是0。

呼...有意义吗？

无论如何，我能做以下哪些事情吗？

1. 用bucardo复制会话表，这样每个数据库的会话就可以共享了吗？

2. 操作上述默认自增函数以考虑表中现有项的最大数量？

如果您有更好的想法，请随意提出。如有问题，请问，感谢任何帮助。

- outrunthewolf

---

“正确”的解决方案是让Bucardo在同步表格时同时同步序列（它本身就是一个自由可操纵的对象）。不过我不知道这是否可能。 - IMSoP

是的，我同意，我不知道是否有像公共或私有表这样的东西，因为Bucardo似乎无法看到序列表以进行同步。 - outrunthewolf

Answer 1

你需要改变ID生成方法，因为FAQ中的评论指出Bucardo没有解决方案。

Bucardo能够复制DDL吗？

不能，Bucardo依靠触发器，而Postgres还没有提供DDL触发器或系统表上的触发器。

由于Bucardo使用触发器，它无法“看到”序列更改，只能复制表中的数据。序列是有趣的对象，不支持触发器，但可以手动更新它们。我想在INSERT之前添加下面的代码，但仍可能存在问题。

SELECT setval('oauth_sessions_id_seq', (SELECT MAX(did) FROM distributors));

更多信息请参见此问题。

虽然我不完全了解所有涉及的问题，但您可以手动执行最大计算并在重试循环中执行插入操作。如果您实际上正在两个数据库上进行插入并允许Bucardo复制，则我怀疑它是否有效，但是如果您可以保证一次只有一个DB更新，则可以尝试像 UPSERT retry loop这样的东西。此帖子了解更多信息。该循环的“核心”可能看起来像这样：

INSERT INTO  distributors (did, dname) 
    VALUES ((SELECT max(did)+1 FROM distributors), 'XYZ Widgets');

- Dwayne Towell

---

太好了。那么，Bucardo能否通过调度定期复制序列表？还是你知道有改变自动ID生成的方法？我不认为我可以只把SELECT语句作为默认值吧？ - outrunthewolf

据我所知，序列与表不同，它们具有特殊属性，使它们能够在多线程环境下保持唯一性。没有简单的方法可以将它们镜像到其他服务器上。您需要实现不同的方法，或者在同一个数据库上执行所有插入操作。 - Dwayne Towell

好的。我的自增设置为nextval('oauth_sessions_id_seq'::regclass)。我不能做类似于nextval(max('oauth_sessions.id'))这样的事情吗？那样会抵消我的问题。 - outrunthewolf

你不能将聚合函数放在默认表达式中。 - Dwayne Towell

Answer 2

无论是哪种数据库（PostgreSQL、Oracle等），都为每个具有主键的表创建了动态序列。 大多数序列在发生大量数据导入或某些人手动修改表序列时会失去同步。

解决方案：我们唯一能够重置序列的方法是通过获取PK表的最大值并将序列下一个值设置为它。

以下查询将列出您DB模式中创建的所有序列：

SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';

SELECT MAX('primary_key') from table;

SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);

- Guru Vamsi