Re: Odd behavior with 'currval'

On Thu, Feb 8, 2018 at 1:09 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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_as
>> set_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
>
>

*I believe your problem is in your usage.In order for currval(regclass) to
work, you must first do a SELECT nextval(regclass) in your _current
transaction_!https://www.postgresql.org/docs/9.6/static/functions-sequence.html
<https://www.postgresql.org/docs/9.6/static/functions-sequence.html>Function
Return Type Descriptioncurrval(regclass) bigint
Return value most recently obtained with nextval for specified sequence*--

*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.