GNOME Bugzilla – Bug 143597
postgres: get_last_insert_id() gives strange result.
Last modified: 2008-09-05 12:16:13 UTC
I expect gda_connection_get_last_insert_id() to provide the generated value of the "serial" field of a new record, when I add a record. Instead it gives some other number. This seems to be the same number that we see here in psql, so maybe it means something: murrayc=# SELECT * FROM tbltestglom; tbltestglom_id | field2 | field4 | field1 | field3 ----------------+--------+--------+--------+-------- 3 | 44 | yadda | | 0 | | yadda | | 4 | 44 | yadda | | 5 | 44 | yadda | | 6 | 44 | yadda | | 7 | 44 | yadda | | (6 rows) murrayc=# INSERT INTO tbltestglom ( field2 ) VALUES (55); INSERT 17200 1 murrayc=# SELECT * FROM tbltestglom; tbltestglom_id | field2 | field4 | field1 | field3 ----------------+--------+--------+--------+-------- 3 | 44 | yadda | | 0 | | yadda | | 4 | 44 | yadda | | 5 | 44 | yadda | | 6 | 44 | yadda | | 7 | 44 | yadda | | 8 | 55 | yadda | | (7 rows) tbltestglom_id is a serial column. I expect get_last_insert_id() to return 8, but instead it returns the 17200.
The current implementation returns the OID of the last inserted row (which is an internal number which postgres assigns to any database object), it's not the value of the "serial" field. The problem is that postgres uses the concept of sequences which mysql does not yet have. There are some problems with the specification of the gda_connection_get_last_insert_id() method: for example if I insert a row in a table containing two "auto increment" fields, which value should I return? So I think we should modify the signature of gda_connection_get_last_insert_id() to add the table name and the field name for which we want the last inserted value. Otherwise we can't make something which will work all the time. Also this way it's possible to retreive the value of the last inserted row for any given field. What do you think?
Yes, that would be a better signature (though the table name might be provided by the data_model argument). However, I'm not confident that this can be implemented with either postgres or mysql. Anyway, I think the function signature is a different bug.
Its more difficult when you have more than one primary key that is a sequence. The oid gets around this problem. Though other databases like mysql dont have this so it returns the last inserted id as a work around for it. Been able to get the last oid is still useful, but i think if you want to replace it you should have a call to return the values of the primary keys of the last inserted record.
get_last_insert_id(field_name) might be most obvious.
Latest revisions on trunk allow one obtain the values of each of the table's fields after a successfull INSERT statement has been executed, see the gda_connection_statement_execute() and gda_connection_statement_execute_non_select() methods. Closing this bug.