I have a body of code using JDBC to work with a PostgreSQL 9.6 database.
All tables use 'SERIAL' or 'BIGSERIAL' types to generate ids. All are
working correctly in terms of using the next value as a default.
However, reading back the most recently applied (currval) value is failing
for one table, where it always returns '0'. Note that the table data shows
the expected value when queried by SELECT! It is only the currval()
function that is wrong. I am properly guarding for SQL exceptions and
none are being thrown.
The code being used in the failing case is not the slightest bit different
from the working cases in terms of structure and transaction control -
only the SQL, column count, etc. is different (but correctly formed and in
all other ways functional).
I'm not sure where to start debugging this. Can anyone give me even a
working theory to explain how returning a bogus value is possible? When I
look at the sequences in pgAdmin, they are as expected in terms of
ownership, etc. And, again, the table IS getting the correct value.
Thanks much for any ideas!
--