Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rT7Qw-000ZWl-6R for pgsql-general@arkaria.postgresql.org; Thu, 25 Jan 2024 21:38:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rT7Qv-004jgI-Cs for pgsql-general@arkaria.postgresql.org; Thu, 25 Jan 2024 21:38:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rT7Qv-004jg9-1s for pgsql-general@lists.postgresql.org; Thu, 25 Jan 2024 21:38:49 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rT7Qs-003iMT-Dt for pgsql-general@lists.postgresql.org; Thu, 25 Jan 2024 21:38:48 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 40PLciKI456738; Thu, 25 Jan 2024 16:38:44 -0500 From: Tom Lane To: Michael Nolan cc: "pgsql-generallists.postgresql.org" Subject: Re: field error on refreshed materialized view In-reply-to: References: Comments: In-reply-to Michael Nolan message dated "Thu, 25 Jan 2024 15:29:08 -0600" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <456736.1706218724.1@sss.pgh.pa.us> Date: Thu, 25 Jan 2024 16:38:44 -0500 Message-ID: <456737.1706218724@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Michael Nolan 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