I’m upgrading to v4 so it’s time to address some things I worked around in v3.
The first is shown in the attached stack overflow question. Our postgres tables have audit triggers which can influence the value returned by lastval().
The best way to handle getting the new id is to add “RETURNING id”, where “id” is the name of the key column, to the end of the insert. But this will require access to the model to get the correct column name (account_id, order_id, product_id). CURRVAL() is another alternative but also requires model information.
Any advice on what we could do?
In v3, I brought in the servicestack auth services and customized with this function.
public static TReturn GetLastId<TModel, TReturn>(this IDbConnection db)
{
var table = OrmLiteConfig.DialectProvider.NamingStrategy.GetTableName(ModelDefinition<TModel>.Definition.ModelName);
var column = OrmLiteConfig.DialectProvider.NamingStrategy.GetColumnName(ModelDefinition<TModel>.Definition.PrimaryKey.FieldName);
const string sql = “SELECT currval(pg_get_serial_sequence(@table,@column))”;
var id = db.QueryScalar<TReturn>(sql, new {table, column});
return id;
}
You can maybe change what command is used to retrieve the last insert id with something like:
PostgreSqlDialect.SelectIdentitySql = “return id”;
This would override the default “SELECT LASTVAL()” sql used in PostgreSql, so you may want to wrap it in an extension method that sets it back.
Jeff Gabhart:
The default base provider will put a semicolon between the insert and the select identity sql which won’t work out.
BUT
I see that I can override InsertAndGetLastInsertId<T>(IDbCommand dbCmd) too and that has T so I can access the model definition.
Might be getting somewhere…
Jeff Gabhart:
My v4 upgrade in July was a trial run, and now I’m upgrading for real.
Here is what I ended up using.
public override long InsertAndGetLastInsertId<T>(IDbCommand dbCmd)
{
var modelDefinition = GetModel(typeof (T));
var table = NamingStrategy.GetTableName(modelDefinition.ModelName);
var column = NamingStrategy.GetColumnName(modelDefinition.PrimaryKey.FieldName);
//dbCmd.CommandText += “; SELECT currval(pg_get_serial_sequence(’{0}’, ‘{1}’));”.Fmt(table, column);
dbCmd.CommandText += " RETURNING {0}".Fmt(column);
return dbCmd.LongScalar();
}
In our case, these methods for getting the generated Id are more reliable due to audit triggers on our tables. On an insert, a trigger fires which also inserts an audit row in a different table. The problem is that LASTVAL() will return the Id from the audit table and not the one you are actually interested in.
If you think this should be included in the default Postgres dialect, I can create a pull request.
It sounds like it’s the better option (http://stackoverflow.com/a/2944481/85785), do you know what DB version this was added?
Actually their versioned docs makes it easy to find when, it wasn’t in 8.1 and was added in 8.2: http://www.postgresql.org/docs/8.1/interactive/sql-insert.html
As 8.1 still fairly current I’ve defaulted to using RETURNING but made it configurable in this commit: https://github.com/ServiceStack/ServiceStack.OrmLite/commit/bbbb7ff840693abbc37688f97c8380d0fc08b7a7
Jeff Gabhart:
Awesome, thank you, Demis!