Thread: pg_upgrade --check fails to warn about abstime

I got a complaint that pg_upgrade --check fails to raise red flags when
the source database contains type abstime when upgrading from pg11.  The
type (along with reltime and tinterval) was removed by pg12.


In passing, while testing this, I noticed that the translation
infrastructure in pg_upgrade/util.c is broken: we do have the messages
in the translation catalog, but the translations for the messages from
prep_status are never displayed.  So I made the quick hack of adding _()
around the fmt, and this was the result:

Verificando Consistencia en Vivo en el Servidor Antiguo
-------------------------------------------------------
Verificando las versiones de los clústers                    éxito
Verificando que el usuario de base de datos es el usuario de instalaciónéxito
Verificando los parámetros de conexión de bases de datos    éxito
Verificando transacciones preparadas                          éxito
Verificando tipos compuestos definidos por el sistema en tablas de usuarioéxito
Verificando tipos de datos reg* en datos de usuario           éxito
Verificando contrib/isn con discordancia en mecanismo de paso de bigintéxito
Checking for incompatible "aclitem" data type in user tables  éxito
Checking for removed "abstime" data type in user tables       éxito
Checking for removed "reltime" data type in user tables       éxito
Checking for removed "tinterval" data type in user tables     éxito
Verificando conversiones de codificación definidas por el usuarioéxito
Verificando operadores postfix definidos por el usuario       éxito
Verificando funciones polimórficas incompatibles             éxito
Verificando tablas WITH OIDS                                  éxito
Verificando columnas de usuario del tipo «sql_identifier»   éxito
Verificando la presencia de las bibliotecas requeridas        éxito
Verificando que el usuario de base de datos es el usuario de instalaciónéxito
Verificando transacciones preparadas                          éxito
Verificando los directorios de tablespaces para el nuevo clústeréxito

Note how nicely they line up ... not.  There is some code that claims to
do this correctly, but apparently it counts bytes, not characters, and
also it appears to be measuring the original rather than the
translation.

I think we're trimming the strings in the wrong places.  We need to
apply _() to the originals, not the trimmed ones.  Anyway, clearly
nobody has looked at this very much.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"We’ve narrowed the problem down to the customer’s pants being in a situation
 of vigorous combustion" (Robert Haas, Postgres expert extraordinaire)

> +/*
> + * check_for_removed_data_type_usage
> + *
> + *        similar to the above, but for types that were removed in 12.
> + */
> +static void
> +check_for_removed_data_type_usage(ClusterInfo *cluster, const char *datatype)

Seems like you could make this more generic instead of hardcoding
version 12, and then you could use it for any future removed types as
well.

Just a thought.

--
Tristan Partin
Neon (https://neon.tech)

On 2023-Sep-20, Tristan Partin wrote:

> > +/*
> > + * check_for_removed_data_type_usage
> > + *
> > + *        similar to the above, but for types that were removed in 12.
> > + */
> > +static void
> > +check_for_removed_data_type_usage(ClusterInfo *cluster, const char *datatype)
> 
> Seems like you could make this more generic instead of hardcoding version
> 12, and then you could use it for any future removed types as well.

Yeah, I thought about that, and then closed that with "we can whack it
around when we need it".  At this point I imagine there's very few other
datatypes we can remove from the core server, if any.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"La libertad es como el dinero; el que no la sabe emplear la pierde" (Alvarez)

On Wed Sep 20, 2023 at 12:58 PM CDT, Alvaro Herrera wrote:
> On 2023-Sep-20, Tristan Partin wrote:
>
> > > +/*
> > > + * check_for_removed_data_type_usage
> > > + *
> > > + *        similar to the above, but for types that were removed in 12.
> > > + */
> > > +static void
> > > +check_for_removed_data_type_usage(ClusterInfo *cluster, const char *datatype)
> >
> > Seems like you could make this more generic instead of hardcoding version
> > 12, and then you could use it for any future removed types as well.
>
> Yeah, I thought about that, and then closed that with "we can whack it
> around when we need it".  At this point I imagine there's very few other
> datatypes we can remove from the core server, if any.

Makes complete sense to me. Patch looks good to me with one comment.

> +                pg_fatal("Your installation contains the \"%s\" data type in user tables.\n"
> +                                 "Data type \"%s\" has been removed in PostgreSQL version 12,\n"
> +                                 "so this cluster cannot currently be upgraded.  You can drop the\n"
> +                                 "problem columns, or change them to another data type, and restart\n"
> +                                 "the upgrade.  A list of the problem columns is in the file:\n"
> +                                 "    %s", datatype, datatype, output_path);

I would wrap the second \"%s\" in commas.

> Data type, "abstime", has been...

Maybe also add a "The" to start that sentence to make it less terse. Up
to you.

--
Tristan Partin
Neon (https://neon.tech)

On Wed, Sep 20, 2023 at 06:54:24PM +0200, Álvaro Herrera wrote:
> I got a complaint that pg_upgrade --check fails to raise red flags when
> the source database contains type abstime when upgrading from pg11.  The
> type (along with reltime and tinterval) was removed by pg12.

Wow, I never added code to pg_upgrade to check for that, and no one
complained either.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.

Bruce Momjian <bruce@momjian.us> writes:
> On Wed, Sep 20, 2023 at 06:54:24PM +0200, Álvaro Herrera wrote:
>> I got a complaint that pg_upgrade --check fails to raise red flags when
>> the source database contains type abstime when upgrading from pg11.  The
>> type (along with reltime and tinterval) was removed by pg12.

> Wow, I never added code to pg_upgrade to check for that, and no one
> complained either.

Yeah, so most people had indeed listened to warnings and moved away
from those datatypes.  I'm inclined to think that adding code for this
at this point is a bit of a waste of time.

            regards, tom lane

On 2023-Sep-21, Tom Lane wrote:

> Bruce Momjian <bruce@momjian.us> writes:

> > Wow, I never added code to pg_upgrade to check for that, and no one
> > complained either.
> 
> Yeah, so most people had indeed listened to warnings and moved away
> from those datatypes.  I'm inclined to think that adding code for this
> at this point is a bit of a waste of time.

The migrations from versions prior to 12 have not stopped yet, and I did
receive a complaint about it.  Because the change is so simple, I'm
inclined to patch it anyway, late though it is.

I decided to follow Tristan's advice to add the version number as a
parameter to the new function; this way, the knowledge of where was what
dropped is all in the callsite and none in the function.  It
looked a bit schizoid otherwise.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Postgres is bloatware by design: it was built to house
 PhD theses." (Joey Hellerstein, SIGMOD annual conference 2002)