Thread: ERROR: tables can have at most 1600 columns

I found this error in /var/log/messages yesterday after a cron job
wouldn't complete:
   STATEMENT:  ALTER TABLE victoria.eodData DROP COLUMN tickDate;
   ERROR:  tables can have at most 1600 columns
   STATEMENT:  ALTER TABLE victoria.eodData ADD COLUMN tickerID INTEGER;
   ERROR:  tables can have at most 1600 columns
   ...etc...

The columns didn't exist at the time I tried to drop them, and \dt
showed that the table only contained the normal dozen columns. Because I
use the COPY command I remove columns to match the input from an ftp'ed
file, add the columns, continue processing, etc. After dropping and then
re-creating the table the error didn't recur and processing continued.

My question is this: every night the database is vacuumed-full-analyze:
wouldn't that prevent this condition from happening? Or is there some
other regular maintenance I should be running?

I'll be away for the next week but I look forward to reading any insight
on this when I return.

Thanks
Ron

-- running Postgresql 7.4.2 on debian stable

On Sun, Jun 27, 2004 at 11:11:32AM -0700, Ron St-Pierre wrote:
> I found this error in /var/log/messages yesterday after a cron job
> wouldn't complete:
>   STATEMENT:  ALTER TABLE victoria.eodData DROP COLUMN tickDate;
>   ERROR:  tables can have at most 1600 columns
>   STATEMENT:  ALTER TABLE victoria.eodData ADD COLUMN tickerID INTEGER;
>   ERROR:  tables can have at most 1600 columns
>   ...etc...
>
> The columns didn't exist at the time I tried to drop them, and \dt
> showed that the table only contained the normal dozen columns.

Have you done the DROP COLUMN/ADD COLUMN cycle to this table more than,
say, 1500 times?  Because a dropped column is actually only hidden from
the user, but it's still present to the system and it will still affect
the 1600 limit.

Dropping the table and restoring from a backup would bring it to sanity,
as you found out.  And it will probably also get you back some
performance (though it may be so small a gain that you may not notice).

> My question is this: every night the database is vacuumed-full-analyze:
> wouldn't that prevent this condition from happening?

Nope.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"The only difference is that Saddam would kill you on private, where the
Americans will kill you in public" (Mohammad Saleh, 39, a building contractor)

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> On Sun, Jun 27, 2004 at 11:11:32AM -0700, Ron St-Pierre wrote:
>> STATEMENT:  ALTER TABLE victoria.eodData DROP COLUMN tickDate;
>> ERROR:  tables can have at most 1600 columns
>> STATEMENT:  ALTER TABLE victoria.eodData ADD COLUMN tickerID INTEGER;
>> ERROR:  tables can have at most 1600 columns

> Have you done the DROP COLUMN/ADD COLUMN cycle to this table more than,
> say, 1500 times?  Because a dropped column is actually only hidden from
> the user, but it's still present to the system and it will still affect
> the 1600 limit.

That is a good theory, but it doesn't quite explain why Ron's getting
the error from DROP COLUMN --- AFAICS, the places that would issue such
an error won't get called in that path.

I tried to reproduce this and could not: after 1600 cycles of adding and
dropping a column, I did indeed start to get "tables can have at most
1600 columns" from ADD, but DROP continued to behave normally.

Ron, are you sure these errors were coming from the DROPs and not only
the ADDs?  Can you exhibit a test case?

            regards, tom lane

On Mon, 2004-06-28 at 14:37, Jaime Casanova wrote:
> Hi all,
>
> There is a way to actually eliminate those dropped tables so they
> don't affect the 1600 columns limit? I know it's very difficult to end
> up with this problem but apparently "it is" posible.
>
You may want to try recreating the table with select into when you're
changing it.  Of course, you'll have to recreate all the fks, indexes,
or other dependent objects.

Tom Lane wrote:

>Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
>
>
>>On Sun, Jun 27, 2004 at 11:11:32AM -0700, Ron St-Pierre wrote:
>>
>>
>>>STATEMENT:  ALTER TABLE victoria.eodData DROP COLUMN tickDate;
>>>ERROR:  tables can have at most 1600 columns
>>>STATEMENT:  ALTER TABLE victoria.eodData ADD COLUMN tickerID INTEGER;
>>>ERROR:  tables can have at most 1600 columns
>>>
>>>
>
>
>
>>Have you done the DROP COLUMN/ADD COLUMN cycle to this table more than,
>>say, 1500 times?  Because a dropped column is actually only hidden from
>>the user, but it's still present to the system and it will still affect
>>the 1600 limit.
>>
>>
>
>That is a good theory, but it doesn't quite explain why Ron's getting
>the error from DROP COLUMN --- AFAICS, the places that would issue such
>an error won't get called in that path.
>
>I tried to reproduce this and could not: after 1600 cycles of adding and
>dropping a column, I did indeed start to get "tables can have at most
>1600 columns" from ADD, but DROP continued to behave normally.
>
>Ron, are you sure these errors were coming from the DROPs and not only
>the ADDs?  Can you exhibit a test case?
>
>            regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>
>
>
I re-checked the logs and saw that the error only ocurred on trying to
add the column, sorry for the cut-and-paste mistake. But before that I
tried a test script and had the same result that Tom did. I think that
I'll take Scott's advice and probably use INSERT INTO to re-create the
table each time and avoid the 1600 column error. I am currently using
TRUNCATE to clear the table before each use but it obviously doesn't
re-set the column count.

Thanks again for your help.

Ron