Re: field error on refreshed materialized view

On Thu, Jan 25, 2024 at 3:38 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Michael Nolan <htfoot(at)gmail(dot)com> writes:
> > On the 10.4 server this materialized view works, but on the 16.1
> > server it fails:
>
> > uscf=# refresh materialized view uscf_vip;
> > refresh materialized view uscf_vip;
> > ERROR: value too long for type character varying(255)
>
> > Both materialized view tables appear to be identical. (We did a
> > restore from a recent database dump to populate the new server.)
>
> > I don't see anything in the log files to help me determine which field
> > or row is causing the error. As far as I can tell, encoding is the
> > same on both versions, but there could be issues in how the mysql
> > server is coding non-ASCII characters.
>
> Yeah, an encoding discrepancy could explain this, by causing the
> server to count characters differently than before. (Note that
> we consider varchar(255) to allow 255 characters, not 255 bytes.)

Thanks, Tom.

Both postgresql databases are using SQL-ASCII and C LC values, I don't
know what the mariadb is using, but I've asked the people who maintain
that server for that information. I suspect it may be UTF-8 or
something else that supports multi-byte characters. I don't know if
changing the encoding on the new database server would introduce more
issues than it would solve, but as we're still in testing mode I could
test that.

> One idea for tracking it down could be to remove or increase all the
> varchar limits in the matview declaration, load the data, and then
> probe for bad entries with something like "select * from matview where
> length(some_varchar_field) > 255".

The reasons for the 255 are mostly historical (the original server
this data came from was a COBOL system.) Increasing or removing the
255 character limits may not cause any new problems.

==
Mike Nolan