Drop column doesn't remove column references e

问题:

I have a table that contained 1600 columns and would like to add more fields in that but as per the database rule the more fields not allowed to be created because the higher limit reached.

So I decided to drop few unwanted fields from the table and I did it. Again I tried to add few fields in that table but it's raise the same error that 1600 columns are there you can't add more.

I gone through other tables of postgresql "pg_attribute" and all those fields are there and having delete parameter = True.

What I have tried so far

Drop Constraints Take table data into another table Truncate Table Re-Create Constraints Re-Copy data to the main table.

But still the dropped columns are there in pg_attributes table.

I also tried to remove that records from pg_attribute but then it gives me error like that.

ERROR:  catalog is missing 1 attribute(s) for relid 208996

********** Error **********

ERROR: catalog is missing 1 attribute(s) for relid 208996
SQL state: XX000

Why we have these many columns reason is

We have odoo-magento (postgresql db in used by odoo) integration and in magento there are more than 60000 products and having 3000 unique attributes, so in connector all these attributes are created as fields in product table and I already synced magento -> odoo products and 1600 limit reached. Now it won't me allow to drop fields from that table.

I have fixed that issue in magento-odoo connector and it will only sync required attributes but what should I do for already synced fields how can I remove it ????

Even dropping a table is not a proper way because it's huge and very important table so I can take any risk with that.

What I want to do

I want to simply remove those columns from the table and will need to be added few other columns instead.

Is there any other possible solution for this issue ?

Any help would be really appreciated.

回答1:

Never mess with pg_attribute directly. If you have done so, it is probably time to restore from a backup.

When a column is dropped, PostgreSQL does not actually remove it, but changes the name and marks it as dropped.

CREATE TABLE testtab (
   id integer PRIMARY KEY,
   dropme text NOT NULL,
   val text NOT NULL
);

ALTER TABLE testtab DROP dropme;

SELECT attname, attnum, attisdropped
FROM pg_attribute
WHERE attrelid = 'testtab'::regclass
   AND attnum > 0
ORDER BY attnum;

┌──────────────────────────────┬────────┬──────────────┐
│           attname            │ attnum │ attisdropped │
├──────────────────────────────┼────────┼──────────────┤
│ id                           │      1 │ f            │
│ ........pg.dropped.2........ │      2 │ t            │
│ val                          │      3 │ f            │
└──────────────────────────────┴────────┴──────────────┘
(3 rows)

So I guess that dropped column still counts towards the column limit.

I can think of one, not very comfortable, way to get rid of that:

BEGIN;
CREATE TABLE testtab_2 (LIKE testtab INCLUDING ALL);
INSERT INTO testtab_2 SELECT * FROM testtab;
DROP TABLE testtab;
ALTER TABLE testtab_2 RENAME TO testtab;
COMMIT;

回答2:

As documented in the manual

The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated.

To force immediate reclamation of space occupied by a dropped column, you can execute one of the forms of ALTER TABLE that performs a rewrite of the whole table. This results in reconstructing each row with the dropped column replaced by a null value.