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 1rTqDs-006n6d-CM for pgsql-general@arkaria.postgresql.org; Sat, 27 Jan 2024 21:28:20 +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 1rTqCs-007bu1-KW for pgsql-general@arkaria.postgresql.org; Sat, 27 Jan 2024 21:27:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rTqCs-007btt-9k for pgsql-general@lists.postgresql.org; Sat, 27 Jan 2024 21:27:18 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rTqCl-003gy6-Cf for pgsql-general@lists.postgresql.org; Sat, 27 Jan 2024 21:27:16 +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 40RLR9Kb1408870; Sat, 27 Jan 2024 16:27:09 -0500 From: Tom Lane To: Michael Nolan cc: "pgsql-generallists.postgresql.org" Subject: Re: field error on refreshed materialized view In-reply-to: References: <456737.1706218724@sss.pgh.pa.us> Comments: In-reply-to Michael Nolan message dated "Sat, 27 Jan 2024 14:28:43 -0600" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1408868.1706390829.1@sss.pgh.pa.us> Date: Sat, 27 Jan 2024 16:27:09 -0500 Message-ID: <1408869.1706390829@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Michael Nolan writes: > Followup: The problem turned out to be a field in the mysql server > that was not size-limited to 255 characters and had several rows with > as many as 299 characters in them. Apparently when using an FDW and > materialized view in postgresql 10.4, field size limits aren't > checked, because a query on the matview in the 10.4 system finds those > records with more than 255 characters in that field. > So, not a character encoding issue at all. Hm ... if the foreign table's field was declared as varchar(255), older PG might've believed that and not rechecked the length while storing into the matview. Not sure if this behavior change was intentional or an accidental product of refactoring. regards, tom lane