Обсуждение: Column widening without outage
Steps Followed :
a. ALTER TABLE FINTRANS DROP CONSTRAINT FK_FINTRANS_SHOPTRANS;
b. ALTER TABLE FINTRANS ADD CONSTRAINT FK_FINTRANS_SHOPTRANS
FOREIGN KEY (MERCHANTID, SHOPTXNO)
REFERENCES SHOPTRANS (MERCHANTID, SHOPTXNO)
MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION NOT VALID;
c. ALTER TABLE fintrans ALTER COLUMN merchantid TYPE VARCHAR(255);
d. ALTER TABLE shoptrans ALTER COLUMN merchantid TYPE VARCHAR(255);
e. ALTER TABLE FINTRANS VALIDATE CONSTRAINT FK_FINTRANS_SHOPTRANS;
I have few queries on above problem, -
1. is there any way by which we can do the widening of column without outage.
2. does ALTER TABLE ALTER COLUMN do re validation of all foreign keys again?
3.In this section of the Postgres documentation https://www.postgresql.org/docs/9.2/static/release-9-2.html
it says -
E.21.3.4.2. ALTER
· Reduce need to rebuild tables and indexes for certain ALTER TABLE ... ALTER COLUMN TYPE operations (Noah Misch)
Increasing the length limit for a varchar or varbit column, or removing the limit altogether, no longer requires a table rewrite. Similarly, increasing the allowable precision of a numeric column, or changing a column from constrained numeric to unconstrained numeric, no longer requires a table rewrite. Table rewrites are also avoided in similar cases involving the interval, timestamp, and timestamptz types.
· Avoid having ALTER TABLE revalidate foreign key constraints in some cases where it is not necessary (Noah Misch)
so, in what circumstances ALTER TABLE will avoid revalidating foreign keys ??
Your help will be much appreciated.
Regards,
Aniruddha