Opened 6 months ago

Last modified 5 weeks ago

#4530 reopened defect

upgrade postgis 2.3 to postgis 3.0.0rc1 fails at raster

Reported by: robe Owned by: strk
Priority: medium Milestone: PostGIS 3.0.2
Component: build/upgrade/install Version: master
Keywords: Cc:

Description

Is this a new test?

https://dronie.osgeo.org/postgis/postgis/696/1/2

/usr/share/postgresql/9.5/contrib/postgis-2.3/rtpostgis_proc_set_search_path.sql
ERROR: query string argument of EXECUTE is null
CONTEXT: PL/pgSQL function _add_raster_constraint_extent(name,name,name) line 27 at EXECUTE
PL/pgSQL function addrasterconstraints(name,name,name,text[]) line 104 at assignment
PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
Something went wrong adding raster constraints to upgrade_test:
make: *** [check] Error 1
make: Leaving directory `/drone/src/raster/test/regress'
FAIL: upgrade unpackaged2.3--3.0.0rc1 

I don't remember sing this one. Then again seems to be erroring on install postgis-2.3 before it even gets to the upgrade step.

Change History (22)

comment:1 Changed 6 months ago by pramsey

Comment it out. The upgrade tests went in too fast and do not have consistent results across different PgSQL versions.

comment:2 Changed 6 months ago by robe

Milestone: PostGIS 3.0.0PostGIS 3.0.1

I don't even know where to comment this out - I'll leave for strk to sort out.

comment:3 Changed 6 months ago by strk

I think it's a legit failure. The code at hand is raster/rt_pg/rtpostgis.sql.in around line 7490. Function _add_raster_constraint_extent(rastschema name, rasttable name, rastcolumn name) . The offending "null argument to EXECUTE" comes from here:

    sql := 'SELECT @extschema@.st_ashexewkb( @extschema@.st_setsrid( @extschema@.st_extent( @extschema@.st_envelope('
      || quote_ident($3)
      || ')), ' || srid || ')) FROM '
      || fqtn;
    EXECUTE sql INTO attr;

I bet srid is the null value, propagating to the whole sql . Here's how it is fetched:

    sql := 'SELECT @extschema@.ST_SRID('
      || quote_ident($3)
      || ') FROM '
      || fqtn
      || ' WHERE '
      || quote_ident($3)
      || ' IS NOT NULL LIMIT 1;';
                EXECUTE sql INTO srid;

Now.. what if the raster table is empty ? I guess SRID remains NULL, making a NULL sql and getting to the error. I'd rather fix than comment out.

Last edited 6 months ago by strk (previous) (diff)

comment:4 Changed 6 months ago by strk

Easy to reproduce:

CREATE TABLE ra(r raster);
SELECT AddRasterConstraints('ra','r');

comment:5 Changed 6 months ago by strk

In 17912:

Avoid sending two queries at once, to more easily spot actual errors

See #4530

comment:6 Changed 6 months ago by robe

Milestone: PostGIS 3.0.1PostGIS 3.0.0

strk I flipped branches already, so you need to backport this to 3.0. in the branches/3.0

comment:7 Changed 6 months ago by strk

With r17915 I made source version visible, on upgrade. I don't have a fix to backport yet. Dronie will tell us more about the upgrade: https://dronie.osgeo.org/postgis/postgis/727

comment:8 Changed 6 months ago by strk

Upgrading from postgis 2.3.5
ERROR: query string argument of EXECUTE is null 

comment:9 Changed 6 months ago by strk

I can easily reproduce locally via:

regress/run_test.pl -v --upgrade --upgrade-path unpackaged2.3.5--:auto --extension --raster raster/test/regress/rt_io.sql

comment:10 Changed 6 months ago by strk

Can also be reproduced without involving the "unpackaged":

regress/run_test.pl --raster --extension --upgrade --upgrade-path 2.3.5--:auto raster/test/regress/rt_io.sql

comment:11 Changed 6 months ago by strk

Upgrades from 2.3.6 suffer by the same issue

comment:12 Changed 6 months ago by robe

So is this a fix that needs to be put in place for 2.3?

comment:13 Changed 5 months ago by strk

upgrades _FROM_ 2.3.6 have this bug, not _TO_, so the fix needs to be in place for 3.0.0 so far. I did NOT test to upgrade _TO_ 2.5, 2.4, 2.3 -- it may be a good idea to do, to see if we also need a fix there.

Note that the problem is AddRasterConstraints failing when called against a table with some NULL and some not-NULL raster values (as prepared by run_test.pl). We might avoid this case from run_test.pl as I don't think it's something we need for testing.

What I mean is that it is NOT the upgrade itself that fails, just the _testing_ of the upgrade.

comment:14 Changed 5 months ago by strk

Resolution: fixed
Status: newclosed

In 17920:

Avoid use of bogus AddRasterConstraint? when running upgrade tests

Works around bug #4547

Closes #4530

comment:15 Changed 5 months ago by strk

In 17923:

Avoid use of bogus AddRasterConstraint? when running upgrade tests

Works around bug #4547 for 3.0 branch

Closes #4530 in 3.0 branch

comment:16 Changed 5 months ago by strk

In 17930:

Avoid use of bogus AddRasterConstraint? when running upgrade tests

Works around bug #4547 for 2.5 branch

Closes #4530 in 2.5 branch

comment:17 Changed 5 months ago by strk

In 17933:

Avoid use of bogus AddRasterConstraint? when running upgrade tests

Works around bug #4547 for 2.4 branch

Closes #4530 in 2.4 branch

comment:18 Changed 5 months ago by strk

In 17935:

Avoid use of bogus AddRasterConstraint? when running upgrade tests

Works around bug #4547 for 2.3 branch

Closes #4530 in 2.3 branch

comment:19 Changed 5 months ago by strk

Resolution: fixed
Status: closedreopened

It looks like the workaround is still not good as of branch 2.5: https://gitlab.com/postgis/postgis/-/jobs/323670065

I'm reopening this..

comment:20 Changed 5 months ago by robe

Milestone: PostGIS 3.0.0PostGIS 3.0.1

comment:21 Changed 5 weeks ago by komzpa

Milestone: PostGIS 3.0.1PostGIS next

comment:22 Changed 5 weeks ago by komzpa

Milestone: PostGIS nextPostGIS 3.0.2

Milestone renamed

Note: See TracTickets for help on using tickets.