Dump/Restore of raster table fails on enforce_coverage_tile_rast constraint

[pramsey]

It seems that adding a "regular_blocking" constraint to a raster table will cause a pg_dump/pg_restore cycle to fail, due to the component functions in the constraint being out of the path during the restore stage.

The attached files demonstrate the problem.

The SH file prepares an empty DB, runs the SQL setup file, and then runs the dump/restore.

The SQL file is a stripped down version of a regression test file the just makes the table, adds some rasters, and applies the constraint.

The problem is visible at least as far back as 3.0 and maybe even further. The fact that it has been unreported this long probably speaks to how rarely the "regular_blocking" constraint is used.

[pramsey]

control the running of the reproduction case

[pramsey]

create a test table and apply the breaking constraint

[robe]

The issue here is with functions used in constraints that reference other functions. I'm frustrated I can't incorporate a test for this in our chain easily for a couple of reasons

1) It would only pass if testing under --extension because it's only in extension scripts we can schema qualify. For non-extension install we just strip the @extschema@. variables

2)

@strk I thought I was hoping I could use

--dumprestore --extension

But seems dumprestore is just testing dumping an restoring an empty database so not that useful. I thought it would run the tests, dump and then restore?

[Regina Obe <lr@…>]

In b5b3a6c6/git:

Schema qualify raster functions used in raster check constraint
functions.

References #5338 for PostGIS 3.4.0dev

[Regina Obe <lr@…>]

In 38761da/git:

Schema qualify raster functions used in raster check constraint
functions.

References #5338 for PostGIS 3.4.0dev

[Regina Obe <lr@…>]

In 5900020/git:

Schema qualify raster functions used in
raster check constraint functions.
References #5338 for PostGIS 3.3.3

[robe]

Replying to Regina Obe <lr@…>:

In 38761da/git:

Schema qualify raster functions used in raster check constraint
functions.

References #5338 for PostGIS 3.4.0dev

References #5338 for PostGIS 3.3.3. Mistyped commit log entry.

[Regina Obe <lr@…>]

In 15eaa9e/git:

Schema qualify raster functions used
in raster check constraint functions.

References #5338 for PostGIS 3.2.4

[Regina Obe <lr@…>]

In c0a84b9/git:

Schema qualify raster functions used in
raster check constraint functions.

References #5338 for PostGIS 3.1.9

[Regina Obe <lr@…>]

In 400317a/git:

Dump/Restore of raster table fails on
enforce_coverage_tile_rast constraint

Closes #5338 for PostGIS 3.0.9

[strk]

Regina I think my pull request ​https://git.osgeo.org/gitea/postgis/postgis/pulls/112 would change the behavior to run before-upgrade scripts PRIOR to dump so that this bug can be tested, could you verify ?

In order to test you would:

1) Add the offending data in hook-before-upgrade-raster.sql 2) Use run_test.pl as you tried to do (but the versino in my pull request above)

[strk]

Regina I've rebased the code in ​https://git.osgeo.org/gitea/postgis/postgis/pulls/112 can you see if it can be reproduced now ? The call is something like:

regress/run_test.pl \
  --raster -v --dumprestore --extension \
  --before-upgrade-script raster/test/regress/hooks/hook-before-upgrade-raster.sql \
  raster/test/regress/tickets.sql 

If things work as I'd expect you just need to make sure hook-before-upgrade-raster.sql adds the coverage tile constraint to some raster table

[strk]

Sorry you also need --upgrade switch. I could reproduce:

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 4506; 0 18661757 TABLE DATA upgrade_test_raster strk
pg_restore: error: COPY failed for table "upgrade_test_raster": ERROR:  function st_samealignment(public.raster, public.raster) does not exist
LINE 1: SELECT NOT ST_SameAlignment(rast, coverage)
                   ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT NOT ST_SameAlignment(rast, coverage)
CONTEXT:  PL/pgSQL function public.st_iscoveragetile(public.raster,public.raster,integer,integer) line 10 at IF
COPY upgrade_test_raster, line 1: "010000010000000000000000400000000000000040000000000000F03F000000000000F03F00000000000000000000000000..."
pg_restore: warning: errors ignored on restore: 1

[Sandro Santilli <strk@…>]

In ecea1b9/git:

run_test.pl: run before-upgrade hooks before dump, use postgis_restore.pl when needed

We can only use pg_restore when the dumped database is extension based.

See #5075 and #5338