Re: Alter the column data type of the large data volume table.

Hi Kevin,

This sounds like a good idea, I will work hard on this idea and let you
know the result.

Most appreciated.

Kevin Brannen <KBrannen(at)efji(dot)com> 于2020年12月5日周六 上午12:04写道：

> *>From:* Olivier Gautherot <ogautherot(at)gautherot(dot)net>
>
> >>5) If you're brave enough, convert your current table as a partition
> (rename it to something like table_hist), duplicate the table model under
> the same name as now (adjusting the primary key type) and set the INHERITS
> on the primary key range. The inheritance should take care of the type
> conversion (haven't tried it but it's worth a try). If it works, you will
> reach your goal without downtime or significant overhead.
>
> >Sorry, just tried this one and it failed: type mismatch.
>
>
>
> Seems like a sound idea in general. I’d probably rename the tables, let’s
> call them “big_hist” for the old big table and “big_split” for the new
> partitioned table that being used go forward – assuming the original table
> was called “big”. Then create a View that will look at both of those but
> call it the same as the old table, and let the view do a type cast on the
> old key like big_hist.id::bigint so it matches the new type, because the
> view will probably be a union and the type need to match. That way your
> application only has to pause long enough to do a few meta-commands then it
> all can resume, and like Olivier pointed you, you can fix the data by
> moving it from big_hist to big_split in the background as you have time.
>
>
>
> I’d probably put it all in a transaction too:
>
>
>
> Create table … -- all the commands to create your patitioned table
> big_split here
>
> Begin;
>
> Alter table big rename to big_hist;
>
> Create view big select * from big_split union select id::bigint, /* other
> cols */ from big_hist;
>
> Commit;
>
>
>
> Try it on a dev system and if it works you’re off and running. I’d expect
> the view to slow things down a little, but probably not too much if you
> have good indexes. But at least you could transition without major downtime
> and then rename “big_split” back to “big” and drop “big_hist” when you’ve
> finished the transition. I might even be tempted to add a trigger so that
> all new inserts into “big” really go into “big_split” so “big_hist” doesn’t
> grow any more. Your imagination is probably the limit. 😊
>
>
>
> HTH,
>
> Kevin
>
> .
>
>
> This e-mail transmission, and any documents, files or previous e-mail
> messages attached to it, may contain confidential information. If you are
> not the intended recipient, or a person responsible for delivering it to
> the intended recipient, you are hereby notified that any disclosure,
> distribution, review, copy or use of any of the information contained in or
> attached to this message is STRICTLY PROHIBITED. If you have received this
> transmission in error, please immediately notify us by reply e-mail, and
> destroy the original transmission and its attachments without reading them
> or saving them to disk. Thank you.
>