ActiveRecord Minor PostgreSQL Insert Bug and Fix

Databases
1

We were getting an incorrect record id returned after inserting a new record using ActiveRecord with PostgreSQL. Turns out that “SELECT LASTVAL();” should not be utilized to get the last insert id as it returns the wrong id if the table your are using has triggers. We have not tested this thoroughly, but the fix seems to be simple as long as your primary key is always named “id”. (I’m sure Bob and his team could make this work for tables that have a differently named pk.)

Here are the changes that we made to the PostgreSQLDatabaseAdapter.InsertRecord method:

[code] dim sql as string
sql = "INSERT INTO " + oTableInfo.sTableName
sql = sql + “(” + Join(arsField, “,”) + “)”
sql = sql + " VALUES "
sql = sql + “(” + Join(arsPlaceholder, “,”) + “) RETURNING id” <change

dim stmt as PreparedSQLStatement
stmt = db.Prepare(sql)

dictFieldValue = BindValues(stmt, oRecord, aroField)

Dim oRst as Recordset = stmt.SQLSelect <change
if db.Error then
raise new BKS_ActiveRecord.DatabaseException(db)
end if

dim iRecordID as Int64 = oRst.IdxField(1).Int64Value <change
dictFieldValue.Value( oTableInfo.piPrimaryKey.Name ) = iRecordID

'store the newly saved property values
dictSavedPropertyValue = dictFieldValue

return iRecordID[/code]

2

Have you sent this information over to @Bob Keeney ? if not it wont get fixed.

3

Ah, thanks for finding that. However, there’s no guarantee that the ID is the primary key. But we should be able to add something since we do know the primary key (it’s in the TableInfo).

I have already forwarded this info to my developer working on the next update.

FWIW, we generally discourage developers from using a plain ‘id’ for the primary key because it means nothing at first glance. In other words if I saw ‘id’ my first question is the id for what? Use some more descriptive.

In joins and views having multiple id fields they need to be aliased to make sense of them anyway, so we include the table name for the Primary Key field name. So the Person table primary key would be Person_ID, Invoice table would be Invoice_ID, etc. It also helps with foreign keys too because you’d end up aliasing them anyway if you have more than one.

4

This is fixed and will be in the next public release. Thanks for letting us know! I can’t say we’ve used PostgreSQL with Triggers before.

5

once again @Bob Keeney and team are awesome.

6

As is Brandon for figuring it out…

7

Interesting. At first I didn’t buy it, but then I found postgresql - Postgres not returning lastval() properly - Stack Overflow .