如何重新启动由Hibernate处理的PostgreSQL序列？ [英] How to restart PostgreSQL sequences handled by Hibernate?

问题描述

我的任务是在PostgreSQL数据库中为许多不同的串行列重新启动一个序列。通常，我会简单地使用：

I've been tasked with restarting a sequence for many different serial columns in a PostgreSQL database. Normally, I would simply use:

ALTER SEQUENCE serial RESTART WITH 105;

然而，似乎Hibernate正在用来处理数据库序列。我对Hibernate一无所知，但我的理解是，hibernate_sequence是所有表的全局序列。这是正确的吗？

However, it seems that Hibernate is being used to handle the database sequences. I really don't know anything about Hibernate, but my understanding is that hibernate_sequence is a global sequence for all tables. Is that correct?

我假设我需要做以下事情，然后：

I'm assuming that I would need to do the following, then:

ALTER SEQUENCE hibernate_sequence RESTART WITH 105;

但我不确定会有什么后果。假设我有表A，B，C和D.这些表中的每一个都有一个类型为serial的ID列。请问上面的hibernate_sequence上的SQL语句是否重新启动了所有表的ID列？

But I am not sure what the consequence will be. Let's assume I have Tables A, B, C, and D. Each of those tables has an ID column of type serial. Will the above SQL statement on hibernate_sequence restart the ID column for ALL of the tables?

谢谢！

Thanks!

推荐答案

首先，这里有一个潜在的问题：你正在处理序列的价值，就像它们意味着什么。查看答案的结尾。

First, there's an underling problem here: You're treating the value of sequences like they mean something. See the end of the answer.

现在，使用Hibernate，它取决于用户如何配置映射。一个配置良好的Hibernate实例将使用与其他一切相同的顺序。

Now, with Hibernate, it depends on how the user has configured the mappings. A well configured Hibernate instance will use the same sequences everything else does.

不幸的是，大多数映射并不是很好 - 或者说，我认为Hibernate的默认行为是可怕的，应该总是被覆盖。所有表使用的缺省序列确实是共享的 hibernate_sequence 。但是，完全有可能使用它们自己的序列和其他使用共享 hibernate_sequence 的表的表，或者使所有表正确使用它们自己的序列。

Unfortunately, most mappings aren't very good - or rather, I think Hibernate's default behaviour is horrible and should always be overridden. The default sequence used is indeed a shared hibernate_sequence for all tables. However, it's entirely possible to have some tables using their own sequences, and other tables using the shared hibernate_sequence, or to have all tables properly using their own sequences.

要启用明确的答案，您需要发布映射。

To enable a definitive answer you'd need to post your mappings.

（s）你想改变ID的生成是在他们的Hibernate映射中为他们的表使用正确的序列，你可以继续前进，然后 ALTER SEQUENCE 那个序列。如果映射是通过JPA完成的，那么使用 @SequenceGenerator 和 @GeneratedValue 并使用 GenerationType .SEQUENCE 。如果它是通过 orm.xml ， hbm.xml 或旧式Hibernate注释完成的，则需要检查手册。或者，如果Hibernate是写入数据库的唯一客户端，请查看序列以查看它们是否正在使用 SELECT * FROM sequence_name 。

If the table(s) you want to change the ID generation of are using the correct sequences for their tables in their Hibernate mappings, you can just go ahead and ALTER SEQUENCE that sequence. If your mappings are via JPA, that's done with a @SequenceGenerator and @GeneratedValue with GenerationType.SEQUENCE. If it's done via orm.xml, hbm.xml or the old-style Hibernate annotations you'll need to check the manual. Or, if Hibernate is the only client that writes to the DB, look at the sequences to see if they're going up using SELECT * FROM sequence_name.

如果感兴趣的表的映射使用共享的 hibernate_sequence ，那么您可能无法在不修复的情况下执行您想要的操作Hibernate映射。您需要更改映射，以便感兴趣的表的映射使用不同的序列，然后`LOCK TABLE ，并将这些序列的起始点设置为这是目前表中最大的一行。

If the mappings for the table(s) of interest are using the shared hibernate_sequence you probably can't do what you want without fixing the Hibernate mappings. You'd need to change the mappings so the mapping for the table(s) of interest use a different sequence, then`LOCK TABLE and set the start point for those sequences to the greatest row currently in the table.

序列：您为什么需要更改或重新启动序列？这并不重要，因为合成主键只是一个无意义的数字，您可以将它与其他无意义的数字进行比较。你试图解决什么问题？

Sequences: Why do you need to change or restart the sequence? It shouldn't matter, since a synthetic primary key is just a meaningless number that you compare for equality with other meaningless numbers. What's the problem you're trying to solve?

这篇关于如何重新启动由Hibernate处理的PostgreSQL序列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！