DBD Errors when run on a table with 1600 columns

Andrew_Bacon

While executing DBD on a v7.0 schema, the following error message was returned:

        [  0%] Error during design...Design errored .


The error is:
Design did not complete successfully.  ERROR - A table/projection/view can only have up to 1600 columns -- this create statement has 1601

The schema contains a table with 1600 columns.

Does this mean that the practical limit of columns per table is 1599?

id10t

Hi!

Does this mean that the practical limit of columns per table is 1599?

Yes and no - depends on your vision.
Probably DBD creates an additional column and with this column "table" exceeds a columns limit.

I did a test on my local machine: create table with 1600 columns + run DBD.
Didn't get an error - all succeeded.

Check in QUERY_REQUESTS what DDL caused to issue

select request from query_requests where request_type = 'DDL';

Regards.

Andrew_Bacon

Thanks for the query, the culprit appears to be

CREATE LOCAL TEMP TABLE <table>_ENCODINGSAMPLE_0 ON COMMIT PRESERVE ROWS  AS SELECT *, DENSE_RANK() OVER (ORDER BY <all columns>) AS DENSE_RANK FROM <schema>.<table> ORDER BY DENSE_RANK

This must be a required step for the DBD in v7.0, even when "Update Statistics" isn't selected.

I should add that I ran the DBD without the following options selected:

• Optimise with Queries
• Update Statistics
• Deploy Design

Cheers,
Andrew

eli_revach

Hi ,
Its seems like the limitaion is 1599 as Vertica automaticly create an internal column name as epoch , for each projection you create . You can view that by selecting the content of the column_storage  system view .

id10t

Hi!

Nope, i checked it, it isn't an epoch column.

select
    count(*) 
from 
    columns 
where 
    table_name = 'wide' 
        and
    column_name not ilike '%epoch%';
***

 count 
-------
  1600
(1 row)

PS
Here is a simple script: http://pastebin.com/UU5CvWzW

daniel@synapse:/tmp$ ./ddl.py -h
usage: ddl.py [-h] [-C] [-d]

optional arguments:
  -h, --help  show this help message and exit
  -C, --ddl   returns CREATE TABLE statement
  -d, --data  returns 1600 ints delimited by pipe

id10t

Hi!

Im afraid only an official support can help you.
I don't know about an option to increase columns limit but for me a bug.
By "System limits" - 1600 columns support without any notes like "if DBD creates an additional...", etc.

If you are an EE customer so open a ticket. My opinion - its a bug.
If you are a CE customer - you are at troubles. Will hope on better.

PS
And if DBD will decide to create 1599 columns? We don't know DBD internals, we can't modify it, we can't control DBD except provided API, actually we don't know what DBD do, so its a bug. 

Andrew_Bacon

Thanks, luckily this doesn't affect us in production, I discovered it by chance on a POC system. For safety, we've revised down the 1600 column limit to 1599. Usually our tables don't get near to 1600 columns wide.

Cheers,
Andrew

id10t

Hi!

I see that DBD fails on creating encoding. For next time you can try do it manually - create optimized projections with encoding AUTO and run dbd_design_projection_encodings function: http://vertica-forums.com/viewtopic.php?f=3&t=1599&p=5275#p5275
May be it will not create an additional column, i don't know.

PS
It is still a bug (for my opinion) and doesn't matter is it POC or not, CE or EE customer and it should be fixed.

eli_revach

Hi Daniel ,

I see . . .

It will be interesting to see your query results in  Andrew’s environment  .