We want to extend/widen the column without outage. But as column widening takes ACCESS EXCLUSIVE LOCK, we have seen noticeable pause on SELECT/INSERTS. This behavior was more noticeable in tables which has composite Foreign keys. .We tried doing it like below which resulted in minimizing the outage but still noticeable pause for INSERTS/UPDATE can be seen.
column_test=# select version();
version
---------------------------------------------------------------------------------------------------------------
EnterpriseDB 9.5.5.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit
(1 row)
column_test=#
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