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@xxxxxxxxxxx
