Change column type VARCHAR to TEXT in PostgreSQL without lock table

Question

I have a "Parent Table" and partition table by year with a lot column and now I need change a column VARCHAR(32) to TEXT because we need more length flexibility.

So I will alter the parent them will also change all partition.

But the table have 2 unique index with this column and also 1 index.

This query lock the table:

ALTER TABLE my_schema.my_table
ALTER COLUMN column_need_change TYPE VARCHAR(64) USING 
column_need_change :: VARCHAR(64);

Also this one :

ALTER TABLE my_schema.my_table
ALTER COLUMN column_need_change TYPE TEXT USING column_need_change :: TEXT;

I see this solution :

UPDATE pg_attribute SET atttypmod = 64+4
WHERE attrelid = 'my_schema.my_table'::regclass
AND attname = 'column_need_change ';

But I dislike this solution.

How can change VARCHAR(32) type to TEXT without lock table, I need continue to push some data in table between the update.

My Postgresql version : 9.6

EDIT :

This is the solution I ended up taking:

ALTER TABLE my_schema.my_table
ALTER COLUMN column_need_change TYPE TEXT USING column_need_change :: TEXT;

The query lock my table between : 1m 52s 548ms for 2.6 millions rows but it's fine.

Answer 1

The supported and safe variant is to use ALTER TABLE. This will not rewrite the table, since varchar and text have the same on-disk representation, so it will be done in a split second once the ACCESS EXCLUSIVE table lock is obtained.

Provided that your transactions are short, you will only experience a short stall while ALTER TABLE waits for all prior transactions to finish.

Messing with the system catalogs is dangerous, and you do so on your own risk.

You might get away with

UPDATE pg_attribute
SET atttypmod = -1,
    atttypid  = 25
WHERE attrelid = 'my_schema.my_table'::regclass
  AND attname = 'column_need_change';

But if it breaks something, you get to keep the pieces…