Opened 21 months ago

Closed 16 months ago

Last modified 16 months ago

#3494 closed enhancement (fixed)

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

Reported by: gbroccolo Owned by: pramsey
Priority: low Milestone: PostGIS PostgreSQL
Component: postgis Version: 2.2.x
Keywords: fully-qualified, operators, functions Cc:

Description

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.

Change History (6)

comment:1 Changed 21 months ago by 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.

comment:2 Changed 21 months ago by robe

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

comment:3 in reply to:  2 Changed 20 months ago by 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!

comment:4 Changed 16 months ago by robe

Resolution: fixed
Status: newclosed

In 15041:

schema qualify function and operator calls in geometry and geography functions

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

comment:5 Changed 16 months ago by 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.

comment:6 Changed 16 months ago by 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.

Note: See TracTickets for help on using tickets.