Collected from the PG bugs email list.
|Reported by||Patrick O'Toole|
Body of first available message related to this bug follows.
The following bug has been logged on the website: Bug reference: 15534 Logged by: Patrick O'Toole Email address: (redacted) 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
|2018-12-01 03:25:05+00||=?utf-8?q?PG_Bug_reporting_form?=||BUG #15534: Operators from public schema in trigger WHEN-clauses are silently allowed despite breaking restores|
|2018-12-01 14:40:08+00||Andrew Gierth||Re: BUG #15534: Operators from public schema in trigger WHEN-clauses are silently allowed despite breaking restores|
|2018-12-01 16:42:59+00||Tom Lane||Re: BUG #15534: Operators from public schema in trigger WHEN-clauses are silently allowed despite breaking restores|