From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Steven Hirsch <snhirsch(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Odd behavior with 'currval' |
Date: | 2018-02-08 19:35:08 |
Message-ID: | 1b8fb206-17c4-6705-b05e-1fc7045c3117@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Steven Hirsch | 2018-02-08 19:51:00 | Re: Odd behavior with 'currval' |
Previous Message | David G. Johnston | 2018-02-08 19:34:37 | Re: Odd behavior with 'currval' |