Re: Odd behavior with 'currval'

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

What if you do?:

SELECT * FROM udm_asset_type_definition_def_id_seq;

SELECT currval('udm_asset_type_definition_id_seq');

Also what happens if you do:

pg_dump -d db_name -U some_user -s -t udm_asset_type_definition > out.sql

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

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