From: | Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> |
---|---|
To: | "Daniel Verite" <daniel(at)manitou-mail(dot)org> |
Cc: | "Peter Eisentraut" <peter(dot)eisentraut(at)2ndquadrant(dot)com>, "Steve Singer" <steve(at)ssinger(dot)info>, "pgsql-hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: sequence data type |
Date: | 2017-01-11 01:16:57 |
Message-ID: | 87vatmwgv3.fsf@news-spur.riddles.org.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
>>>>> "Daniel" == Daniel Verite <daniel(at)manitou-mail(dot)org> writes:
Daniel> Consider the case of a table with a SERIAL column which later
Daniel> has to become a BIGINT due to growth. Currently a user would
Daniel> just alter the column's type and does need to do anything with
Daniel> the sequence.
Daniel> With the patch, it becomes a problem because
Daniel> - ALTER SEQUENCE seqname MAXVALUE new_value
Daniel> will fail because new_value is beyond the range of INT4.
Daniel> - ALTER SEQUENCE seqname TYPE BIGINT
Daniel> does not exist (yet?)
Daniel> - DROP SEQUENCE seqname (with the idea of recreating the
Daniel> sequence immediately after) will be rejected because the table
Daniel> depends on the sequence.
Daniel> What should a user do to upgrade the SERIAL column?
Something along the lines of:
begin;
alter table tablename alter column id drop default;
alter sequence tablename_id_seq owned by none;
create sequence tablename_id_seq2 as bigint owned by tablename.id;
select setval('tablename_id_seq2', last_value, is_called) from tablename_id_seq;
drop sequence tablename_id_seq;
alter table tablename alter column id type bigint;
alter table tablename alter column id set default nextval('tablename_id_seq2');
commit;
Not impossible, but not at all obvious and quite involved. (And -1 for
this feature unless this issue is addressed.)
--
Andrew (irc:RhodiumToad)
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2017-01-11 01:18:17 | Re: RustgreSQL |
Previous Message | Michael Paquier | 2017-01-11 01:09:14 | Re: Replication/backup defaults |