Postgresql：保持2个序列同步
有没有办法在 Postgres 中保持 2 个序列同步？
我的意思是，如果我有：
table_A_id_seq = 1
table_B_id_seq = 1
如果我执行SELECT nextval('table_A_id_seq'::regclass)，
我希望table_B_id_seq采用与table_A_id_seq相同的值
显然另一边也一定是一样的。
我需要 2 个不同的序列，因为我必须破解 Django 中的一些约束（并且我无法在那里解决）。
Is there a way to keep 2 sequences synchronized in Postgres?
I mean if I have:
table_A_id_seq = 1
table_B_id_seq = 1
if I execute SELECT nextval('table_A_id_seq'::regclass)
I want that table_B_id_seq takes the same value of table_A_id_seq
and obviously it must be the same on the other side.
I need 2 different sequences because I have to hack some constraints I have in Django (and that I cannot solve there).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这两个表一定以某种方式相关吗？我会将该关系封装在包含序列的查找表中，然后用使用该查找表的视图替换您期望处理的两个表。
The two tables must be related in some way? I would encapsulate that relationship in a lookup table containing the sequence and then replace the two tables you expect to be handling with views that use the lookup table.
只需对两个表使用一个序列即可。除非您总是一次又一次地同步它们，否则您无法使它们保持同步。序列不是事务安全的，它们总是向前滚动，从不向后滚动，甚至不通过回滚。
编辑：一个序列也不起作用，不会为两个表提供相同的数字。使用子查询来获取正确的数字，并为单个表仅使用单个序列。另一个表必须使用子查询。
Just use one sequence for both tables. You can't keep them in sync unless you always sync them again and over again. Sequences are not transaction safe, they always roll forwards, never backwards, not even by ROLLBACK.
Edit: one sequence is also not going to work, doesn't give you the same number for both tables. Use a subquery to get the correct number and use just a single sequence for a single table. The other table has to use the subquery.
看到这个时我的第一个想法是你为什么真的想这样做？闻起来有点变质，有点像过期几天的牛奶。
什么场景要求这两个seq保持相同的值？
忽略我内心的“这似乎有点奇怪”的感觉，你可以尝试这个：
在 table_a 上放置一个触发器，在插入时执行此操作。
--将 b seq 设置为 a 的值。
这种方法的问题在于，假设只有对 table_a 的插入才会更改 table_a_seq 值，而没有其他操作会增加 table_a_seq。如果你能忍受这一点，这可能会以一种非常黑客的方式工作，如果这是我的决定，我不会发布到生产中。
如果您确实需要这个，为了使其更加健壮，请创建一个接口来增加 table_a_seq （例如函数）。并且只允许通过此函数操作 table_a_seq。这样就有一个接口可以增加 table_a_seq 并且你还应该把
select setval('table_b_seq',currval('table_a_seq'));进入该函数。这样无论如何，table_b_seq将始终被设置为等于table_a_seq。这意味着删除对用户 table_a_seq 的任何授权，只授予他们对新函数的执行授权。My first thought when seeing this is why do you really want to do this? This smells a little spoiled, kinda like milk does after being a few days expired.
What is the scenario that requires that these two seq stay at the same value?
Ignoring the "this seems a bit odd" feelings I'm getting in my stomach you could try this:
Put a trigger on table_a that does this on insert.
--set b seq to the value of a.
The problem with this approach is that is assumes only a insert into table_a will change the table_a_seq value and nothing else will be incrementing table_a_seq. If you can live with that this may work in a really hackish fashion that I wouldn't release to production if it was my call.
If you really need this, to make it more robust make a single interface to increment table_a_seq such as a function. And only allow manipulation of table_a_seq via this function. That way there is one interface to increment table_a_seq and you should also put
select setval('table_b_seq',currval('table_a_seq'));into that function. That way no matter what, table_b_seq will always be set to be equal to table_a_seq. That means removing any grants to the users to table_a_seq and only granting them execute grant on the new function.您可以在
Table_A上放置一个INSERT触发器，该触发器执行一些增加Table_B序列的代码。现在，每次您将新行插入Table_A时，它都会触发该触发器。You could put an
INSERTtrigger onTable_Athat executes some code that increasesTable_B's sequence. Now, every time you insert a new row intoTable_A, it will fire off that trigger.