Thread: pg_upgrade from PG-14.5 to PG-15.1 failing due to non-existing function

pg_upgrade from PG-14.5 to PG-15.1 failing due to non-existing function

From
Dimos Stamatakis
Date:

Hi hackers,

 

I attempted to perform an upgrade from PG-14.5 to PG-15.1 with pg_upgrade and unfortunately it errors out because of a function that does not exist anymore in PG-15.1.

The function is ‘pg_catalog.close_lb’ and it exists in 14.5 but not in 15.1.

In our scenario we changed the permissions of this function in PG14.5 (via an automated tool) and then pg_upgrade tries to change the permissions in PG15.1 as well.

 

 

Steps to reproduce:

 

  1. Run initdb for 14.5
  2. Run initdb for 15.1
  3. Run psql client on 14.5
    1. postgres=# REVOKE ALL ON FUNCTION close_lb(line, box) FROM $USER;
  4. Run pg_upgrade from 14.5 to 15.1

 

This will error out because pg_upgrade will attempt to REVOKE the persmissions on close_lb on 15.1.

Is there a way to specify which functions/objects to exclude in pg_upgrade?

Thanks in advance!

 

Dimos

(ServiceNow)

Re: pg_upgrade from PG-14.5 to PG-15.1 failing due to non-existing function

From
Christoph Moench-Tegeder
Date:
## Dimos Stamatakis (dimos.stamatakis@servicenow.com):

> In our scenario we changed the permissions of this function in PG14.5
> (via an automated tool) and then pg_upgrade tries to change the
> permissions in PG15.1 as well.

Given that this function wasn't even documented and did nothing but
throw an error "function close_lb not implemented" - couldn't you
revert that permissions change for the upgrade? (if it comes to the
worst, a superuser could UPDATE pg_catalog.pg_proc and set proacl
to NULL for that function, but that's not how you manage ACLs in
production, it's for emergency fixing only).

Regards,
Christoph

-- 
Spare Space



Re: pg_upgrade from PG-14.5 to PG-15.1 failing due to non-existing function

From
David Geier
Date:
Hi,

On 1/25/23 19:38, Dimos Stamatakis wrote:
>
> Hi hackers,
>
> I attempted to perform an upgrade from PG-14.5 to PG-15.1 with 
> pg_upgrade and unfortunately it errors out because of a function that 
> does not exist anymore in PG-15.1.
>
> The function is ‘pg_catalog.close_lb’ and it exists in 14.5 but not in 
> 15.1.
>
> In our scenario we changed the permissions of this function in PG14.5 
> (via an automated tool) and then pg_upgrade tries to change the 
> permissions in PG15.1 as well.
>
Here [1] is a very similar issue that has been reported in 2019.

The patch didn't make it in but it also seems to not fix the issue 
reported by Dimos. The patch in [1] seems to be concerned with changed 
function signatures rather than with dropped functions. Maybe [1] could 
be revived and extended to also ignore dropped functions?

[1] 
https://www.postgresql.org/message-id/flat/f85991ad-bbd4-ad57-fde4-e12f0661dbf0%40postgrespro.ru

-- 
David Geier
(ServiceNow)




Re: pg_upgrade from PG-14.5 to PG-15.1 failing due to non-existing function

From
Dimos Stamatakis
Date:

## Dimos Stamatakis (dimos.stamatakis@servicenow.com):

> In our scenario we changed the permissions of this function in PG14.5
> (via an automated tool) and then pg_upgrade tries to change the
> permissions in PG15.1 as well.

Given that this function wasn't even documented and did nothing but
throw an error "function close_lb not implemented" - couldn't you
revert that permissions change for the upgrade? (if it comes to the
worst, a superuser could UPDATE pg_catalog.pg_proc and set proacl
to NULL for that function, but that's not how you manage ACLs in
production, it's for emergency fixing only).

 

Thanks Christoph! Actually, I already tried reverting the permissions but pg_upgrade attempts to replicate the revert SQL statement as well 😊
It would be nice to make pg_upgrade ignore some statements while upgrading.

As David mentions, we can alter the patch to ignore dropped functions.

Thanks,
Dimos
(ServiceNow)