Re: Odd behavior with 'currval'

On Thu, Feb 8, 2018 at 9:09 AM, Steven Hirsch <snhirsch(at)gmail(dot)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.

David J.