I'm experiencing a problem with loading a PostgreSQL backup file (SQL format).
The SQL file has a function that is defined after another function where it's used. PostgreSQL 13.13 can handle such a backup file, while PostgreSQL 13.14 fails to load it:
ERROR: function public.label_id_constant() does not exist
LINE 1: SELECT public.uuid_increment($1, public.label_id_constant()...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT public.uuid_increment($1, public.label_id_constant())
CONTEXT: SQL function "label_id" during inlining
I've double-checked if there is SET check_function_bodies = false; in the dump file. I also searched if I could disable the inlining during the dump load, but still no success.
I've distilled the dump file into a minimal reproducible example and attached it as a script to this ticket.
If anybody experienced anything similar, please help.
#!/bin/env bash
DUMP_FILE=$(mktemp)
trap "rm -f $DUMP_FILE" EXIT
cat - > "$DUMP_FILE" <<'EOF'
--
-- PostgreSQL database dump
--
-- Dumped from database version 13.18
-- Dumped by pg_dump version 13.18
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_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;
CREATE SCHEMA internal;
CREATE DOMAIN public.ulid AS uuid;
CREATE FUNCTION public.label_id(team_id public.ulid) RETURNS public.ulid
LANGUAGE sql IMMUTABLE PARALLEL SAFE
AS $_$ SELECT public.uuid_increment($1, public.label_id_constant()) $_$;
CREATE FUNCTION public.ulid_generate() RETURNS public.ulid
LANGUAGE sql
AS $$
SELECT encode(public.ulid_time_as_bytea(NOW()) || public.gen_random_bytes(10), 'hex')::uuid::public.ulid;
$$;
SET default_tablespace = '';
SET default_table_access_method = heap;
CREATE TABLE internal.teams (
team_id public.ulid DEFAULT public.ulid_generate() NOT NULL,
label_id public.ulid GENERATED ALWAYS AS (public.label_id(team_id)) STORED NOT NULL
);
CREATE FUNCTION public.label_id_constant() RETURNS integer
LANGUAGE sql IMMUTABLE PARALLEL SAFE
AS $$SELECT 3$$;
EOF
echo "Testing with postgres 13.13" >&2
docker run -d \
--name postgres-13.13 \
-e POSTGRES_HOST_AUTH_METHOD=trust \
-p 5432:5432 \
postgres:13.13
echo "Waiting for postgres to start" >&2
while ! docker exec postgres-13.13 pg_isready -h localhost -U postgres -q; do
sleep 1
done
cat "$DUMP_FILE" | psql -h localhost -U postgres -v ON_ERROR_STOP=1 --port 5432 -e -1 && echo "******** Success ********" || echo "******** Failure ********"
docker stop postgres-13.13
docker rm postgres-13.13
echo "Testing with postgres 13.14" >&2
docker run -d \
--name postgres-13.14 \
-e POSTGRES_HOST_AUTH_METHOD=trust \
-p 5432:5432 \
postgres:13.14
echo "Waiting for postgres to start" >&2
while ! docker exec postgres-13.14 pg_isready -h localhost -U postgres -q; do
sleep 1
done
cat "$DUMP_FILE" | psql -h localhost -U postgres -v ON_ERROR_STOP=1 --port 5432 -e -1 && echo "******** Success ********" || echo "******** Failure ********"
docker stop postgres-13.14
docker rm postgres-13.14
UPD:
What I've already tried:
Setting SET jit = off; doesn't fix the problem.
UPD2:
I tried exporting our database using pg_dump, instead of the CloudSQL export API. It gave me the same error.
I tried to export the database, load it to 13.13, then export it from 13.13 and load it to 13.14, but the error was the same again.
UPD: I successfully migrated the DB with the following script:
https://paste.ubuntu.com/p/kgGGQzNcgp/
After migrating to PostgreSQL 17.5, the issue persists. If I dump the DB with pg_dump, I cannot load it with the same error.
CREATEstatements that produce a v13 database that causes a failure when you dump and reload it with 13.14? That would be a bug.