postgresqlpostgresql-13

Updating pg_attribute did not effect on actual table (postgresql 13.8)

i had 2 postgresql server with different version (9.6 and 13.9). I'm trying alter some varchar column from 20 to 50. As many views depend on it, I'm trying to avoid using UPDATE syntax in actual table by 'hacking' pg_atribute as explain here https://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data/

checking size

idempiere=# SELECT atttypmod FROM pg_attribute
idempiere-# WHERE attrelid = 'C_BPartner'::regclass
idempiere-# AND attname = 'x_taxid';
 atttypmod
-----------
        24
(1 row)

Update to 50

idempiere=# UPDATE pg_attribute SET atttypmod = 50+4
idempiere-# WHERE attrelid = 'C_BPartner'::regclass
idempiere-# AND attname = 'x_taxid';
UPDATE 1
idempiere=#

I can update with more than 20 chars at postgresql 9.6 server, but not at server version 13.8

No error occurs.

Any clue ?

Solution

  • The trick will work on PostgreSQL v13; you must have made a mistake.

    Which brings me to my main message: don't do that. Directly manipulating the system catalogs exposes you to the risk of data corruption. Moreover, an UPDATE like the one you are performing will neither protect the table against concurrent use, nor will it invalidate plans or cached metadata in database sessions (which may be your problem).

    Use the proper statement:

    ALTER TABLE "C_BPartner" ALTER x_taxid TYPE varchar(50);

    That statement will be fast, as it does not have to rewrite the table. Simply drop the dependent views and create them again after the ALTER TABLE. That will be fast too and should not be a problem. Just run the script that performs CREATE OR REPLACE VIEW for all your views, a script that you have checked into your version control system.