Postgres Pro
Downloads
Home   >   mailing lists

Thread: bad dependency in pg_dump output related to support function breaks binary upgrade

bad dependency in pg_dump output related to support function breaks binary upgrade

From
Pavel Stehule
Date:
Hi

some Orafce's user reported problems with pg_upgrade. I checked this issue and it looks like pg_dump problem:


pg_restore: creating FUNCTION "public.nvarchar2("public"."nvarchar2", integer, boolean)"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 612; 1255 33206 FUNCTION nvarchar2("public"."nvarchar2", integer, boolean) pavel
pg_restore: error: could not execute query: ERROR:  function public.nvarchar2_transform(internal) does not exist
Command was: CREATE FUNCTION "public"."nvarchar2"("public"."nvarchar2", integer, boolean) RETURNS "public"."nvarchar2"
    LANGUAGE "c" IMMUTABLE STRICT SUPPORT "public"."nvarchar2_transform"
    AS '$libdir/orafce', 'nvarchar2';




--
-- TOC entry 612 (class 1255 OID 33206)
-- Name: nvarchar2("public"."nvarchar2", integer, boolean); Type: FUNCTION; Schema: public; Owner: pavel
--

CREATE FUNCTION "public"."nvarchar2"("public"."nvarchar2", integer, boolean) RETURNS "public"."nvarchar2"
    LANGUAGE "c" IMMUTABLE STRICT SUPPORT "public"."nvarchar2_transform"
    AS '$libdir/orafce', 'nvarchar2';

-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "orafce" ADD FUNCTION "public"."nvarchar2"("public"."nvarchar2", integer, boolean);


ALTER FUNCTION "public"."nvarchar2"("public"."nvarchar2", integer, boolean) OWNER TO "pavel";

--
-- TOC entry 607 (class 1255 OID 33201)
-- Name: nvarchar2_transform("internal"); Type: FUNCTION; Schema: public; Owner: pavel
--

CREATE FUNCTION "public"."nvarchar2_transform"("internal") RETURNS "internal"
    LANGUAGE "c" IMMUTABLE STRICT
    AS '$libdir/orafce', 'orafce_varchar_transform';

-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "orafce" ADD FUNCTION "public"."nvarchar2_transform"("internal");


ALTER FUNCTION "public"."nvarchar2_transform"("internal") OWNER TO "pavel";

the supporting function should be dumped first before function where supporting function is used.

Regards

Pavel

Re: bad dependency in pg_dump output related to support function breaks binary upgrade

From
Tom Lane
Date:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> some Orafce's user reported problems with pg_upgrade. I checked this issue
> and it looks like pg_dump problem:
> ...
> the supporting function should be dumped first before function where
> supporting function is used.

I tried to reproduce this and could not.  It should work, since
ProcedureCreate definitely makes a dependency on the support function.
Can you make a self-contained test case?

            regards, tom lane

Re: bad dependency in pg_dump output related to support function breaks binary upgrade

From
Pavel Stehule
Date:


po 21. 12. 2020 v 17:23 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> some Orafce's user reported problems with pg_upgrade. I checked this issue
> and it looks like pg_dump problem:
> ...
> the supporting function should be dumped first before function where
> supporting function is used.

I tried to reproduce this and could not.  It should work, since
ProcedureCreate definitely makes a dependency on the support function.
Can you make a self-contained test case?

After some deeper investigation I found an old bug in Orafce :-/. I am sorry for the noise.

This old bug is related to introduction aliases types of varchar - nvarchar2 and varchar2. In this age the "in" function can use a protransform column, but there was not a possibility how to set this column externally, and Orafce used dirty update. The value was correct, but the new dependency was not used. Originally it was not a problem, because the transform function was built in. But there was a new issue related to Postgres 12 when these functions were renamed. I fixed this issue by introducing my own wrapping function - but without dependency I broke the binary upgrade.

On Postgres 12 and higher I can use ALTER FUNCTION SUPPORT and all works well. On older platforms I have to hack pg_depend, but it is working too.

Again I am sorry for false alarm

Regards

Pavel


 

                        regards, tom lane