Opened 20 months ago

Closed 20 months ago

Last modified 20 months ago

#5338 closed defect (fixed)

Dump/Restore of raster table fails on enforce_coverage_tile_rast constraint

Reported by: pramsey Owned by: robe
Priority: medium Milestone: PostGIS 3.0.9
Component: raster Version: 3.0.x
Keywords: Cc:

Description

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.

Attachments (2)

rastercheck.sh (338 bytes ) - added by pramsey 20 months ago.
control the running of the reproduction case
rastercheck.sql (1.1 KB ) - added by pramsey 20 months ago.
create a test table and apply the breaking constraint

Download all attachments as: .zip

Change History (16)

by pramsey, 20 months ago

Attachment: rastercheck.sh added

control the running of the reproduction case

by pramsey, 20 months ago

Attachment: rastercheck.sql added

create a test table and apply the breaking constraint

comment:1 by robe, 20 months ago

Owner: changed from pramsey to robe

comment:2 by robe, 20 months ago

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?

comment:3 by Regina Obe <lr@…>, 20 months ago

In b5b3a6c6/git:

Schema qualify raster functions used in raster check constraint
functions.

References #5338 for PostGIS 3.4.0dev

comment:4 by Regina Obe <lr@…>, 20 months ago

In 38761da/git:

Schema qualify raster functions used in raster check constraint
functions.

References #5338 for PostGIS 3.4.0dev

Last edited 20 months ago by robe (previous) (diff)

comment:5 by Regina Obe <lr@…>, 20 months ago

In 5900020/git:

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

in reply to:  4 comment:6 by robe, 20 months ago

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.

comment:7 by Regina Obe <lr@…>, 20 months ago

In 15eaa9e/git:

Schema qualify raster functions used
in raster check constraint functions.

References #5338 for PostGIS 3.2.4

comment:8 by Regina Obe <lr@…>, 20 months ago

In c0a84b9/git:

Schema qualify raster functions used in
raster check constraint functions.

References #5338 for PostGIS 3.1.9

comment:9 by Regina Obe <lr@…>, 20 months ago

Resolution: fixed
Status: newclosed

In 400317a/git:

Dump/Restore of raster table fails on
enforce_coverage_tile_rast constraint

Closes #5338 for PostGIS 3.0.9

comment:10 by strk, 20 months ago

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)

comment:11 by strk, 20 months ago

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

comment:12 by strk, 20 months ago

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

comment:13 by Sandro Santilli <strk@…>, 20 months ago

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

comment:14 by robe, 20 months ago

Component: postgisraster
Note: See TracTickets for help on using tickets.