Thread: Relations between operators from pg_amop and classes of operators from pg_opclass

Relations between operators from pg_amop and classes of operators from pg_opclass

From
Anna Rodionova
Date:

Hi!

For each operator from system catalog pg_amop I need to know - a member of which class of operators from system catalog pg_opclass it is (in case if such class of operators exists).


In PostgreSQL versions before v14.0 I was able to get this information from system catalog pg_depend.

In case of existing relation between operator and class of operators - in pg_depend was row, in which objid was equal to the oid of this operator and refobjid was equal to oid of its class of operators.


However in PostgreSQL v14.0 it changed. Now in such rows values of refobjid are equal to values of oid of family of operators (instead of class of operators).


How can I find relations between operators from pg_amop and classes of operators from pg_opclass in v14.0?


Best regards,

Anna Rodionova

Anna Rodionova <anna.rodionova@jetbrains.com> writes:
> For each operator from system catalog pg_amop I need to know - a member of
> which class of operators from system catalog pg_opclass it is (in case if
> such class of operators exists).

> In PostgreSQL versions before v14.0 I was able to get this information from
> system catalog pg_depend.

Not for built-in operators ...

> However in PostgreSQL v14.0 it changed. Now in such rows values of refobjid
> are equal to values of oid of family of operators (instead of class of
> operators).

That's always been possible, but it happens in more cases now.  We made an
effort to not tie operators to opclasses unless they are essential to the
opclass.  The exact rules are index AM dependent, and I don't think
they're documented outside of code comments.  So if you really want to
know, have a look at [1], as well as the mailing list thread about it [2].

> How can I find relations between operators from pg_amop and classes of
> operators from pg_opclass in v14.0?

It's kind of a mistake to assume that there is any such relationship.
Operators are really tied to opfamilies, and have been for many years
--- and those relationships are in pg_amop, you needn't grovel around
in pg_depend to find them.

The only reason to tie an operator to a specific opclass is if a
particular index would necessarily not work without that operator.
The new code reduces the number of cases where we think an operator
is essential to an opclass when it is not really.

            regards, tom lane

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=9f9682783
[2] https://www.postgresql.org/message-id/flat/4578.1565195302%40sss.pgh.pa.us