In Postgres the Connection.prepareStatement() calls that return
generated keys are not supported. Because of this we need a workaround
to get the generated keys for inserts into tables that use sequences to
auto-generate their primary keys.
Up to now, we were selecting the current value of the sequence
immediately after the insert. I thought this was safe because
transactions should be isolated. But now I realize this could
potentially fail because the default transaction isolation is "read
committed". Thus if another connection inserts into the same table,
causing the sequence to increment, if it commits before we read the
sequence value, we might read the wrong value (the value as incremented
by the other transaction, not the value as it was for our own insert).
What is the best workaround for this? Ideally the JDBC calls should be
supported because (1) we would only need a single round trip and (2)
it's transactionally safe. But without that, what is the recommended
best practice workaround?
I believe I could set the transaction isolation level to "serializable",
but this seems heavy handed. Is that really the best option?
Thanks,
Michael Clements
Principal Architect, Actional Products
http://www.progress.com
mclement@progress.com