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
