DBD Errors when run on a table with 1600 columns

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?

Comments

  • 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.

  • 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
  • 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 .


  • 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

  • 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. 
  • 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
  • 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.
  • Hi Daniel ,

    I see . . .

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file