Re: Odd behavior with 'currval'

On 02/08/2018 11:12 AM, Steven Hirsch wrote:
> On Thu, 8 Feb 2018, Adrian Klaver wrote:
> 
>> hplc=> \d student_attendance_attendance_id_seq
>>     Sequence "public.student_attendance_attendance_id_seq"
>>    Column     |  Type   |                Value
>> ---------------+---------+--------------------------------------
>> sequence_name | name    | student_attendance_attendance_id_seq
>> last_value    | bigint  | 39590
>> start_value   | bigint  | 1
>> increment_by  | bigint  | 1
>> max_value     | bigint  | 9223372036854775807
>> min_value     | bigint  | 1
>> cache_value   | bigint  | 1
>> log_cnt       | bigint  | 0
>> is_cycled     | boolean | f
>> is_called     | boolean | t
>> Owned by: public.student_attendance.attendance_id
> 
> 
>> Assuming you showed the complete output I am not seeing the Owned by: 
>> for your sequence. I would do the \d on one of your sequences that 
>> 'works', I am guessing you will see Owned by: .
>>
>> To correct see:
>>
>> https://www.postgresql.org/docs/10/static/sql-altersequence.html
>>
>> "OWNED BY table_name.column_name
>> OWNED BY NONE
>>
>>    The OWNED BY option causes the sequence to be associated with a 
>> specific table column, such that if that column (or its whole table) 
>> is dropped, the sequence will be automatically dropped as well. If 
>> specified, this association replaces any previously specified 
>> association for the sequence. The specified table must have the same 
>> owner and be in the same schema as the sequence. Specifying OWNED BY 
>> NONE removes any existing association, making the sequence 
>> “free-standing”.
> 
> Agggh.  That's it!  I'll fix the ownership.
> 
> So, a few questions:
> 
> 1. How on earth did this happen? I do not recall doing any manual 
> fiddling with either database - they were (as far as I know) built from 
> the same DDL. We may never have an answer for this.  Being human, who 
> knows what I may or may not have done 4 months ago...

https://www.postgresql.org/docs/10/static/datatype-numeric.html#DATATYPE-SERIAL

"The data types smallserial, serial and bigserial are not true types, 
but merely a notational convenience for creating unique identifier 
columns (similar to the AUTO_INCREMENT property supported by some other 
databases). In the current implementation, specifying:

CREATE TABLE tablename (
     colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
     colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
"

So the only way I know this can happen is:

ALTER SEQUENCE seq_name OWNED BY NONE;

> 
> But,
> 
> 2. Why is the currval() function being so blasted dumb?  If 
> 'pg_get_serial_sequence' cannot resolve the sequence, it returns NULL. 
> As such, shouldn't the outer currval() also be returning NULL?  I cannot 
> imagine a rationale for the current behavior.

Someone else will need to comment on this as I have no idea.

> 
> THANKS to everyone who chimed in on this.  I was beginning to think I 
> was losing my marbles.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com