On Thu, Jan 25, 2024 at 3:38 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > > Michael Nolan <htfoot@xxxxxxxxx> 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
