Very annoying ! Please Remove these feature !!!

Adrian_Oprea_1

 

 

    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 ?  

Navin_C

Any example ?

Adrian_Oprea_1

 

 

 

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

Navin_C

Hi,

 

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

For bigger definitions, still a problem.

Adrian_Oprea_1

   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 

DerekW

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.

Adrian_Oprea_1

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.