Thread

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

   Dimos Stamatakis <dimos.stamatakis@servicenow.com> — 2023-01-25T18:38:55Z

   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
        *   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)
   

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

   Christoph Moench-Tegeder <cmt@burggraben.net> — 2023-01-25T19:06:50Z

   ## 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
   
   
   
   

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

   David Geier <geidav.pg@gmail.com> — 2023-01-26T09:14:05Z

   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)
   
   
   
   
   

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

   Dimos Stamatakis <dimos.stamatakis@servicenow.com> — 2023-01-26T13:10:36Z

   ## 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)