Re: BUG #7758: pg_dump does not correctly dump operators.

Daniel Migowski <dmigowski(at)ikoffice(dot)de> writes:

> DROP OPERATOR IF EXISTS #<#(text,text) CASCADE;
> CREATE OPERATOR #<#(
> PROCEDURE = text_natsort_lt,
> LEFTARG = text,
> RIGHTARG = text,
> COMMUTATOR = #>#,
> RESTRICT = scalarltsel,
> JOIN = scalarltjoinsel);

> DROP OPERATOR IF EXISTS #>#(text,text) CASCADE;
> CREATE OPERATOR #>#(
> PROCEDURE = text_natsort_gt,
> LEFTARG = text,
> RIGHTARG = text,
> COMMUTATOR = #<#,
> RESTRICT = scalargtsel,
> JOIN = scalargtjoinsel);

The second DROP removes the "shell" operator that was created as a
placeholder by the first operator's COMMUTATOR reference. Then when
you create the #># operator for real, it's not linked to the #<#
operator, at least not in that direction. pg_dump is not at fault
here; it's just reporting what's in the catalogs, which is to say a
dangling commutator link.

I believe we've looked at this in the past, and not found any cure
that wasn't worse than the disease. For example, if we were to treat
the first operator's COMMUTATOR reference as a hard dependency, then
the second DROP CASCADE would cascade to remove the first operator,
hardly the outcome you'd want.

My recommendation for the moment is that if you want to write the
script in this style, put all the DROPs first and then create the
operators.

regards, tom lane