Re: sequence data type

>>>>> "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)