Re: CUBE seems a bit confused about ORDER BY

On Wed, Jan 10, 2018 at 8:02 PM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

Teodor Sigaev wrote:

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

(It looks like you could get rid of the 'deptype' qual and
dep.refclassid also)