Value too long varchar(275) error with src/AdminViews/v_generate_tbl_ddl.sql

[mattmc3]

When running v_generate_tbl_ddl.sql on certain databases, the script errors with the message:

ERROR: value too long for type character varying(275)

When hacking at the script, I discovered that changing line 77 from this:
,'\t,' + pg_get_constraintdef(con.oid) AS ddl
to this:
,'\t,' + pg_get_constraintdef(con.oid)::VARCHAR(8000) AS ddl
addresses the problem for my use case. Unfortunately, I cannot speak to whether this would be the optimal way to solve this for all conditions due to the script's close ties with the deep inner workings of Redshift.

[ericfe]

Have you tried with other values ( like 1024 instead of 8000 ) ?

[mattmc3]

8000 is semi-arbitrary. The size just has to be large enough to hold the text of a long FOREIGN KEY object name. 1024 would work until you had a DDL generation for a line needing 1025 characters.

On Oct 31, 2015, at 6:46 PM, ericfe notifications@github.com wrote:

Have you tried with other values ( like 1024 instead of 8000 ) ?

—
Reply to this email directly or view it on GitHub #38 (comment).

[ericfe]

Correct. Given the max size of objects( tables and columns ) 1024 should be enough.
8000 has performance implications. If 1024 works for you, I will go with that for now.

[ericfe]

Pushed fix

[ghost]

Looks like the problem isn't fixed. The fix should go on line 89 in the --CONSTRAINT LIST section of the query. This can be reproduced by adding a UNIQUE constraint on a table using enough columns to blow past 275 characters, and then querying v_generate_tbl_ddl. The fix is still the same as I described - change line 89 to:
,'\t,' + pg_get_constraintdef(con.oid)::VARCHAR(1024) AS ddl