Clear postgres knowledge of dropped columns

Question

I have a table in a postgres database that's being used in a testing environment where we need to add and drop several columns at a time. The problem is that postgres has a maximum of 1600 columns, and this count includes dropped columns. My table will never have 1600 total `un-dropped' columns, but over time it accumulates to more than the 1600 with drops.

I've tried using VACUUM and VACUUM FULL, and I've tried recasting an existing column as its own type (ALTER TABLE table ALTER COLUMN anycol TYPE anytype) to cause postgres to scan all of the columns and clean up the memory from dropped columns, but none of these reset postgres' `column number.'

I know that this could be solved by copying the entire table, but that has its own issues, and is in a separate question.

Do you know of a way to make postgres forget it had dropped columns?

I know that postgres was not designed for applications like this, but I'm not going to get into why we chose to implement it this way. If you have an alternate tool to use, I'd be interested to hear about it, but I'd still like to find a solution to this.

You said you've tried `VACUUM` on the table. What about `VACUUM FULL`? Or perhaps, `ALTER TABLE table ALTER COLUMN anycol TYPE anytype;`, changing a column to the same type it already has. — Wayne Conrad, Jul 07 '11 at 04:34
As you already found out there are at least two ways1) : CREATE TABLE new AS select * FROM the_table; rename things; The othe one is pg_dump - t thetable; drop the table; recreate the table from the pg_dump results. In BOTH cases you'll have to reconstruct the FK constraints (which is a bit simpler in the second case) — joop, Mar 29 '16 at 16:55

score 6 · Accepted Answer · answered Jul 07 '11 at 09:42

6

This is not possible, other than by recreating the table, as you already found out.

Otherwise the database system would have to keep track somehow when the storage used by a dropped column has been cleared everywhere, and then renumber the attributes. That would be incredibly expensive and complicated.

answered Jul 07 '11 at 09:42

Peter Eisentraut

35,221
12
85
90

That's good to know. Now I just need to figure out how to [effectively copy the table](http://stackoverflow.com/questions/6601978/completely-copying-a-postgres-table-with-sql) – Erik Jul 07 '11 at 11:30
3

To me, this looks like a job for VACUUM FULL (including ANALYZE?), it rewrites the table anyway and needs a table lock as well. An update of pg_class whouldn't be too expensive, compared to all other work it has to do. – Frank Heikens Jul 07 '11 at 17:05

score 0 · Answer 2 · answered Jul 07 '11 at 05:10

0

The systemtable pg_attribute still shows old (deleted) columns. I don't know why, but it looks like a bug to me.

SELECT 
  relnatts, 
  attname, 
  attisdropped 
FROM 
  pg_class 
    JOIN pg_attribute att ON attrelid = pg_class.oid 
WHERE 
  relname = 'your_table_name';

Could you send a bugreport, including a simple example, to pgsql-bugs@postgresql.org or http://www.postgresql.org/support/submitbug ?

answered Jul 07 '11 at 05:10

Frank Heikens

117,544
24
142
135

If memory serves, it's actually there for transaction-aware alter table statements. But yeah, it should drop the row eventually. :-) – Denis de Bernardy Jul 07 '11 at 09:35

Clear postgres knowledge of dropped columns

2 Answers2