PG12 → PG13 database conversion anomaly: ERROR: function kepler_start_3(double precision, double precision) does not exist

RE: PG12 → PG13 database conversion anomaly

We are encountering difficulties performing a database conversion from PG12 (12.7 (Debian 12.7-1.pgdg100+1)) to PG13 (13.3 (Debian 13.3-1.pgdg100+1)) that we believe to be a bug related to a generated column that calls an immutable user-defined function.  This generated column has worked correctly with good stability on our PG12 database.

Our conversion methodology is to use the pg_dumpall utility (PostgreSQL) 13.3 (Debian 13.3-1.pgdg100+1) to pipe its output to psql (PostgreSQL) 13.3 (Debian 13.3-1.pgdg100+1).

At the conclusion of the above procedure, we discovered that one table in the public schema of the target database, "orbelements", had zero rows, where the source database table contains 115,692,916 rows.

An error was reported by the receiving psql task in this regard:

ERROR: function kepler_start_3(double precision, double precision) does not exist

LINE 1: SELECT kepler_start_3(cast(ecc AS DOUBLE PRECISION), CAST(mn...

^

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

QUERY: SELECT kepler_start_3(cast(ecc AS DOUBLE PRECISION), CAST(mnorm as DOUBLE PRECISION))

CONTEXT: PL/pgSQL function public.kepler_ecc_anomaly(double precision,double precision,double precision) line 11 at assignment

COPY orbelements, line 1: "216172782205023964 72057594038525237 2023-12-22 00:00:00 134.53847019680939 307.1695097610939 351.64..."

setval

--------------------

216172782315473289

(1 row)

The other tables in the database were apparently converted without incident.

While the hint in the error message says “No function matches the given name and argument types.”, we confirmed that the called function and two other functions called by it are present in the public schema are immutable functions, and after extensive type-checking was performed, we can confirm that all argument types are consistent with those functions.

We performed additional tests with psql, and confirmed that the kepler_ecc_anomaly function and its sub-functions perform correctly in the PG 13 database when called in a normal SQL SELECT context. The anomalous behavior appears to occur in the GENERATED ALWAYS AS context. We have so far only encountered this problem in the COPY process that would trigger the GENERATED ALWAYS event.

The DDL for the table containing the column in question is below, the column orbelements.ecc_anomaly is the one that uses the user-defined function. The user defined functions referenced are also included below.

Please advise on what additional information we should provide about this problem.

Best regards,

Rob

Robert Schottland | Database Manager

Lowell Observatory

1400 West Mars Hill Road, Flagstaff, AZ 86001

www.lowell.edu | asteroid.lowell.edu
Office:  +1 928 282 7773
Mobile: +1 928 274 0096
email:|  rschottland(at)lowell(dot)edu

CREATE TABLE public.orbelements (

id int8 NOT NULL,

id_minorplanet int8 NOT NULL,

epoch timestamp NOT NULL,

m float8 NOT NULL,

peri float8 NOT NULL,

node float8 NOT NULL,

i float8 NOT NULL,

e float8 NOT NULL,

a float8 NOT NULL,

id_citation int4 NULL,

when_created timestamp NULL,

when_modified timestamp NULL,

dyn_type int8 NULL,

delta_v float8 NULL,

moid_earth float8 NULL,

moid_mars float8 NULL,

moid_mercury float8 NULL,

moid_venus float8 NULL,

tisserand_param float8 NULL,

dyn_type_json jsonb NULL GENERATED ALWAYS AS (j_dynamical_types(dyn_type)::jsonb) STORED,

aphelion_dist float8 NULL GENERATED ALWAYS AS (a * (1::double precision + e)) STORED,

q float8 NULL GENERATED ALWAYS AS (a * (1::double precision - e)) STORED,

moid_jupiter float8 NULL,

moid_neptune float8 NULL,

moid_saturn float8 NULL,

moid_uranus float8 NULL,

long_of_perihelion float8 NULL GENERATED ALWAYS AS (((peri + node)::numeric % 360::numeric)::double precision) STORED,

r float8 NULL,

true_anomaly float8 NULL,

x float8 NULL,

y float8 NULL,

z float8 NULL,

ecc_anomaly float8 NULL GENERATED ALWAYS AS (kepler_ecc_anomaly(e, m)) STORED,

CONSTRAINT orbelements_pkey PRIMARY KEY (id),

CONSTRAINT uix_ast_epoch UNIQUE (id_minorplanet, epoch)

);

CREATE INDEX ix_dynamical_types ON public.orbelements USING btree (dyn_type);

CREATE INDEX ix_nea_elements ON public.orbelements USING btree (dyn_type) WHERE ((dyn_type % (2)::bigint) = 1);

CREATE INDEX ix_orbelements_citation_id ON public.orbelements USING btree (id_citation);

CREATE INDEX ix_orbelements_id_citation ON public.orbelements USING btree (id_citation);

CREATE INDEX ix_orbelements_id_minorplanet ON public.orbelements USING btree (id_minorplanet);

CREATE OR REPLACE FUNCTION public.kepler_ecc_anomaly(ecc double precision, ma double precision, tol double precision DEFAULT 0.000000000000010)

RETURNS double precision

LANGUAGE plpgsql

IMMUTABLE

AS $function$

DECLARE

de float8; e float8; e0 float8; mnorm float8; ct INTEGER; twopi float8; mar float8;

BEGIN

IF ecc >= 1.0 THEN

RETURN('NaN');

END IF;

twopi = 2.0 * pi();

mar = radians(ma);

mnorm = mar - floor(mar / twopi) * twopi;

e0 = kepler_start_3(cast(ecc AS DOUBLE PRECISION), CAST(mnorm as DOUBLE PRECISION)); -- get initial guess

de = tol + 1.0;

ct = 0;

WHILE de > tol LOOP

e = e0 - kepler_eps_3(CAST(ecc AS DOUBLE PRECISION), CAST(mnorm AS DOUBLE PRECISION), CAST(e0 AS DOUBLE PRECISION)); -- solve iteration

de = abs(e - e0);

e0 = e;

ct = ct + 1;

IF ct > 99 THEN

e = 'NaN';

EXIT;

END IF;

END LOOP;

RETURN degrees(e);

END $function$

CREATE OR REPLACE FUNCTION public.kepler_start_3(e double precision, m double precision)

RETURNS double precision

LANGUAGE plpgsql

IMMUTABLE

AS $function$

DECLARE

t33 float8; t35 float8; t34 float8;

begin

-- raise notice 'function public.kepler_start_3(%s, %s)', e, m;

t34 = (e * e);

t35 = (e * t34);

t33 = cos(m);

RETURN (m + (-1 / 2 * t35 + e + (t34 + 3 / 2 * t33 * t35) * t33) * sin(m))::float8;

END $function$

;

CREATE OR REPLACE FUNCTION public.kepler_eps_3(e double precision, m double precision, x double precision)

RETURNS double precision

LANGUAGE plpgsql

IMMUTABLE

AS $function$

DECLARE

t1 float8; t2 float8; t3 float8;

t4 float8; t5 float8; t6 float8;

begin

-- raise notice 'kepler_eps_3(e=%, M=%, x=%)', e, m, x;

t1 = cos(x);

t2 = -1 + e * t1;

t3 = sin(x);

t4 = e * t3;

t5 = -x + t4 + m;

t6 = t5 / (1 / 2 * t5 * t4 + t2);

return t5 / ((1 / 2 * t3 - 1 / 6 * t1 * t6) * e * t6 + t2);

END $function$

;

--

Robert Schottland | Database Manager

Lowell Observatory

1400 West Mars Hill Road, Flagstaff, AZ 86001

www.lowell.edu | asteroid.lowell.edu
Office:  +1 928 282 7773
Mobile: +1 928 274 0096
email:|  rschottland(at)lowell(dot)edu

--