On Thu, Feb 8, 2018 at 9:09 AM, Steven Hirsch <snhirsch@gmail.com> wrote:
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.
Its seems that whatever name you are passing into currval must match an existing sequence but it doesn't match the sequence name that is attached to the table.col default expression in question.
search_path considerations might play a role if its not a simple copy-paste type error...but its hard to blindly suggest potential reasons.