pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Nolan <htfoot@gmail.com>
Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: field error on refreshed materialized view
Date: Thu, 25 Jan 2024 16:38:44 -0500
Message-ID: <456737.1706218724@sss.pgh.pa.us> (raw)
In-Reply-To: <CAOzAqu+U6h201hE9PNe6jfBFc7G-LmWS973fUTsM+eRzwx3Fvg@mail.gmail.com>
References: <CAOzAqu+U6h201hE9PNe6jfBFc7G-LmWS973fUTsM+eRzwx3Fvg@mail.gmail.com>

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.)

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





view thread (6+ messages)  latest in thread

Message-ID: <456737.1706218724@sss.pgh.pa.us>
Permalink:  ../456737.1706218724@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/456737.1706218724@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: tgl@sss.pgh.pa.us, htfoot@gmail.com
  Subject: Re: field error on refreshed materialized view
  In-Reply-To: <456737.1706218724@sss.pgh.pa.us>
  References: <CAOzAqu+U6h201hE9PNe6jfBFc7G-LmWS973fUTsM+eRzwx3Fvg@mail.gmail.com> <456737.1706218724@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox