ERROR: value too long for type character varying noticed while executing DB query

Products

VMware Smart Assurance

Issue/Introduction

database-utility.pl tool fails with ERROR: value too long for type character varying(500)
Unable to view device config on GUI, even though pull config job is successful; Found "ERROR: value too long for type character varying(100)" in AS logs.

Environment

NCM - 10.1.x

Cause

This error is caused when the maximum column width for one or more columns in the voyence.cm_cel_audit_record table does not match the column width for that same column in the corresponding acrhive.cm_cel_audit_record table.

If output has more characters than it is set at table level, this error is witnessed; example - vendor_model has more than 100 characters, causing the DB write after pull config to fail.

Resolution

Log into a Linux shell on the NCM PostgreSQL Control Database (controldb) host as 'root'.
Run the following command to launch a controldb shell (the current controldb password will be required):
```
su - pgdba -s /bin/bash -c 'psql voyencedb voyence'
```

Run the following query to find out which column widths are mismatched between the voyence and archive versions of the table (where {table name} is the name of the table cited in any error messages captured from log):

PREPARE voyence_vs_archive_type_compare( text ) AS
       SELECT
         a.table_name,
         a.column_name,
         v.voyence_type,
         a.archive_type
       FROM (
         SELECT
           table_name,
           column_name,
           'varchar(' || character_maximum_length || ')' AS archive_type
         FROM information_schema.columns
         WHERE 
           table_schema = 'archive' AND
           table_name = $1
       ) AS a
       JOIN (
         SELECT
           table_name,
           column_name,
           'varchar(' || character_maximum_length || ')' AS voyence_type
         FROM information_schema.columns
         WHERE 
           table_schema = 'voyence' AND
           table_name = $1
       ) AS v
         ON a.column_name = v.column_name
       WHERE voyence_type != archive_type;

EXECUTE voyence_vs_archive_type_compare('{table name}');

Example:

voyencedb=# EXECUTE voyence_vs_archive_type_compare('cm_cel_audit_record');
          table_name      |  column_name   | voyence_type  | archive_type
     ---------------------+----------------+---------------+--------------
      cm_cel_audit_record | description    | varchar(1024) | varchar(500)
     (1 row)

NOTE:

column_name value may vary according which columns are mismatched between the voyence and archive schema versions of the table.
The prepared statement can be executed repeatedly using different table names without re-pasting the query.

Run the following SQL command to verify the max space required to store a value in the column identified in Step 3 above in the voyence schema version of the table column (Note: Results may differ by file system, however, we generally expect the result to be the sum of the character width of the column and additional overhead space needed that equates to approximately the same size as it would take to store 4 additional characters - in the example above it would be 1028; substitute the column_name value identified in Step 3 above in place of the highlighted {column name} reference in all steps below):
```
SELECT atttypmod 
     FROM pg_attribute 
     WHERE 
       attrelid = '{schema_name.table_name}'::regclass AND 
       attname = '{column name}';
```
Example (Continuation of previous example using the description column in the voyence version of the cm_cel_audit_record table, assuming Step 2 above style login):
```
voyencedb=# SELECT atttypmod
     voyencedb-# FROM pg_attribute
     voyencedb-# WHERE
     voyencedb-# attrelid = 'voyence.cm_cel_audit_record'::regclass AND
     voyencedb-# attname = 'description';
     atttypmod
     -----------
           1028
     (1 row)
```

Run the following command to increase the maximum width in characters for the mismatched column in the archive version of the table where:

{new size} is the maximum size of the data allowed for the column in the voyence schema version of the table as determined in Step 4 above.
{schema_name.table_name} is the name of the archive schema qualified name of the table cited in the error message.

{column name} is the name of the column in question.

 UPDATE pg_attribute 
     SET atttypmod = {new size}
     WHERE attrelid = '{schema_name.table_name}'::regclass
     AND attname = '{column name}';

Example (Continuation of previous step examples using the description column in the archive version of the cm_cel_audit_record table):

voyencedb=# SELECT atttypmod
     voyencedb-# FROM pg_attribute
     voyencedb-# WHERE
     voyencedb-# attrelid = 'archive.cm_cel_audit_record'::regclass AND
     voyencedb-# attname = 'description';
      atttypmod
     -----------
            500
     (1 row)

     voyencedb=# UPDATE pg_attribute
     voyencedb-# SET atttypmod = 1028
     voyencedb-# WHERE attrelid = 'archive.cm_cel_audit_record'::regclass AND
     voyencedb-# AND attname = 'description';
     UPDATE 1

     voyencedb=# SELECT atttypmod
     voyencedb-# FROM pg_attribute
     voyencedb-# WHERE
     voyencedb-# attrelid = 'archive.cm_cel_audit_record'::regclass AND
     voyencedb-# attname = 'description';
      atttypmod
    -----------
          1028
    (1 row)

Additional Information

The above process cannot be used to reduce the size of any table column where any of the records contain data. This is not a problem because the PostgreSQL engine does not consume storage space with empty padding for column values that do not reach the maximum data length set for a given column. Instead, for larger data lengths, having a maximum value set is primary useful to the PostgreSQL engine in terms of determining the most efficient way of storing inserted data in a way that maximizes efficient read/write operations against the data.

Before expanding the data length of any column in any table in NCM, it is advisable to understand why the original limit is being exceeded. That is best accomplished by engaging Dell EMC technical support. It is appropriate to open a service request before undertaking any such action unless the issue can be tied exactly to the error message cited above, in which case these steps are required to resolve the issue.

While steps provided in resolution tab can be used for entire DB tables, here is other example:

Check max length available:

SELECT atttypmod FROM pg_attribute WHERE attrelid = 'cm_device'::regclass AND attname = 'vendor_model ;

Example:

voyencedb=# SELECT atttypmod FROM pg_attribute WHERE attrelid = 'cm_device'::regclass AND attname = 'vendor_model';
atttypmod
-----------
       104
(1 row)

Update to user preferred length:

UPDATE pg_attribute SET atttypmod = 300+4 WHERE attrelid = 'cm_device'::regclass AND attname = 'vendor_model';