How to restart PostgreSQL sequences handled by Hibernate?

Question

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;

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;

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!

Answer 1

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

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.

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.

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.

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?