Re: field error on refreshed materialized view - Mailing list pgsql-general

From Michael Nolan
Subject Re: field error on refreshed materialized view
Date
Msg-id CAOzAquJrdL8kCAm2Hh0oTfqzc21XUHaN_dRaU7Lix2RzEuUkSg@mail.gmail.com
Whole thread Raw
In response to Re: field error on refreshed materialized view  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: field error on refreshed materialized view
List pgsql-general
On Thu, Jan 25, 2024 at 3:38 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Michael Nolan <htfoot@gmail.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



pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: field error on refreshed materialized view
Next
From: Atul Kumar
Date:
Subject: Re: permission denied on socket