Hi All,
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.
Environment Details :
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
<https://www.postgresql.org/docs/9.2/static/sql-altertable.html> ... 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
<https://www.postgresql.org/docs/9.2/static/sql-altertable.html> 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
