Re: Odd behavior with 'currval'

On 02/08/2018 08:09 AM, Steven Hirsch 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).

Can we see the table schema and the currval code?

Is there anything in the Postgres logs that might help?

>
> 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!
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com