Re: CUBE seems a bit confused about ORDER BY

On Wed, Jan 10, 2018 at 8:02 PM, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)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?
>

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.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company