Re: Odd behavior with 'currval'

On Thu, Feb 8, 2018 at 1:17 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

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

*FYI, as an alternative, you can also do:SELECT last_value FROM
udm_asset_type_definition_def_id_seq;*

*That should always work.*

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