unexpected error " tables can have at most 1600 columns"

2015-04-13 17:57 GMT+02:00 Day, David <dday@redcom.com>:

Situation

I have a co-developer installing a new Virtual Machine and encountering a
postgres error during the installation.

One of our SQL patch files is failing unexpectedly.

The patch is attempting to add columns to a table, The table involved
currently has only 2 columns,

Interactively I can generate the same error in his current state.

psql -h ohio -U redcom ace_db

psql (9.3.6)

Type "help" for help.

ace_db=# select * from log.conference_history;

conf_id | max_size

---------+----------

(0 rows)

ace_db=# ALTER TABLE log.conference_history ADD talker_limit integer
DEFAULT 0;

ERROR: tables can have at most 1600 columns

ace_db=#

ace_db=#

There can be removed (invisible columns)

select attname from pg_attribute where attrelid = 'test'::regclass and
attnum > 0;

postgres=# select attname from pg_attribute where attrelid =
'test'::regclass and attnum > 0;
┌─────────┐
│ attname │
╞═════════╡
│ a │
│ c │
│ d │
└─────────┘
(3 rows)

alter table test drop column a, drop column c;

postgres=# select attname from pg_attribute where attrelid =
'test'::regclass and attnum > 0;
┌──────────────────────────────┐
│ attname │
╞══════════════════════════════╡
│ ........pg.dropped.1........ │
│ ........pg.dropped.2........ │
│ d │
└──────────────────────────────┘
(3 rows)

postgres=# select count(*) from pg_attribute where attrelid =
'test'::regclass and attnum > 0 and attisdropped;
┌───────┐
│ count │
╞═══════╡
│ 2 │
└───────┘
(1 row)

So maybe it can be a reason of this issue?

Pavel

Pavel,

Thanks so much. This seems to help explain the problem.

I can say that the patch file had a later statement with an error related to search_path setup.
Given that the logic that applies patches repeat attempts for an extended period of time,
I speculate that the rollback of the patch leaves these invisible columns remaining?
Otherwise I fail to see from where these columns originate.

When I drop the database and recreate it with the schema reference issues resolved.
( ie. Issues related to search_path setup ). The database builds without issue.

These patches were not an issue for ongoing developers because at some point
In the installation the default search_path gets setup so that this err in the
patch writing is masked.

Thanks so much.

Regards

Dave Day

select attname from pg_attribute where attrelid='log.conference_history'::regclass and attnum > 0;
attname
---------------------------------
........pg.dropped.11........
........pg.dropped.13........
........pg.dropped.14........
........pg.dropped.15........
........pg.dropped.41........
........pg.dropped.56........
........pg.dropped.42........
........pg.dropped.43..

select count(*) from pg_attribute where attrelid='log.conference_history'::regclass and attnum > 0 and attisdropped;
count
-------
1598

From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: Monday, April 13, 2015 12:06 PM
To: Day, David
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] unexpected error " tables can have at most 1600 columns"

2015-04-13 17:57 GMT+02:00 Day, David <dday@redcom.com<mailto:dday@redcom.com>>:
Situation

I have a co-developer installing a new Virtual Machine and encountering a postgres error during the installation.
One of our SQL patch files is failing unexpectedly.

The patch is attempting to add columns to a table, The table involved currently has only 2 columns,
Interactively I can generate the same error in his current state.

psql -h ohio -U redcom ace_db
psql (9.3.6)
Type "help" for help.

ace_db=# select * from log.conference_history;
conf_id | max_size
---------+----------
(0 rows)

ace_db=# ALTER TABLE log.conference_history ADD talker_limit integer DEFAULT 0;
ERROR: tables can have at most 1600 columns
ace_db=#
ace_db=#

There can be removed (invisible columns)

select attname from pg_attribute where attrelid = 'test'::regclass and attnum > 0;

postgres=# select attname from pg_attribute where attrelid = 'test'::regclass and attnum > 0;
┌─────────┐
│ attname │
╞═════════╡
│ a │
│ c │
│ d │
└─────────┘
(3 rows)

alter table test drop column a, drop column c;

postgres=# select attname from pg_attribute where attrelid = 'test'::regclass and attnum > 0;
┌──────────────────────────────┐
│ attname │
╞══════════════════════════════╡
│ ........pg.dropped.1........ │
│ ........pg.dropped.2........ │
│ d │
└──────────────────────────────┘
(3 rows)

postgres=# select count(*) from pg_attribute where attrelid = 'test'::regclass and attnum > 0 and attisdropped;
┌───────┐
│ count │
╞═══════╡
│ 2 │
└───────┘
(1 row)
So maybe it can be a reason of this issue?
Pavel

Puzzled ?

Any thoughts ?

Regards

Dave Day