Why is the Hibernate default generator for PostgreSql "SequenceGenerator", not "IdentityGenerator"?

Question

The default identifier generator for Postgresql in Hibernate is SequenceGenerator [1]. i.e. Hibernate will do SELECT nextval('hibernate_sequence') to generate an ID before doing an INSERT foo (id, ...) VALUES (123, ...) on session commit.

However, PostgreSql supports autoincrement id columns (see e.g. [2]), and the default generator for all other databases which support autoincrement is to use that feature [3], and to perform inserts omitting the id value and to query the database for the new id (before session commit, but within the session's transaction).

I have seen some recent discussion [4] suggesting that the former strategy is better in general, due to the insert-before-session-commit mismatch.

If SequenceGenerator is better (as per [4]), why is it not the default for databases which support it (see [3])?

If IdentityGenerator is better, why does PostgreSql explicitly choose SequenceGenerator when Postgres does support the former (as per [2])?

I tried to find the history of the decision to override the default in the Postgres dialect (see [1]), but I couldn't find the relevant commit in GitHub. I have followed the code back to the SVN repository, but the trail goes cold where the PostgreSQLDialect file is added at r11563 with an unhelpful commit message of "maven migration" [5]. I can't seem to follow the history any further back. Can anyone find the commit which added this override? Perhaps there is more info in the commit message.

Thanks in advance.

[1] https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/dialect/PostgreSQL81Dialect.java#L267

[2] PostgreSQL Autoincrement

[3] https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/dialect/Dialect.java#L639

[4] http://nhforge.org/blogs/nhibernate/archive/2009/03/20/nhibernate-poid-generators-revealed.aspx

[5] https://source.jboss.org/browse/Hibernate/core/trunk/core/src/main/java/org/hibernate/dialect/PostgreSQLDialect.java?focusedRev=14993&fromRev=11563&toRev=14993#r14993

Zealot

I assume this is because the serial datatype is just a "sequence in disguise". – Burck

That doesn't explain it -- all autoincrement columns are sequences in disguise. – Zealot

no, not for SQL Server or MySQL where you can not get the next value without doing that insert (which is the big difference between a sequence based solution and an "auto-increment") – Burck

Answer 1

Maybe because afterInsert generators are generally broken for PG in NHibernate because it uses OracleStyle out-parameter style which is not supported by npgsql-ADONET driver which returns the result as query result and not out parameter.

SQL: INSERT INTO .... returning id into nhoutparameter; :nhoutparameter = null;

using Oracle this works

command.Execute();
object id =  command.Parameter["nhoutparameter"].Value;
Assert.NotNull(id);

in PG not. It should be

object id = command.ExecuteScalar();

Basidium

This makes sense for NHibernate. Do you know if the same applies to Hibernate? – Zealot

maybe. i do not have the environment here to test. For NH i just got the source and ran the UnitTests against PG. All postInsertGenerator-tests failed. You could do the same with Hibernatesource from github running against PG. – Basidium

Answer 2

This is just speculation since I cant find good feature / version docs on postgres but...

Maybe the postgres dialect was added before serial was implemented, stable, widely used, or the developers who implemented the dialect were aware of it. Changing the default identifier generator for a dialect would be a breaking change and those are bad.

Sketch