PostgreSQL 11 regression failures on topology upgrade caused by pg_proc catalog changes

[robe]

Sorry guys been distracted lately and seems debbie has been chocking on PostgreSQL 11 runs. I think it's changes upstream.

Has been happening since March 3rd.

One issue is topology ​https://debbie.postgis.net/job/PostGIS_Regress/8825/consoleFull

Upgrading PostGIS in 'postgis_reg' using: ALTER EXTENSION postgis UPDATE TO '2.5.0devnext'
Upgrading PostGIS Topology in 'postgis_reg' using: ALTER EXTENSION postgis_topology UPDATE TO '2.5.0devnext'
Died at ../../regress/run_test.pl line 1485.
 failed (Error encountered altering EXTENSION POSTGIS_TOPOLOGY: /var/lib/jenkins/workspace/postgis/tmp/2_5_pg11w64/regress_log)
-----------------------------------------------------------------------------
sh: 1: createlang: not found
CREATE EXTENSION
CREATE EXTENSION
ALTER EXTENSION
ERROR:  column proc.proisagg does not exist
LINE 15: ...nd e.extname = $1 and c.relname = $2 AND COALESCE(proc.prois...
                                                              ^
HINT:  Perhaps you meant to reference the column "proc.prolang".
QUERY:  SELECT 'ALTER EXTENSION ' || e.extname || ' DROP ' || $3 || ' ' || COALESCE(proc.proname || '(' || oidvectortypes(proc.proargtypes) || ')',typ.typname, cd.relname, op.oprname,
				cs.typname || ' AS ' || ct.typname || ') ', opcname, opfname) || ';' AS remove_command
		FROM pg_depend As d INNER JOIN pg_extension As e
			ON d.refobjid = e.oid INNER JOIN pg_class As c ON
				c.oid = d.classid
				LEFT JOIN pg_proc AS proc ON proc.oid = d.objid
				LEFT JOIN pg_type AS typ ON typ.oid = d.objid
				LEFT JOIN pg_class As cd ON cd.oid = d.objid
				LEFT JOIN pg_operator As op ON op.oid = d.objid
				LEFT JOIN pg_cast AS ca ON ca.oid = d.objid
				LEFT JOIN pg_type AS cs ON ca.castsource = cs.oid
				LEFT JOIN pg_type AS ct ON ca.casttarget = ct.oid
				LEFT JOIN pg_opclass As oc ON oc.oid = d.objid
				LEFT JOIN pg_opfamily As ofa ON ofa.oid = d.objid
		WHERE d.deptype = 'e' and e.extname = $1 and c.relname = $2 AND COALESCE(proc.proisagg, false) = $4;
CONTEXT:  PL/pgSQL function postgis_extension_remove_objects(text,text) line 27 at FOR over EXECUTE statement
-----------------------------------------------------------------------------
make[2]: *** [Makefile:79: check] Error 1
make[2]: Leaving directory '/var/lib/jenkins/workspace/postgis/branches/2.5/topology/test'
make[1]: *** [Makefile:131: check] Error 2
make[1]: Leaving directory '/var/lib/jenkins/workspace/postgis/branches/2.5/topology'
make: *** [GNUmakefile:16: check] Error 1
Build step 'Execute shell' marked build as failure
An attempt to send an e-mail to empty list of recipients, ignored.
Triggering a new build of PostgreSQL stop

[robe]

found the culprit, and the dev run missed it because it only fails on upgrade.

​https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fd1a421fe66173fb9b85d3fe150afde8e812cbe4

author	Peter Eisentraut <peter_e@gmx.net>	
	Fri, 2 Mar 2018 09:57:38 -0400 (08:57 -0500)
committer	Peter Eisentraut <peter_e@gmx.net>	
	Fri, 2 Mar 2018 14:48:33 -0400 (13:48 -0500)
commit	fd1a421fe66173fb9b85d3fe150afde8e812cbe4
tree	24c80c87337ec2d1bb46ee8463207d0cfff5ffc3	tree | snapshot
parent	1733460f0205fc6d6bbe4c14911049a918c6e073	commit | diff
Add prokind column, replacing proisagg and proiswindow

The new column distinguishes normal functions, procedures, aggregates,
and window functions.  This replaces the existing columns proisagg and
proiswindow, and replaces the convention that procedures are indicated
by prorettype == 0.  Also change prorettype to be VOIDOID for procedures.

Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Reviewed-by: Michael Paquier <michael@paquier.xyz>

[strk]

proisagg is used in a query by our postgis_extension_helper: extensions/postgis_extension_helper.sql So this is not topology-specific but affects any need to update aggregates (remember the "brave enough" statement ? it's time to show that off ! )

[robe]

Weird it only broke in topology wonder if that's the only thing we are testing.

strk you are brave enough I know you are or perhaps pramsey is braver.

I challenge you two and don't try to cop out with "Can't fight with a girl"

Anyway I took PG11 out of the tests until we fix it. I'm tied up with other things at moment so feel free to work on it if you have time. If you don't will have to wait till I'm less busy.

And if I do it, I'm not back-porting it to 2.4 pramsey you hear that - robe will not back-port. robe does not want people using 2.4 for PG 11 and this is as perfect an excuse as any. Sure they'll still be able to compile, but they can't upgrade

[robe]

I guess since this would prevent upgrading of postgis_topology I should backport fix.

Updating for regular postgis though works fine without fixing. Seems the regular postgis is not calling this function for some reason

[robe]

In 16497:

Revise postgis extension helper functions to support PostgreSQL 11+ (proisagg replaced with prokind)
References #4044 for PostGIS 2.5 (trunk)

[robe]

I've turned back on debbie's testing of 11 and will backport to 2.4.4 if she's happy with the change.

[robe]

In 16498:

upgrade support for PostgreSQL 11
closes #4044 for PostGIS 2.4.4