Re: field error on refreshed materialized view

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.)

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".

regards, tom lane