Re: BUG #15534: Operators from public schema in trigger WHEN-clauses are silently allowed despite breaking restores

>>>>> "PG" == PG Bug reporting form <noreply(at)postgresql(dot)org> writes:

PG> I encountered a problem while trying to copy a few structures to a
PG> separate database for testing via pg_dump and pg_restore.The issue
PG> seems to be that operators found in the public schema (which may be
PG> put there by extensions) are made unavailable during restore [...]

The problem is a bit more subtle:

PG> CREATE TRIGGER tr BEFORE UPDATE ON t FOR EACH ROW WHEN ( NEW.geom IS
PG> DISTINCT FROM OLD.geom ) EXECUTE PROCEDURE f();

What's happening here is that IS DISTINCT FROM has a hidden search_path
search in it, that ruleutils doesn't (and can't) handle.

If the clause contained a normal operator not in pg_catalog, it'd get
dumped like this:

CREATE TRIGGER ... WHEN ((old.value OPERATOR(public.<>) new.value)) ...

with an explicit schema for the operator. But IS DISTINCT FROM can't be
used in an OPERATOR clause that way, and the deparse code for
DistinctExpr pays no attention to the schema and assumes it can just
output IS DISTINCT FROM.

Really, IS DISTINCT FROM should get the equality operator from an
opclass rather than assuming it is named "=" and findable via the
search_path. This has been broken since forever, but perhaps this bug
will provide an incentive to get it fixed.

--
Andrew (irc:RhodiumToad)