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

Question

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 ?

Laurenz Albe · Accepted Answer · 2024-01-18 07:04:41Z

1

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.

answered Jan 18, 2024 at 7:04

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sapta Over a year ago

Hi @Laurenz, recreating view is currently somewhat what I'm trying to avoid. there are 6 views to recreate in running production database. I'm looking tricky way to quickly alter that column, if it is possible.

Laurenz Albe Over a year ago

Well, you can have your tricky way if you insist. But dropping and creating the views is the better way. You want to prevent concurrent access while you change the data type.

Sapta Over a year ago

Update: performing the trick with another table was worked. so I guess I made a mistake previously. Thank you @Laurenz

Laurenz Albe Over a year ago

Seems like you like to live dangerously.

Sapta Over a year ago

@laurenz_albe haha, no. I was in a rush and had to do it quickly asap.

CarlosRuiz · Accepted Answer · 2024-01-22 12:47:40Z

0

As you are using iDempiere, you can change the "Length" in the dictionary, in the Table and Column window, then you can click on the "Synchronize Column" button, and iDempiere will take care of updating the column and recreating all the related views.

answered Jan 22, 2024 at 12:47

CarlosRuiz

3912 silver badges3 bronze badges

1 Comment

Sapta Over a year ago

Hello Carloz, yes indeed. but I'm running on production environment, caused long-waiting time as we have many transaction.

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related