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.klaver@aklaver.com