Re: Odd behavior with 'currval'

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

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.

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

--