PostgreSQL 15 with plperl: lc_monetary problem (works with Postgresql 9.5)

Question

I was trying to import a Postgres 9.5 dump into a Postgres 15 database and found that creating the plperl extension changes the behavior with lc_monetary: inserts with columns of type money fail when in the dump: ERROR: invalid input syntax for type money When I run the same inserts in the console after importing the dump, they are fine. The dump works with Postgres 9.5 but not with Postgres 15.

I reduced the dump to the relevant lines:

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

show lc_monetary;

CREATE EXTENSION IF NOT EXISTS plperl WITH SCHEMA pg_catalog;

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

CREATE EXTENSION IF NOT EXISTS btree_gist WITH SCHEMA public;

show lc_monetary;

CREATE DOMAIN public.nonnegmoney AS money
    CONSTRAINT nonnegmoney_check CHECK ((VALUE >= (0)::money));

ALTER DOMAIN public.nonnegmoney OWNER TO vms_test;

CREATE TABLE public.vms_test (
     id integer,
     money1 public.nonnegmoney,
     money2 money
);

ALTER TABLE public.vms_test OWNER TO vms_test;

insert into public.vms_test (id, money1, money2) values (1, '€ 0,00', '€ 0,00'); -- <=== fails here
insert into public.vms_test (id, money1, money2) values (2, '€ 625,00', '€ 625,00');

REVOKE ALL ON TABLE public.vms_test FROM PUBLIC;
REVOKE ALL ON TABLE public.vms_test FROM vms_test;
GRANT ALL ON TABLE public.vms_test TO vms_test;

If I disable the plperl extension creation line, the dump works. If I enable the line with plperl extension creation and disable the inserts, the dump works too - and - as I said - if I run those inserts afterwards, they work. Also, it doesn't matter if I use --column-inserts for the dump or not. (Changing the database is not an option)

Maybe it's a problem how I create the docker container.

Dockerfile:

FROM postgres:15
USER root
RUN localedef -i de_AT -c -f UTF-8 -A /usr/share/locale/locale.alias de_AT.UTF-8
RUN apt-get update
RUN apt-get -y install perl
RUN apt-get -y install postgresql-plperl-15
ENV LANG de_AT.utf8

docker-compose:

version: '2'

services:

  vmspostgres:
    build:
      context: .
      dockerfile: postgres/Dockerfile
    container_name: vms-postgres-15
    ports:
      - "5439:5432"
    volumes:
      - ./_postgresdata:/var/lib/postgresql/data
      - ./postgres/15-create-dbs.sh:/docker-entrypoint-initdb.d/15-create-dbs.sh
      - ./postgres/17-monetary-test.sql:/17-monetary-test.sql
      - ./postgres/18-monetary-test.sql.gz:/18-monetary-test.sql.gz
    environment:
      - POSTGRES_USER=vms
      - POSTGRES_PASSWORD=xxx
      - POSTGRES_DB=postgres
    command: ["postgres", "-c", "log_statement=all"]

This is the database log with enabled CREATE EXTENSION plperl line (Sorry it's in German but I hope you can get an idea of what it means):

2023-01-09 10:11:55 /usr/local/bin/docker-entrypoint.sh: sourcing /docker-entrypoint-initdb.d/15-create-dbs.sh
2023-01-09 10:11:55 === 17-monetary-test.sql ===
2023-01-09 10:11:55 2023-01-09 09:11:55.189 UTC [61] LOG:  Anweisung: CREATE ROLE vms_test LOGIN
2023-01-09 10:11:55   PASSWORD 'vms_test'
2023-01-09 10:11:55   NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
2023-01-09 10:11:55 CREATE ROLE
2023-01-09 10:11:55 2023-01-09 09:11:55.206 UTC [61] LOG:  Anweisung: create database vms_test with owner vms_test LC_COLLATE = 'de_AT.UTF-8' LC_CTYPE = 'de_AT.UTF-8' TEMPLATE template0;
2023-01-09 10:11:55 CREATE DATABASE
2023-01-09 10:11:55 2023-01-09 09:11:55.436 UTC [61] LOG:  Anweisung: set datestyle = 'iso, ymd';
2023-01-09 10:11:55 SET
2023-01-09 10:11:55 2023-01-09 09:11:55.436 UTC [61] LOG:  Anweisung: set lc_messages = 'en_US.UTF-8';
2023-01-09 10:11:55 SET
2023-01-09 10:11:55 2023-01-09 09:11:55.436 UTC [61] LOG:  statement: set lc_monetary = 'de_AT.UTF-8';
2023-01-09 10:11:55 SET
2023-01-09 10:11:55 2023-01-09 09:11:55.436 UTC [61] LOG:  statement: set lc_numeric = 'de_AT.UTF-8';
2023-01-09 10:11:55 SET
2023-01-09 10:11:55 2023-01-09 09:11:55.437 UTC [61] LOG:  statement: set lc_time = 'de_AT.UTF-8';
2023-01-09 10:11:55 SET
2023-01-09 10:11:55 2023-01-09 09:11:55.437 UTC [61] LOG:  statement: set default_text_search_config = 'pg_catalog.german';
2023-01-09 10:11:55 SET
2023-01-09 10:11:55 2023-01-09 09:11:55.438 UTC [61] LOG:  statement: GRANT ALL PRIVILEGES ON DATABASE vms_test TO vms_test;
2023-01-09 10:11:55 GRANT
2023-01-09 10:11:55 
2023-01-09 10:11:55 === 18-monetry-test.sql.gz ===
2023-01-09 10:11:55 2023-01-09 09:11:55.522 UTC [64] LOG:  Anweisung: SET statement_timeout = 0;
2023-01-09 10:11:55 SET
2023-01-09 10:11:55 2023-01-09 09:11:55.523 UTC [64] LOG:  Anweisung: SET lock_timeout = 0;
2023-01-09 10:11:55 SET
2023-01-09 10:11:55 2023-01-09 09:11:55.523 UTC [64] LOG:  Anweisung: SET client_encoding = 'UTF8';
2023-01-09 10:11:55 SET
2023-01-09 10:11:55 2023-01-09 09:11:55.523 UTC [64] LOG:  Anweisung: SET standard_conforming_strings = on;
2023-01-09 10:11:55 SET
2023-01-09 10:11:55 2023-01-09 09:11:55.523 UTC [64] LOG:  Anweisung: SELECT pg_catalog.set_config('search_path', '', false);
2023-01-09 10:11:55  set_config 
2023-01-09 10:11:55 ------------
2023-01-09 10:11:55  
2023-01-09 10:11:55 (1 Zeile)
2023-01-09 10:11:55 
2023-01-09 10:11:55 2023-01-09 09:11:55.524 UTC [64] LOG:  Anweisung: SET check_function_bodies = false;
2023-01-09 10:11:55 SET
2023-01-09 10:11:55 2023-01-09 09:11:55.525 UTC [64] LOG:  Anweisung: SET xmloption = content;
2023-01-09 10:11:55 SET
2023-01-09 10:11:55 2023-01-09 09:11:55.525 UTC [64] LOG:  Anweisung: SET client_min_messages = warning;
2023-01-09 10:11:55 SET
2023-01-09 10:11:55 2023-01-09 09:11:55.526 UTC [64] LOG:  Anweisung: SET row_security = off;
2023-01-09 10:11:55 SET
2023-01-09 10:11:55 2023-01-09 09:11:55.526 UTC [64] LOG:  Anweisung: show lc_monetary;
2023-01-09 10:11:55  lc_monetary 
2023-01-09 10:11:55 -------------
2023-01-09 10:11:55  de_AT.utf8
2023-01-09 10:11:55 (1 Zeile)
2023-01-09 10:11:55 
2023-01-09 10:11:55 2023-01-09 09:11:55.526 UTC [64] LOG:  Anweisung: CREATE EXTENSION IF NOT EXISTS plperl WITH SCHEMA pg_catalog;
2023-01-09 10:11:55 CREATE EXTENSION
2023-01-09 10:11:55 2023-01-09 09:11:55.551 UTC [64] LOG:  Anweisung: CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
2023-01-09 10:11:55 CREATE EXTENSION
2023-01-09 10:11:55 2023-01-09 09:11:55.551 UTC [64] LOG:  Anweisung: CREATE EXTENSION IF NOT EXISTS btree_gist WITH SCHEMA public;
2023-01-09 10:11:55 CREATE EXTENSION
2023-01-09 10:11:55 2023-01-09 09:11:55.610 UTC [64] LOG:  Anweisung: show lc_monetary;
2023-01-09 10:11:55  lc_monetary 
2023-01-09 10:11:55 -------------
2023-01-09 10:11:55  de_AT.utf8
2023-01-09 10:11:55 (1 Zeile)
2023-01-09 10:11:55 
2023-01-09 10:11:55 2023-01-09 09:11:55.611 UTC [64] LOG:  Anweisung: CREATE DOMAIN public.nonnegmoney AS money
2023-01-09 10:11:55     CONSTRAINT nonnegmoney_check CHECK ((VALUE >= (0)::money));
2023-01-09 10:11:55 CREATE DOMAIN
2023-01-09 10:11:55 2023-01-09 09:11:55.612 UTC [64] LOG:  Anweisung: ALTER DOMAIN public.nonnegmoney OWNER TO vms_test;
2023-01-09 10:11:55 ALTER DOMAIN
2023-01-09 10:11:55 2023-01-09 09:11:55.613 UTC [64] LOG:  Anweisung: CREATE TABLE public.vms_test (
2023-01-09 10:11:55      id integer,
2023-01-09 10:11:55      money1 public.nonnegmoney,
2023-01-09 10:11:55      money2 money
2023-01-09 10:11:55 );
2023-01-09 10:11:55 CREATE TABLE
2023-01-09 10:11:55 2023-01-09 09:11:55.616 UTC [64] LOG:  Anweisung: ALTER TABLE public.vms_test OWNER TO vms_test;
2023-01-09 10:11:55 ALTER TABLE
2023-01-09 10:11:55 2023-01-09 09:11:55.617 UTC [64] LOG:  Anweisung: insert into public.vms_test (id, money1, money2) values (1, '€ 0,00', '€ 0,00');
2023-01-09 10:11:55 2023-01-09 09:11:55.617 UTC [64] FEHLER:  ungültige Eingabesyntax für Typ money: »€ 0,00« bei Zeichen 61
2023-01-09 10:11:55 2023-01-09 09:11:55.617 UTC [64] ANWEISUNG:  insert into public.vms_test (id, money1, money2) values (1, '€ 0,00', '€ 0,00');
2023-01-09 10:11:55 FEHLER:  ungültige Eingabesyntax für Typ money: »€ 0,00«
2023-01-09 10:11:55 ZEILE 1: ...o public.vms_test (id, money1, money2) values (1, '€ 0,00', ...
2023-01-09 10:11:55                                                               ^

This is the database log with disabled CREATE EXTENSION plperl line:

2023-01-09 10:15:11 /usr/local/bin/docker-entrypoint.sh: sourcing /docker-entrypoint-initdb.d/15-create-dbs.sh
2023-01-09 10:15:11 === 17-monetary-test.sql ===
2023-01-09 10:15:11 2023-01-09 09:15:11.402 UTC [60] LOG:  Anweisung: CREATE ROLE vms_test LOGIN
2023-01-09 10:15:11   PASSWORD 'vms_test'
2023-01-09 10:15:11   NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
2023-01-09 10:15:11 CREATE ROLE
2023-01-09 10:15:11 2023-01-09 09:15:11.419 UTC [60] LOG:  Anweisung: create database vms_test with owner vms_test LC_COLLATE = 'de_AT.UTF-8' LC_CTYPE = 'de_AT.UTF-8' TEMPLATE template0;
2023-01-09 10:15:11 CREATE DATABASE
2023-01-09 10:15:11 2023-01-09 09:15:11.653 UTC [60] LOG:  Anweisung: set datestyle = 'iso, ymd';
2023-01-09 10:15:11 SET
2023-01-09 10:15:11 2023-01-09 09:15:11.653 UTC [60] LOG:  Anweisung: set lc_messages = 'en_US.UTF-8';
2023-01-09 10:15:11 SET
2023-01-09 10:15:11 2023-01-09 09:15:11.653 UTC [60] LOG:  statement: set lc_monetary = 'de_AT.UTF-8';
2023-01-09 10:15:11 SET
2023-01-09 10:15:11 2023-01-09 09:15:11.653 UTC [60] LOG:  statement: set lc_numeric = 'de_AT.UTF-8';
2023-01-09 10:15:11 SET
2023-01-09 10:15:11 2023-01-09 09:15:11.654 UTC [60] LOG:  statement: set lc_time = 'de_AT.UTF-8';
2023-01-09 10:15:11 SET
2023-01-09 10:15:11 2023-01-09 09:15:11.654 UTC [60] LOG:  statement: set default_text_search_config = 'pg_catalog.german';
2023-01-09 10:15:11 SET
2023-01-09 10:15:11 2023-01-09 09:15:11.655 UTC [60] LOG:  statement: GRANT ALL PRIVILEGES ON DATABASE vms_test TO vms_test;
2023-01-09 10:15:11 GRANT
2023-01-09 10:15:11 
2023-01-09 10:15:11 === 18-monetry-test.sql.gz ===
2023-01-09 10:15:11 2023-01-09 09:15:11.743 UTC [63] LOG:  Anweisung: SET statement_timeout = 0;
2023-01-09 10:15:11 SET
2023-01-09 10:15:11 2023-01-09 09:15:11.743 UTC [63] LOG:  Anweisung: SET lock_timeout = 0;
2023-01-09 10:15:11 SET
2023-01-09 10:15:11 2023-01-09 09:15:11.743 UTC [63] LOG:  Anweisung: SET client_encoding = 'UTF8';
2023-01-09 10:15:11 SET
2023-01-09 10:15:11 2023-01-09 09:15:11.743 UTC [63] LOG:  Anweisung: SET standard_conforming_strings = on;
2023-01-09 10:15:11 SET
2023-01-09 10:15:11 2023-01-09 09:15:11.743 UTC [63] LOG:  Anweisung: SELECT pg_catalog.set_config('search_path', '', false);
2023-01-09 10:15:11  set_config 
2023-01-09 10:15:11 ------------
2023-01-09 10:15:11  
2023-01-09 10:15:11 (1 Zeile)
2023-01-09 10:15:11 
2023-01-09 10:15:11 2023-01-09 09:15:11.744 UTC [63] LOG:  Anweisung: SET check_function_bodies = false;
2023-01-09 10:15:11 SET
2023-01-09 10:15:11 2023-01-09 09:15:11.744 UTC [63] LOG:  Anweisung: SET xmloption = content;
2023-01-09 10:15:11 SET
2023-01-09 10:15:11 2023-01-09 09:15:11.744 UTC [63] LOG:  Anweisung: SET client_min_messages = warning;
2023-01-09 10:15:11 SET
2023-01-09 10:15:11 2023-01-09 09:15:11.744 UTC [63] LOG:  Anweisung: SET row_security = off;
2023-01-09 10:15:11 SET
2023-01-09 10:15:11 2023-01-09 09:15:11.744 UTC [63] LOG:  Anweisung: show lc_monetary;
2023-01-09 10:15:11  lc_monetary 
2023-01-09 10:15:11 -------------
2023-01-09 10:15:11  de_AT.utf8
2023-01-09 10:15:11 (1 Zeile)
2023-01-09 10:15:11 
2023-01-09 10:15:11 2023-01-09 09:15:11.744 UTC [63] LOG:  Anweisung: CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
2023-01-09 10:15:11 CREATE EXTENSION
2023-01-09 10:15:11 2023-01-09 09:15:11.745 UTC [63] LOG:  Anweisung: CREATE EXTENSION IF NOT EXISTS btree_gist WITH SCHEMA public;
2023-01-09 10:15:11 CREATE EXTENSION
2023-01-09 10:15:11 2023-01-09 09:15:11.815 UTC [63] LOG:  Anweisung: show lc_monetary;
2023-01-09 10:15:11  lc_monetary 
2023-01-09 10:15:11 -------------
2023-01-09 10:15:11  de_AT.utf8
2023-01-09 10:15:11 (1 Zeile)
2023-01-09 10:15:11 
2023-01-09 10:15:11 2023-01-09 09:15:11.815 UTC [63] LOG:  Anweisung: CREATE DOMAIN public.nonnegmoney AS money
2023-01-09 10:15:11     CONSTRAINT nonnegmoney_check CHECK ((VALUE >= (0)::money));
2023-01-09 10:15:11 CREATE DOMAIN
2023-01-09 10:15:11 2023-01-09 09:15:11.816 UTC [63] LOG:  Anweisung: ALTER DOMAIN public.nonnegmoney OWNER TO vms_test;
2023-01-09 10:15:11 ALTER DOMAIN
2023-01-09 10:15:11 2023-01-09 09:15:11.817 UTC [63] LOG:  Anweisung: CREATE TABLE public.vms_test (
2023-01-09 10:15:11      id integer,
2023-01-09 10:15:11      money1 public.nonnegmoney,
2023-01-09 10:15:11      money2 money
2023-01-09 10:15:11 );
2023-01-09 10:15:11 CREATE TABLE
2023-01-09 10:15:11 2023-01-09 09:15:11.818 UTC [63] LOG:  Anweisung: ALTER TABLE public.vms_test OWNER TO vms_test;
2023-01-09 10:15:11 ALTER TABLE
2023-01-09 10:15:11 2023-01-09 09:15:11.819 UTC [63] LOG:  Anweisung: insert into public.vms_test (id, money1, money2) values (1, '€ 0,00', '€ 0,00');
2023-01-09 10:15:11 INSERT 0 1
2023-01-09 10:15:11 2023-01-09 09:15:11.820 UTC [63] LOG:  Anweisung: insert into public.vms_test (id, money1, money2) values (2, '€ 625,00', '€ 625,00');
2023-01-09 10:15:11 INSERT 0 1
2023-01-09 10:15:11 2023-01-09 09:15:11.820 UTC [63] LOG:  Anweisung: REVOKE ALL ON TABLE public.vms_test FROM PUBLIC;
2023-01-09 10:15:11 REVOKE
2023-01-09 10:15:11 2023-01-09 09:15:11.820 UTC [63] LOG:  Anweisung: REVOKE ALL ON TABLE public.vms_test FROM vms_test;
2023-01-09 10:15:11 REVOKE
2023-01-09 10:15:11 2023-01-09 09:15:11.821 UTC [63] LOG:  Anweisung: GRANT ALL ON TABLE public.vms_test TO vms_test;
2023-01-09 10:15:11 GRANT
2023-01-09 10:15:11 
2023-01-09 10:15:11 
2023-01-09 10:15:11 2023-01-09 09:15:11.827 UTC [49] LOG:  schnelles Herunterfahren verlangt
2023-01-09 10:15:11 warte auf Herunterfahren des Servers...2023-01-09 09:15:11.828 UTC [49] LOG:  etwaige aktive Transaktionen werden abgebrochen
2023-01-09 10:15:11 2023-01-09 09:15:11.832 UTC [49] LOG:  Background-Worker »logical replication launcher« (PID 55) beendete mit Status 1
2023-01-09 10:15:11 .2023-01-09 09:15:11.835 UTC [50] LOG:  fahre herunter
2023-01-09 10:15:11 2023-01-09 09:15:11.836 UTC [50] LOG:  Checkpoint beginnt: shutdown immediate
2023-01-09 10:15:12 2023-01-09 09:15:12.013 UTC [50] LOG:  Checkpoint komplett: 973 Puffer geschrieben (5.9%); 0 WAL-Datei(en) hinzugefügt, 0 entfernt, 0 wiederverwendet; Schreiben=0,083 s, Sync=0,091 s, gesamt=0,179 s; sync. Dateien=258, längste=0,003 s, Durchschnitt=0.001 s; Entfernung=4905 kB, Schätzung=4905 kB
2023-01-09 10:15:12 2023-01-09 09:15:12.056 UTC [49] LOG:  Datenbanksystem ist heruntergefahren
2023-01-09 10:15:12  fertig
2023-01-09 10:15:12 Server angehalten
2023-01-09 10:15:12 
2023-01-09 10:15:12 PostgreSQL init process complete; ready for start up.
2023-01-09 10:15:12 
2023-01-09 10:15:12 2023-01-09 09:15:12.188 UTC [1] LOG:  PostgreSQL 15.1 (Debian 15.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit startet
2023-01-09 10:15:12 2023-01-09 09:15:12.188 UTC [1] LOG:  erwarte Verbindungen auf IPv4-Adresse »0.0.0.0«, Port 5432
2023-01-09 10:15:12 2023-01-09 09:15:12.188 UTC [1] LOG:  erwarte Verbindungen auf IPv6-Adresse »::«, Port 5432
2023-01-09 10:15:12 2023-01-09 09:15:12.191 UTC [1] LOG:  erwarte Verbindungen auf Unix-Socket »/var/run/postgresql/.s.PGSQL.5432«
2023-01-09 10:15:12 2023-01-09 09:15:12.204 UTC [68] LOG:  Datenbanksystem wurde am 2023-01-09 09:15:12 UTC heruntergefahren
2023-01-09 10:15:12 2023-01-09 09:15:12.220 UTC [1] LOG:  Datenbanksystem ist bereit, um Verbindungen anzunehmen

This is the import script which is executed in the docker container:

#!/bin/bash

set -e

echo "=== 17-monetary-test.sql ==="
psql -v ON_ERROR_STOP=1 --username postgres --dbname postgres -f ../17-monetary-test.sql; echo;
echo "=== 18-monetry-test.sql.gz ==="
gunzip -c ../18-monetary-test.sql.gz | psql -v ON_ERROR_STOP=1 --username postgres --dbname vms_test; echo;

What do you think?

"SOLUTION"

I haven't found any real solution to this specific problem. All attempts with different postgres versions were unsuccessful, both with docker and directly on my Linux system. Even if I execute all statements of the dump on the console, there is ultimately a problem with the import of larger amounts of money, because on newer Linux systems the thousands separator of lc_monetary for the locale de_AT has been changed from a normal space (<U0020>) to a non-breakable-space (<U202F>).

Since the official Docker container for postgres-9.5 is no longer supported, I got the Dockerfile for version 9.5 from the postgres git history, changed the apt directories to the archive directories and now have a running postgres-9.5 docker version, into which I can import the old database. For our software upgrade to a current postgresql version, we will change all money columns to a numeric type!

Thank you for your help (especially @AdrianKlaver)