ERROR: tables can have at most 1600 columns

[Ron St-Pierre]

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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Alvaro Herrera]

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)
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

Alvaro Herrera <al******@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

[Jaime Casanova]

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.

Thanx in advance,

Jaime Casanova

Tom Lane <tg*@sss.pgh.pa.us> wrote:Alvaro Herrera 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


---------------------------------
Do You Yahoo!?
Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo.
Visíta Yahoo! Noticias.

[Scott Marlowe]

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.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Ron St-Pierre]

Tom Lane wrote:

Alvaro Herrera <al******@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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly