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 ?  


  • Options
    Navin_CNavin_C Vertica Customer

    Any example ?

  • Options




    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]));


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


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

  • Options
    Navin_CNavin_C Vertica Customer



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

    For bigger definitions, still a problem.

  • Options

       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 

  • Options

    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.

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