Re: [HACKERS] sequence data type

>>>>> "Daniel" == Daniel Verite <daniel@manitou-mail.org> writes:
Daniel> Consider the case of a table with a SERIAL column which laterDaniel> has to become a BIGINT due to growth.
Currentlya user wouldDaniel> just alter the column's type and does need to do anything withDaniel> the sequence.
 
Daniel> With the patch, it becomes a problem because
Daniel> - ALTER SEQUENCE seqname MAXVALUE new_valueDaniel> will fail because new_value is beyond the range of INT4.
Daniel> - ALTER SEQUENCE seqname TYPE BIGINTDaniel> does not exist (yet?)
Daniel> - DROP SEQUENCE seqname  (with the idea of recreating theDaniel> sequence immediately after) will be rejected
becausethe tableDaniel> 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)