fully-qualified operators/functions - problems during a restore of a logical dump
|Reported by:||gbroccolo||Owned by:||pramsey|
|Keywords:||fully-qualified, operators, functions||Cc:|
I'm wondering about the following case: considering the restore of a *logical* dump of a database with the PostGIS extension installed, containing tables with some fields with a CHECK constraint that involves some PostGIS operators/functions, e.g.
CREATE TABLE t(
i integer PRIMARY KEY, g geometry CHECK (g && ST_Polygon(ST_GeomFromText('LINESTRING(75.15 29.53,77 29,77.6 29.5, 75.15 29.53)'), 4326))
The restore will recreate all PostGIS operators/functions, but not in the right schema (postgis), and should raise errors like "ERROR: operator does not exist: postgis.geometry && postgis.geometry" in my example, since PostGIS operators/functions are not restored where expected.
The issue could be avoided if the operators and the functions would be fully-qualified (i.e. the schema is specified in the DDL during their creation). In my example, the workaround was to define the && operator in the public schema, where the restore is done.