Обсуждение: BUG #15534: Operators from public schema in trigger WHEN-clauses aresilently allowed despite breaking restores

The following bug has been logged on the website:

Bug reference:      15534
Logged by:          Patrick O'Toole
Email address:      p.otoole@uwyo.edu
PostgreSQL version: 9.6.11
Operating system:   Amazon Linux
Description:

Apologies in advance if this has been raised already.

I encountered a problem while trying to copy a few structures to a separate
database for testing via pg_dump and pg_restore.The issue seems to be that
operators found in the public schema (which may be put there by extensions)
are made unavailable during restore to prevent ambiguity across different
namespaces. In my case, this was a PostGIS operator, but using `\do` from
psql after altering the 'search_path' setting shows this might be a pitfall
that is generally possible against various extensions' custom operators
(e.g. hstore). As such, I'm reporting the problem here first.

Since a typical user may not always be aware of which operators are are
built-ins from pg_catalog and which are made available in schema "public"
via extensions, it might be good to prevent use of unsafe operators or at
least issue a warning on parsing WHEN expressions during trigger-creation.
Otherwise, I might expect Postgres to encode these operators so they can be
restored without issue; the current behavior silently creates surprises
which can halt database-restores attempted later on.

The reproduction-steps I give below use pg_dump and psql started from within
a command-shell.

-- from psql:

DROP DATABASE IF EXISTS temp;
CREATE DATABASE temp;

\c temp

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE t(
    id SERIAL PRIMARY KEY,
    geom GEOMETRY
);

CREATE OR REPLACE FUNCTION f() RETURNS trigger AS $$
BEGIN
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

DROP DATABASE IF EXISTS restore_test;
CREATE DATABASE restore_test;
-- [now exit psql]
\q

# Now from the shell:

# dump schema to file:
pg_dump temp -s -f temp.sql

# restore to empty database:
psql restore_test -f temp.sql

# Receive error:

# CREATE TRIGGER tr BEFORE UPDATE ON public.t FOR EACH ROW WHEN ((new.geom
IS DISTINCT FROM old.geom)) EXECUTE PROCEDURE public.f();
# psql:temp:159: ERROR:  operator is not unique: public.geometry =
public.geometry
# LINE 1: ...E UPDATE ON public.t FOR EACH ROW WHEN ((new.geom IS
DISTINC...
#                                                              ^
# HINT:  Could not choose a best candidate operator. You might need to add
explicit type casts.

The same error will occur if using `pg_dump -F d` and `pg_restore` instead
of psql and the default plain dump. Adding type-casts of ::public.GEOMETRY
has no effect, and the reconstructed node-tree in the WHEN-expression does
not appear different per the restore-script.

This gotcha can explained by performing `\do =` from within psql with
'search_path' as the default "$user",public versus the environment during
restores obtained by doing set_config('search_path','',false).

As an aside, a workaround for the issue is to create a wrapper-function for
the comparison instead of placing it directly in the WHEN clause. Having
tracked my problem down, the answer of "just don't use problem-operators
directly that way" seems obvious, but it might not be for all users, which
is why I'm writing.

Thanks to all,
- Patrick O'Toole

Full OS (uname -a): Linux 4.14.77-69.57.amzn1.x86_64 #1 SMP Tue Nov 6
21:32:55 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
Full Postgres version: PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

>>>>> "PG" == PG Bug reporting form <noreply@postgresql.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)

Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> 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.

Right.  A few months ago I posted a list of problems of this ilk
(IS DISTINCT FROM is far from the only trouble spot).  Too lazy to
search the archives for it right now, but the discussion kinda went
nowhere AFAIR.

            regards, tom lane