Re: Odd behavior with 'currval'

On Thu, 8 Feb 2018, Francisco Olarte wrote:

> Something must be different. As requested by others, try posting the
> SQL code chunks, more eyeballs make bugs shallower ( it's happened
> several times to me, make a typo, go over it for half an hour, grab a
> colleague, she immediately points to it )

Fair enough. Here is the DDL:

CREATE TABLE udm_asset_type_definition (
def_id BIGSERIAL NOT NULL,
def_name VARCHAR(32) NOT NULL,
PRIMARY KEY (def_id)
);

When I look at the column definition, I see:

nextval('udm_asset_type_definition_def_id_seq'::regclass)

When I look at the catalog, I can see a sequence:

udm_asset_type_definition_def_id_seq

That appears identical to the column default definition and it has the
expected 'last_value'.

Here's the odd part: If I issue

SELECT pg_get_serial_sequence('udm_asset_type_definition','def_id')

I get back NULL (doesn't matter if I qualify with schema - everything is
in a schema called 'main' and that is first on the search path). All
other sequences in the database (created exactly the same way, through
definition as 'BIGSERIAL' type) are properly found.

On a hunch, I tried 'SELECT currval(NULL)' to see if it returned '0', but
that too returns NULL. So, where is the '0' coming from when I do:

SELECT currval( pg_get_serial_sequence('udm_asset_type_definition','def_id'))

? I've already established that the inner expression evaluates to NULL!

> It shouldn't be, this I why several perople are requesting to see the
> relevant code. Experience says lots of this fails are pilot error.
>
> As an aside, with recent postgres versions you can normally use the
> returning construct to grab autogenerated id. I.e., instead of "insert
> blah-blah-blah, select currval(), whatever else" you can many times do
> "insert balh-blah-blah returning auto_gen_column, whatever else". I've
> used it a lot, and normally leads to shorter/easier/faster code.

Yes, I changed the code to 'INSERT .. RETURNING ..' and that works
correctly. But, again, not necessary for any of the other tables.

This problem is not a transient fluke - I can reproduce it in two
different databases on different servers that were created with the same
DDL.

--