Unable to upgrade from PostGIS 2.4.3 to 2.4.4

[Rikiar]

Hello, I work for Heroku Postgres, where we provide Postgres as a service for our customers. A fair number of these customers use the PostGIS extension, which we install and support. I currently am working with a customer, who are running PostGIS 2.4.3 on Postgres 9.6.8 and are attempting to update to PostGIS 2.4.4. They are encountering this error:

sql_error_code = 42P13 HINT:  Use DROP FUNCTION box2d_in(cstring) first.
sql_error_code = 42P13 STATEMENT:  ALTER EXTENSION "postgis" UPDATE;

When attempting to follow the hint and performing the DROP FUNCTION for box2d_in(cstring), I get the following error:

DROP FUNCTION box2d_in(cstring);
ERROR:  cannot drop function box2d_in(cstring) because extension postgis requires it
HINT:  You can drop extension postgis instead.

I didn't see anything in the release notes for 2.4.5 regarding the behavior I am seeing, so what I am seeing seems to indicate that there is an error in the PostGIS update script. If this seems inaccurate, please let me know.

To reproduce:

• Create a 9.6.8 Postgres cluster
• Install PostGIS 2.4.3
• Run CREATE EXTENSION postgis;
• Install PostGIS 2.4.4
• Run ALTER EXTENSION "postgis" UPDATE;

[pramsey]

I'm not seeing this:

heroku=# select version();
                                                      version                                                      
-------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.9 on x86_64-apple-darwin17.7.0, compiled by Apple LLVM version 10.0.0 (clang-1000.11.45.2), 64-bit
(1 row)

heroku=# create extension postgis with version '2.4.3';
CREATE EXTENSION

heroku=# alter extension postgis update to '2.4.4';
ALTER EXTENSION

[msakrejdasfdc]

Thanks for attempting to repro. I've taken on investigating the issue we ran into on our side, and I'm still looking into it. We noticed you tried the repro with Postgres 9.6.9 rather than 9.6.8, where we ran into the problem. I suspect the answer is no, but do you think there is any way that point release difference could have affected your ability to reproduce?

[komzpa]

Can it happen that the customer had a populated db of an old version with legacy.sql applied?

[msakrejdasfdc]

Maybe, but as far as I can tell, the versions of legacy.sql we have don't involve any changes to box2d_in. I just noticed my colleague did not paste the full error; here are all the relevant pieces:

LOG:  statement: ALTER EXTENSION "postgis" UPDATE;
ERROR:  cannot change return type of existing function
HINT:  Use DROP FUNCTION box2d_in(cstring) first.

I checked the metadata about the function on the customer's 2.4.3 install versus a fresh 2.4.4, and it looks like the differences are pretty trivial:

Fresh 2.4.4:

=> \df+ box2d_in
List of functions
-[ RECORD 1 ]-------+----------
Schema              | public
Name                | box2d_in
Result data type    | box2d
Argument data types | cstring
Type                | normal
Volatility          | immutable
Parallel            | safe
Owner               | postgres
Security            | invoker
Access privileges   | 
Language            | c
Source code         | BOX2D_in
Description         | 

The 2.4.3 where we see the upgrade issue:

=> \df+ box2d_in
List of functions
-[ RECORD 1 ]-------+-------------
Schema              | public
Name                | box2d_in
Result data type    | public.box2d
Argument data types | cstring
Type                | normal
Volatility          | immutable
Parallel            | unsafe
Owner               | postgres
Security            | invoker
Access privileges   | 
Language            | c
Source code         | BOX2D_in
Description         | 

So basically the only differences are

• schema-qualified return type in the 2.4.3 version (i.e., public.box2d instead of box2d)
• Parallel: unsafe in 2.4.3 versus safe in 2.4.4

Everything else appears identical. I tried running the extension update again on a copy of the database but I get this error:

=> begin; alter extension postgis update;
BEGIN
ERROR:  cannot create temporary table within security-restricted operation

I've tried looking up this error, but that led me to this comment in tablecmds.c in Postgres source:

    /*                                                                                                                                                                                                          
     * Security check: disallow creating temp tables from security-restricted                                                                                                                                   
     * code.  This is needed because calling code might not expect untrusted                                                                                                                                    
     * tables to appear in pg_temp at the front of its search path.                                                                                                                                             
     */
    if (stmt->relation->relpersistence == RELPERSISTENCE_TEMP
        && InSecurityRestrictedOperation())
        ereport(ERROR,
                (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
                 errmsg("cannot create temporary table within security-restricted operation")));

I'm not sure what this means or why I'm hitting it. Any ideas?

[msakrejdasfdc]

Oh and another odd thing is that I tried a CREATE OR REPLACE FUNCTION on a dummy function with similar changes to the signature (i.e., only parallel and the schema-qualification of the return type changing), and both of those seem to REPLACE the original function just fine.

[msakrejdasfdc]

I think the ALTER EXTENSION error may have to do with our usage of pgextwlist, so I tried running the update as a superuser. I was able to reproduce the original error, and then in investigating further, I ran across this:

=> select null::box2d;
ERROR:  type "box2d" is only a shell
LINE 1: select null::box2d;
                     ^

however, fully-qualifying the type does seem to work:

=> select null::public.box2d;
 box2d 
-------
 
(1 row)

the search_path does include the public schema:

=> show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

Any ideas?