NH-1316 - PostgreSQL dialect use of lastval to retrieve last inserted "id" not safe with Triggers

[nhibernate-bot]

Stephane Alie created an issue — 14th May 2008, 8:00:30:

From the PostgreSQL documentation(1) the lastval function definition is : "Return value most recently obtained with nextval for any sequence"

The important part is "for any sequence". The problem arise when an insertion happens on table1 (id column that points to sequence1) that has a trigger firing on insertion that inserts a row to table2 which also has a serial column (id column that points to sequence2). What happens is that the lastval function will return the value of the last modified sequence which in this case would be sequence2, therefor corrupting our Entity.

A solution for 1.2.x was proposed by dbachmann on the NHibernate forums(2) to use the PostgreSQL "RETURNING" clause on inserts(3).

public override SqlString AddIdentitySelectToInsert(SqlString insertSql, string identityColumn, string tableName)
{
   return insertSql.Append(" returning ").Append(identityColumn);
}

The only side effect is that insert rules on views need to be defined with a RETURNING * to work properly.

There is still the issue of version 2.0 which I'm not sure what needs to be done.

ref:
(1) http://www.postgresql.org/docs/8.3/interactive/functions-sequence.html
(2) http://forum.hibernate.org/viewtopic.php?p=2385225#2385225
(3) http://www.postgresql.org/docs/8.3/interactive/sql-insert.html

---

Fabio Maulo added a comment — 5th June 2009, 12:59:35:

At least this issue need a specific test case for PostgreSQL.

---

Shaun Wilde added a comment — 20th August 2010, 3:18:06:

I have raised a similar issue https://nhibernate.jira.com/browse/NH-2204

Sorry for the x-posting but not sure how to link issues.

---

hidegh added a comment — 4th March 2015, 15:26:53:

It's something POSTGRE specific. So dialect should be changed :D

According to stockexchange answers ** lastval() should not be used** (because of triggers)!
Solution (possibly the only): use RETURNING generatedKeyColumnName (see: http://stackoverflow.com/questions/17819001/postgres-not-returning-lastval-properly)

Other link suggested the use of currVal(tableName), but if my insert triggers an insert inside the same table (and trigger do checks to avoid recursivity), I think the same issue may occure even with the using of currVal(tableName) (see: http://pear.php.net/bugs/bug.php?id=19918)

---

Oskar Berggren added a comment — 20th November 2016, 13:20:08:

I've added the test case from the duplicate NH-2204 to NHibernate master, commit a6bedf4.
Before trying to fix it, we should look into porting all the dialect refactoring from Hibernate. In fact, I think it's already fixed there. That would be 5.0 material.