Re: Odd behavior with 'currval'

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Thu, Feb 8, 2018 at 1:17 PM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:

On Thu, Feb 8, 2018 at 1:09 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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_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@xxxxxxxxxxx

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

Function             Return Type     Description
currval(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.