Very annoying ! Please Remove these feature !!!

 

 

    Why is everytime i write a view or some object definition in Vertica and then i try to see the written definition

Vertica translates the definition by adding datatype casts, a billion parentheses :( , trasform in stmt into array defintion !!!

  This is a pain in the b..utt when you try review the code or make changes !!!

 

Please some smart person educate me in why this is required ?  

Comments

  • Vertica Customer

    Any example ?

  •  

     

     

    My Create view defintition

    create view testview as 
    with a as (
    select 1 as id
    )
    select * from a where id not in (2,3,4,5,6)

     

    Exported definition:

    CREATE  VIEW public.testview AS
    SELECT a.id
    FROM ( SELECT 1 AS id
    FROM v_catalog.dual) a
    WHERE (a.id <> ALL (ARRAY[2, 3, 4, 5, 6]));

    SELECT MARK_DESIGN_KSAFE(1);

    This is just tiny tiny view ! imagina a 5000 k lines of code ! :( 

     

    just waisted 4 h running after a loose character ......

  • Vertica Customer

    Hi,

     

    For a smaller definition (< 65000 characters), you can use view system table to get the structure.

    For bigger definitions, still a problem.

  •    Not sure you understood my initial question. 

     

      I know how to get the definition(it can be done in more than 5 ways :) ), my issue is that the Vertica db engine re-writes the definition of views making them really hard to read.

     

     I just wanted to know why ? 

     

      - imagine doing maintainance on other Developers code and you acually see different code from the depoly documents --- it is a nightmare !! 

     

    • getdate() transforms in statement_timestamp()
    • not in  transforms in <>
    • ('val','val1')  transforms in ALL (ARRAY[val1, val2])
    • where and val1=val2 and val2=val3 transforms in where and (val1=val2) and (val2=val3)
    • select date_part('year',getdate()) transforms in SELECT date_part('year'::varchar(4), (statement_timestamp())::timestamp) AS date_part

    this are only few examples 

  • I recognize this issue, and it's very annoying. We actually stopped using views at all, partially because of this reason. As a workaround we stored al 'original' view definitions in a version control environment like Git, and used a script to recreate all views on an hourly or daily basis.

  • I agree , i do that with all my other DBs that i manage , but doing consultancy on top of other guys enviroments is so hard.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
You can use Markdown in your post.