> SQL-query seems too huge for release notes and isn't looking for > materialized view (fixable) and functional indexes with function which > contains this operator somewhere inside (not fixable by this query). I > think, just words is enough.
But the query can be made a little bit shorter and more comprehensible:
SELECT pg_describe_object(dep.classid, dep.objid, dep.objsubid) FROM pg_catalog.pg_extension ext JOIN pg_catalog.pg_depend edep ON edep.refobjid = ext.oid JOIN pg_catalog.pg_operator oper ON oper.oid = edep.objid JOIN pg_catalog.pg_depend dep ON dep.refobjid = oper.oid WHERE ext.extname = 'cube' AND edep.refclassid = 'pg_catalog.pg_extension'::regclass AND edep.classid = 'pg_catalog.pg_operator'::regclass AND edep.deptype = 'e' AND oper.oprname = '~>' AND dep.refclassid = 'pg_catalog.pg_operator'::regclass ;
which returns the following
pg_describe_object ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────── regla «_RETURN» en vista materializada f índice tmp_idx restricción «tmp_c_check» en tabla tmp operador 15 (cube, integer) de familia de operadores gist_cube_ops para el método de acceso gist: ~>(cube,integer) (4 filas)
(after create materialized view f as select * from tmp where c~>1 > 1; )
Yes, it looks better. I didn't notice we can use pg_describe_object() here.
I think this is useful enough. The fact remains that we can't check very well for functions; maybe suggest a LIKE clause to look for ~> anywhere in function source code?
That's an option, but we should note that this check is inexact.
(It looks like you could get rid of the 'deptype' qual and dep.refclassid also)
Since this bugfix should be backpatched to 9.6, there are patches for 9.6 and 10 too.
