Обсуждение: limit of data type character varying
Ran into a problem with SymmetricDS when attempting to sync two postgres 12 databases.
The problem occurs when Symmetric builds the XML for a table that has the character varying datatype with an undefined length. SymmetricDS creates in the xml character field that is 2,147,483,647 length. Subsequently, when SymmetricDS attempts to create the table on the client, an error is raised that says the character varying field exceeds the maximum length.
The actual maximum length supported by postgresql 12 is 10,485,760. We dug into the postgres code and found a limit in the config with a comment dating it to the pg 8.5 era. Being the simple folk that we are, we changed the setting to the higher value. Unfortunately, pg 12 raised some errors of its own.
This seems like a bug in SymmetricDS, which ought to set the maximum length to that supported by the target database.
Additional Information
Mophilly Technology Inc.
The problem occurs when Symmetric builds the XML for a table that has the character varying datatype with an undefined length. SymmetricDS creates in the xml character field that is 2,147,483,647 length. Subsequently, when SymmetricDS attempts to create the table on the client, an error is raised that says the character varying field exceeds the maximum length.
The actual maximum length supported by postgresql 12 is 10,485,760. We dug into the postgres code and found a limit in the config with a comment dating it to the pg 8.5 era. Being the simple folk that we are, we changed the setting to the higher value. Unfortunately, pg 12 raised some errors of its own.
This seems like a bug in SymmetricDS, which ought to set the maximum length to that supported by the target database.
On the other hand, one could argue that a higher limit in postgres is sensible these days.
Additional Information
1. symmetricds version 3.12.4
2. Postgresql Version 12
3. OS's Windows 10, Mac Catalina and SME Server
2. Postgresql Version 12
3. OS's Windows 10, Mac Catalina and SME Server
Regards,
Mark Phillips
Telephone: (619) 296-0114
On the web at http://www.mophilly.com
On the web at http://www.mophilly.com
On Wed, Nov 25, 2020 at 1:43 PM Mark Phillips <mark.phillips@mophilly.com> wrote:
The actual maximum length supported by postgresql 12 is 10,485,760. We dug into the postgres code and found a limit in the config with a comment dating it to the pg 8.5 era. Being the simple folk that we are, we changed the setting to the higher value. Unfortunately, pg 12 raised some errors of its own.
This seems like a bug in SymmetricDS, which ought to set the maximum length to that supported by the target database.On the other hand, one could argue that a higher limit in postgres is sensible these days.
I find the documentation's recommendation relevant here:
In any case, the longest possible character string that can be stored is about 1 GB. (The maximum value that will be allowed for n in the data type declaration is less than that. It wouldn't be useful to change this because with multibyte character encodings the number of characters and bytes can be quite different. If you desire to store long strings with no specific upper limit, use text or character varying without a length specifier, rather than making up an arbitrary length limit.)
David J.