fully-qualified operators/functions - problems during a restore of a logical dump

[gbroccolo]

Hi,

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.

[robe]

I'm not sure why it wouldn't create them in the right schema. If your extension install is

CREATE EXTENSION postgis SCHEMA postgis;

As I recall, it creates all objects including operators in postgis during restore. I think I'm missing something you are saying.

[robe]

See if my proposal in #3496 will satisfy your needs.

[gbroccolo]

Hi Regina,

Replying to robe:

See if my proposal in #3496 will satisfy your needs.

Yes, it is exactly what I was searching. Thanks!

[robe]

In 15041:

schema qualify function and operator calls in geometry and geography functions

Closes #3496
Closes #3494
Closes #3486
Closes #3076

[robe]

gbroccolo,

Rereading this, I'm not sure my change fixes this particular issue. Can you retest your above example to verify it's fixed by r15041.

I may need to make some additional adjustments.

[gbroccolo]

Hi Regina, I've tried with this simple example (that is really similar to the real case when I opened the ticket), once I compiled PostGIS considering commit 7880db5:

~$ psql -c "CREATE DATABASE geotest" CREATE DATABASE ~$ psql -c "CREATE SCHEMA geo" geotest CREATE SCHEMA ~$ psql -c "ALTER DATABASE geotest SET search_path TO geo" ALTER DATABASE ~$ psql -c "CREATE EXTENSION postgis" geotest CREATE EXTENSION ~$ psql -c "CREATE TABLE geo.tab(i int PRIMARY KEY, g geometry CHECK (g && ST_MakePolygon(ST_GeomFromText('LINESTRING(75.15 29.53,77 29,77.6 29.5, 75.15 29.53)'))))" geotest CREATE TABLE ~$ psql -c "INSERT INTO tab VALUES (0, ST_MakeLine(ST_MakePoint(0,0), ST_MakePoint(100,100)))" geotest INSERT 0 1 ~$ pg_dump -Fc geotest > geotest.dump ~$ psql -c "DROP DATABASE geotest" DROP DATABASE ~$ psql -c "CREATE DATABASE geotest" CREATE DATABASE ~$ pg_restore -d geotest geotest.dump

All worked fine - when I opened the ticket, I obtained the error "operator && does not exists". I've tried also with the postgis_topology extension installed:

~$ psql -c "CREATE DATABASE geotest" CREATE DATABASE ~$ psql -c "CREATE SCHEMA geo" geotest CREATE SCHEMA ~$ psql -c "ALTER DATABASE geotest SET search_path TO geo" ALTER DATABASE ~$ psql -c "CREATE EXTENSION postgis" geotest CREATE EXTENSION ~$ psql -c "CREATE EXTENSION postgis_topology" geotest CREATE EXTENSION ~$ psql -c "CREATE TABLE geo.tab(i int PRIMARY KEY, g geometry CHECK (g && ST_MakePolygon(ST_GeomFromText('LINESTRING(75.15 29.53,77 29,77.6 29.5, 75.15 29.53)'))))" geotest CREATE TABLE ~$ psql -c "INSERT INTO tab VALUES (0, ST_MakeLine(ST_MakePoint(0,0), ST_MakePoint(100,100)))" geotest INSERT 0 1 ~$ pg_dump -Fc geotest > geotest.dump ~$ psql -c "DROP DATABASE geotest" DROP DATABASE ~$ psql -c "CREATE DATABASE geotest" CREATE DATABASE ~$ psql -c "ALTER DATABASE geotest SET search_path TO geo,topology" ALTER DATABASE ~$ pg_restore -d geotest geotest.dump

And all worked properly. So I think that the issue is solved, thanks.

BTW: I encountered the same issue reported in the pull request 113, and fixed it in the same way.