>>>>> "Daniel" == Daniel Verite <dan...@manitou-mail.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> 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:

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');

Not impossible, but not at all obvious and quite involved. (And -1 for
this feature unless this issue is addressed.)

Andrew (irc:RhodiumToad)

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to