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.
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
Regards,
Mark Phillips
Mophilly Technology Inc.
Telephone: (619) 296-0114
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.)
https://www.postgresql.org/docs/current/datatype-character.html
David J.