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 1rT7Ke-000YkL-DA for pgsql-general@arkaria.postgresql.org; Thu, 25 Jan 2024 21:32:21 +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 1rT7Kd-004YKr-Hv for pgsql-general@arkaria.postgresql.org; Thu, 25 Jan 2024 21:32:19 +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 1rT7Kd-004YKj-6Z for pgsql-general@lists.postgresql.org; Thu, 25 Jan 2024 21:32:19 +0000 Received: from wout5-smtp.messagingengine.com ([64.147.123.21]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rT7Ka-003iIr-Op for pgsql-general@lists.postgresql.org; Thu, 25 Jan 2024 21:32:18 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.west.internal (Postfix) with ESMTP id 9EDB93200A82; Thu, 25 Jan 2024 16:32:14 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute3.internal (MEProxy); Thu, 25 Jan 2024 16:32:14 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm2; t=1706218334; x=1706304734; bh=JgdwHBt0tp MG4CIgiys/sP4ihTFtQI/bK77Fotk7iyQ=; b=LkGfP0CkaJPeT9W5Eat6lLienW vkKKdP4PQZXW2+mjgFKKI7feMDUKBvbu0qZK4QPPUvcKDLHiwJizE1UOltrSCYYi x8xLSBKpb52D8bkTWSJyba6WJLuYFIbi5TZDTpSx1iWfJ5xXXOYM9oh7FSQuQlew odQjyezPuIAUELNgFEWdgj75ypfnDAVw/y40SRwYJUZdZ3uajDahLuwzwz0YY7Xu 9q3h1hNRf9UeMez/Zcu9dwFBO0amP9lf2OTz1x0Orh+XvWwIOVsymEGGq2FkvttG ReXVsf/XLL2+QpSx+yxKmPNkOcw/wdCaxIIrZJsvHyNksPHJKqckJo8sANIA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm3; t=1706218334; x=1706304734; bh=JgdwHBt0tpMG4CIgiys/sP4ihTFt QI/bK77Fotk7iyQ=; b=foMc6Ihrz7GhKMnYt6ugZKEvcbX8/TrXQ3bMuaRlnrg/ NaGxGR9aDRNSoiDk/7+/xpmH4hkv21oeFhV44xqAPwYLi+QtzAstky93AEl+WusI MyVD6l8/VkexLlBWozA6sKgriHq+3EEKKdY3+lRUnwm9qUBIWgyO+kqlB96huATq /sn74YR2j4+7+HvjLd62R2JaPkep7yO51V+O54lC0ibG2l1TDEgUMXkc31lWdYr3 pIvK8U9+vVOEIrxJT2u3IZAX3XBnfWrm39jxy4eI3dlvQnUrtVKBOgL1ibLMEIYz Wm8qz35amxKpWPLggI+uA0vqdQ9mH3HXhkuWMzGkUA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrvdelhedgheejucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurheptgfkffggfgfuvfhfhfgjsegrtderredtvdejnecuhfhrohhmpeetughrihgr nhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh eqnecuggftrfgrthhtvghrnhepheevudfgfeeifefhheejleevgefglefgheeijeffueeg feejfedtieffhfffkeelnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrg hilhhfrhhomheprggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 25 Jan 2024 16:32:13 -0500 (EST) Content-Type: multipart/alternative; boundary="------------ms4y2uXqjxUFbyH3Pa8O0Zxf" Message-ID: <7b40b352-b8bb-439f-8b29-a93d5efe51bf@aklaver.com> Date: Thu, 25 Jan 2024 13:32:11 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: field error on refreshed materialized view Content-Language: en-US To: Michael Nolan , "pgsql-generallists.postgresql.org" References: From: Adrian Klaver In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------ms4y2uXqjxUFbyH3Pa8O0Zxf Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 1/25/24 1:29 PM, Michael Nolan wrote: > I'm in the process of updating a server from postgresql 10.4 on Centos > 7 to 16.1 on AlmaLInux 9, and am reminded of the old line about when > you're up to your necks in alligators, it is difficult to remember > that your goal was to drain the swamp. > > We have several materialized views that are refreshed from an external > mysql/mariadb server. Define "... refreshed from an external mysql/mariadb server"? > > 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) Is there more to the error, that shows the actual value? Or have you looked in server log to see if there is more information? > > 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. > > Is this something that has to be tracked down on the mysql/mariadb > server or is there anything I can do to help narrow down the problem > on my 16.1 server? > -- > Mike Nolan > htfoot@gmail.com > > --------------ms4y2uXqjxUFbyH3Pa8O0Zxf Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit


On 1/25/24 1:29 PM, Michael Nolan wrote:
I'm in the process of updating a server from postgresql 10.4 on Centos
7 to 16.1 on AlmaLInux 9, and am reminded of the old line about when
you're up to your necks in alligators, it is difficult to remember
that your goal was to drain the swamp.

We have several materialized views that are refreshed from an external
mysql/mariadb server.

Define "... refreshed from an external mysql/mariadb server"?


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)


Is there more to the error, that shows the actual value?

Or have you looked in server log to see if there is more information?



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.

Is this something that has to be tracked down on the mysql/mariadb
server or is there anything I can do to help narrow down the problem
on my 16.1 server?
--
Mike Nolan
htfoot@gmail.com


--------------ms4y2uXqjxUFbyH3Pa8O0Zxf--