Обсуждение: pg_upgrade is failed for 'plpgsql_call_handler' handler

Hi,

In one of my testing scenario, i found pg_upgrade is failed for 
'plpgsql_call_handler' handler

Steps to reproduce - ( on any supported version of PG)

Perform initdb ( ./initdb -D  d1 ;  ./initdb -D d2)

Start d1 cluster(./pg_ctl -D d1 start) , connect to postgres (./psql 
postgres)  and create this language

postgres=# CREATE TRUSTED LANGUAGE plspl_sm HANDLER plpgsql_call_handler;
CREATE LANGUAGE

stop the server (./pg_ctl -D d1 stop)

perform pg_upgrade ( ./pg_upgrade -d d1 -D d2 -b . B .)

will fail with these message

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 825; 2612 16384 PROCEDURAL LANGUAGE plspl_sm edb
pg_restore: error: could not execute query: ERROR:  could not open 
extension control file 
"/home/edb/pg14/pg/edbpsql/share/postgresql/extension/plspl_sm.control": 
No such file or directory
Command was: CREATE OR REPLACE PROCEDURAL LANGUAGE "plspl_sm";

is this expected ?

-- 
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

> On 3 Jun 2021, at 11:53, tushar <tushar.ahuja@enterprisedb.com> wrote:

> In one of my testing scenario, i found pg_upgrade is failed for 'plpgsql_call_handler' handle

This isn't really a pg_upgrade issue but a pg_dump issue.  The handler, inline
nd validator functions will be looked up among the functions loaded into
pg_dump and included in the CREATE LANGUAGE statement.  However, iff they are
in pg_catalog then they wont be found (pg_catalog is excluded in getFuncs) and
a bare CREATE LANGUAGE statement will be emitted.  This bare statement will
then be interpreted as CREATE EXTENSION.

This is intentional since the language template work in 8.1, before then
pg_dump would look up support functions in pg_catalog.

--
Daniel Gustafsson        https://vmware.com/

Daniel Gustafsson <daniel@yesql.se> writes:
>> On 3 Jun 2021, at 11:53, tushar <tushar.ahuja@enterprisedb.com> wrote:
>> In one of my testing scenario, i found pg_upgrade is failed for 'plpgsql_call_handler' handle

> This is intentional since the language template work in 8.1, before then
> pg_dump would look up support functions in pg_catalog.

I don't see any particular need to support reaching inside the guts
of another PL language implementation, as this test case does.
We'd be perfectly within our rights to rename plpgsql_call_handler
as something else; that should be nobody's business except that
of the plpgsql extension.

But yeah, the behavior you're seeing here is intended to support
normally-packaged languages.  pg_dump won't ordinarily dump objects
in pg_catalog, because it assumes stuff in pg_catalog is to
be treated as built-in.

            regards, tom lane

> On 3 Jun 2021, at 16:12, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Daniel Gustafsson <daniel@yesql.se> writes:
>>> On 3 Jun 2021, at 11:53, tushar <tushar.ahuja@enterprisedb.com> wrote:
>>> In one of my testing scenario, i found pg_upgrade is failed for 'plpgsql_call_handler' handle
>
>> This is intentional since the language template work in 8.1, before then
>> pg_dump would look up support functions in pg_catalog.
>
> I don't see any particular need to support reaching inside the guts
> of another PL language implementation, as this test case does.
> We'd be perfectly within our rights to rename plpgsql_call_handler
> as something else; that should be nobody's business except that
> of the plpgsql extension.
>
> But yeah, the behavior you're seeing here is intended to support
> normally-packaged languages.  pg_dump won't ordinarily dump objects
> in pg_catalog, because it assumes stuff in pg_catalog is to
> be treated as built-in.

Agreed, I don't think there is anything we could/should do here (the lack of
complaints in the archives back that up).

--
Daniel Gustafsson        https://vmware.com/