Easily find issues by searching: #<Issue ID>
Example: #1832
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
| Dev |
|
| QA |
|
| Priority | Low |
| Complexity | Unknown |
| Component | Tools - ER Modeler |
| Version | 13.0 |
1.) Convert the attached ER Model from Greenplum to Vertica. For the data types below
bytea gets converted to varbinary(2147483647)
text gets converted to varchar(1073741825)
xml gets converted to varbinary(2147483647)
2.) For Vertica Datatypes, the range for varbinary and varchar is 1 to 65000
3.) So when i try to create the table in vertica the sql fails with the error
>[Error] Script lines: 1-44 -------------------------
[Vertica][VJDBC](3852) ERROR: Length for type varbinary cannot exceed 65000
The ER conversion from source-db to dest-db is done by source-db based on its best knowledge.
For Greenplum and PostgreSQL, 'text' is a character type with variable unlimited length. The longest possible character string that can be stored in Greenplum/PostgreSQL is about 1 GB; hence, Greenplum/PostgreSQL converts 'text' to varchar(1GB). The length of the converted varchar type, i.e. 1GB, maybe good for some databases (e.g. SQL Server) but might cause problems on other (e.g. Vertica).
Similarly, 'bytea' is a variable-length binary string; Greenplum/PostgreSQL converts it to a varbinary(2GB) which might be good for SQL Server but not Vertica. Same as 'xml' data type.
In the current ADS implementation, conversion is performed by source-db based on its own limitation, not dest-db's limitation. If you apply the same test case to 'SQL Server -> Vertica', you likely will get the same problem because SQL Server converts 'text' to 'varchar(2GB)', 'xml' to 'varbinary(2GB)' and 'image' to 'varbinary(2GB)'.
References:
http://www.postgresql.org/docs/8.2/static/datatype-character.html
http://www.postgresql.org/docs/8.2/static/datatype-binary.html
This problem does not seem to be a bug to me based on current ADS approach.
Had discussed with Fung regarding this issue and this is the general behaviour which exists for ERD and wont be fixed.
Had discussed with Fung regarding this issue and this is the general behaviour which exists for ERD and wont be fixed.
Issue #8153 |
| Closed |
| Won't Fix |
| Resolved |
Completion |
| No due date |
| No fixed build |
| No time estimate |
The ER conversion from source-db to dest-db is done by source-db based on its best knowledge.
For Greenplum and PostgreSQL, 'text' is a character type with variable unlimited length. The longest possible character string that can be stored in Greenplum/PostgreSQL is about 1 GB; hence, Greenplum/PostgreSQL converts 'text' to varchar(1GB). The length of the converted varchar type, i.e. 1GB, maybe good for some databases (e.g. SQL Server) but might cause problems on other (e.g. Vertica).
Similarly, 'bytea' is a variable-length binary string; Greenplum/PostgreSQL converts it to a varbinary(2GB) which might be good for SQL Server but not Vertica. Same as 'xml' data type.
In the current ADS implementation, conversion is performed by source-db based on its own limitation, not dest-db's limitation. If you apply the same test case to 'SQL Server -> Vertica', you likely will get the same problem because SQL Server converts 'text' to 'varchar(2GB)', 'xml' to 'varbinary(2GB)' and 'image' to 'varbinary(2GB)'.
References:
http://www.postgresql.org/docs/8.2/static/datatype-character.html
http://www.postgresql.org/docs/8.2/static/datatype-binary.html
This problem does not seem to be a bug to me based on current ADS approach.