Opened 13 months ago

Closed 3 months ago

#4530 closed defect (wontfix)

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 (23)

comment:1 Changed 13 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 13 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 13 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 13 months ago by strk (previous) (diff)

comment:4 Changed 13 months ago by strk

Easy to reproduce:

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

comment:5 Changed 13 months ago by strk

In 17912:

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

See #4530

comment:6 Changed 13 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 13 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 13 months ago by strk

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

comment:9 Changed 13 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 13 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 13 months ago by strk

Upgrades from 2.3.6 suffer by the same issue

comment:12 Changed 13 months ago by robe

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

comment:13 Changed 13 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 13 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 13 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 13 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 13 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 13 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 13 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 12 months ago by robe

Milestone: PostGIS 3.0.0PostGIS 3.0.1

comment:21 Changed 8 months ago by komzpa

Milestone: PostGIS 3.0.1PostGIS next

comment:22 Changed 8 months ago by komzpa

Milestone: PostGIS nextPostGIS 3.0.2

Milestone renamed

comment:23 Changed 3 months ago by robe

Resolution: wontfix
Status: reopenedclosed

since PostGIS 2.3 has reached EOL and this only seems to affect upgrading from 2.3, I'm going to close out as wont fix and leave it for someone to reopen if there is a pressing need.

Note: See TracTickets for help on using tickets.