debbie failing on raster upgrade from 3.2, 3.1 to 3.4

[robe]

Might be related to #5338

​https://debbie.postgis.net/job/PostGIS_Regress/24158/consoleFull

07:15:14 psql:../../../raster/test/regress/hooks/hook-before-upgrade-raster.sql:33: NOTICE:  Adding coverage tile constraint required for regular blocking
07:15:14 psql:../../../raster/test/regress/hooks/hook-before-upgrade-raster.sql:33: NOTICE:  Unable to create coverage raster. Cannot add coverage tile constraint: column "sqlerrm" does not exist (42703)
07:15:14 psql:../../../raster/test/regress/hooks/hook-before-upgrade-raster.sql:33: WARNING:  Unable to add constraint: 'regular_blocking'.  Skipping
07:15:14 psql:../../../raster/test/regress/hooks/hook-before-upgrade-raster.sql:33: ERROR:  None of the constraints specified could be added.  Is the schema name, table name or column name incorrect?
07:15:14 CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,text[]) line 119 at RAISE
07:15:14 -----------------------------------------------------------------------------
07:15:14 make: *** [../../../regress/runtest.mk:24: check-regress] Error 3
07:15:14 make: Leaving directory '/var/lib/jenkins/workspace/postgis/branches/3.4/raster/test/regress'
07:15:14 FAIL: postgis_raster script-based upgrade unpackaged3.2--:auto
07:15:14 Testing postgis_raster script-based upgrade unpackaged3.3--:auto
07:15:14 make: Entering directory '/var/lib/jenkins/workspace/postgis/branches/3.4/raster/test/regress'
07:15:14 RUNTESTFLAGS: -v --upgrade-path=unpackaged3.3--:auto -v --raster

[robe]

What's interesting is woodie is doing the same upgrade tests and is not failing. So perhaps something wrong with my backports to older versions.

Only difference in theory between woodie's

oading unpackaged components from /usr/share/postgresql/15/contrib/postgis-3.3
1349s
7138
Loading PostGIS into 'postgis_reg'
1349s
7139
Upgrading from postgis 3.3.2
1350s
7140
Running before-upgrade-script /woodpecker/src/git.osgeo.org/gitea/postgis/postgis/regress/hooks/hook-before-upgrade.sql
1350s
7141
Upgrading PostGIS in 'postgis_reg' 

is that debbie would be upgrading from a not yet released version of postgis 3.3 which might have the new patch.

[robe]

She's not failing at the moment because I think she had a postgresql build since which wiped out all installed versions.

[robe]

I changed her to not build the stable branches so frequently to allow old postgis installs to build up so we have many more postgis versions to upgrade from. I expect this issue to come back soon, but we'll see.

[strk]

The error column "sqlerrm" does not exist (42703) suggests a bug in the version of PostGIS that your test ends up loading, which for an upgrade-path of unpackaged3.3--:auto is whatever version is in ${PG_SHAREDIR}/contrib/postgis-3.3

Now, with a quick look at postgis-3.3/rtpostgis.sql (for 3.3.3dev) I found this snippet which seems suspicious:

    -- rasterize extent
    BEGIN
      _covrast := ST_AsRaster(_covextent, _scalex, _scaley, '8BUI', 1, 0, NULL, NULL, _skewx, _skewy);
      IF _covrast IS NULL THEN
        RAISE NOTICE 'Unable to create coverage raster. Cannot add coverage tile constraint: % (%)',
          SQLERRM, SQLSTATE;
        RETURN FALSE;
      END IF;

IN this case the SQLERRM is probably not set because the code is NOT in an exception handling block. The problem should then arise when the ST_AsRaster call performed by _add_raster_constraint_coverage_tile returns NULL

[strk]

I'm surprised we don't see this error in ALL bots, but maybe Debbie is the only one testing upgrades from yet-to-be-released PostGIS old versions ?

[strk]

The bug was introduced by commit [97ba109a541870b526667431397bc2666bca452e/git] (July 2014!)

[Sandro Santilli <strk@…>]

In 18feccc/git:

Do not use SQLERRM when not defined

References #5341 in master branch (3.4.0dev)

[strk]

I'm trying to reproduce this locally but don't seem to be able to. The problem should be triggered by ST_Metadata returning NULL for some of the parameters passed to ST_AsRaster, which would then return NULL. If that's the case the current code would raise a DEBUG. Any chance you have those DEBUG logs activated in debbie's log ?

[Sandro Santilli <strk@…>]

In b30c2b81/git:

Do not use SQLERRM when not defined

References #5341 in 3.0 branch (3.0.9dev)

[Sandro Santilli <strk@…>]

In a90ca05/git:

Do not use SQLERRM when not defined

References #5341 in 3.1 branch (3.1.9dev)

[Sandro Santilli <strk@…>]

In bac93bd/git:

Do not use SQLERRM when not defined

References #5341 in 3.2 branch (3.2.5dev)

[Sandro Santilli <strk@…>]

In 8ec4288/git:

Do not use SQLERRM when not defined

References #5341 in 3.3 branch (3.3.3dev)

[strk]

I've fixed the reference to unexisting variable in all branches from 3.4 back to 3.0 but I'm not closing this ticket because even without referencing the missing variable, given the conditions that seem to have been detected by Debbie you should still see error:

ERROR:  None of the constraints specified could be added.

It would be nice if you could re-produce the issue.

[strk]

I'm closing as I believe the problem was fixed. Debbie is trouble in other ways at the moment so it's not worth waiting for her to re-produce this issue. Eventually it can be re-opened.

[robe]

This is still failing on unpackaged to packaged and packaged to unpackaged.

[Regina Obe <lr@…>]

In 0dae51d/git:

Turn off unpackaged -> packaged tests as well
References #5341 try to quiet debbie's failures

[robe]

I reopened because of the failures happening on PG14

​https://debbie.postgis.net/job/PostGIS_Regress/24560/consoleFull

6:48:19 psql:../../../raster/test/regress/hooks/hook-before-upgrade-raster.sql:33: NOTICE:  Adding coverage tile constraint required for regular blocking
16:48:19 psql:../../../raster/test/regress/hooks/hook-before-upgrade-raster.sql:33: NOTICE:  Unable to create coverage raster: ST_AsRaster returned NULL.
16:48:19 psql:../../../raster/test/regress/hooks/hook-before-upgrade-raster.sql:33: WARNING:  Unable to add constraint: 'regular_blocking'.  Skipping
16:48:19 psql:../../../raster/test/regress/hooks/hook-before-upgrade-raster.sql:33: ERROR:  None of the constraints specified could be added.  Is the schema name, table name or column name incorrect?
16:48:19 CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,text[]) line 119 at RAISE
16:48:19 -----------------------------------------------------------------------------
16:48:19 make: *** [../../../regress/runtest.mk:24: check-regress] Error 3
16:48:19 make: Leaving directory '/var/lib/jenkins/workspace/postgis/branches/3.4/raster/test/regress'
16:48:19 FAIL: postgis_raster extension upgrade unpackaged3.1--3.4.0dev
16:48:19 Testing postgis_raster extension upgrade unpackaged3.2--3.4.0dev
16:48:19 make: Entering directory '/var/lib/jenkins/workspace/postgis/branches/3.4/raster/test/regress'
16:48:19 RUNTESTFLAGS: -v --extension --upgrade-path=unpackaged3.2--3.4.0dev -v --raster
16:48:19 RUNTESTFLAGS_INTERNAL: --before-upgrade-script ../../../raster/test/regress/hooks/hook-before-upgrade-raster.sql  --after-upgrade-script ../../../raster/test/regress/hooks/hook-after-upgrade-raster.sql

The error is different from before but still same area of failure.

Seems to be only an issue going from 3.1 -> 3.4 or 3.2-- 3.4 . 3.3--> 3.4 is fine.

[strk]

It could be addrasterconstraints() behaviour changed in 3.3 and our use of it from the before-upgrade-raster hook is incompatible. The idea of that call before upgrade was to test the fix for #5338

[strk]

The manual doesn't say anything about behavioural changes: ​https://postgis.net/docs/manual-3.3/RT_AddRasterConstraints.html

[strk]

Note I still cannot reproduce locally:

../regress/run_test.pl -v --extension --raster --upgrade-path unpackaged3.2--:auto --before-upgrade-script ../raster/test/regress/hooks/hook-before-upgrade-raster.sql --after-upgrade-script ../raster/test/regress/hooks/hook-after-upgrade-raster.sql ../raster/test/regress/box3d.sql

Works fine for me, and /tmp/pgis_reg/regress_log nicely says:

psql:../raster/test/regress/hooks/hook-before-upgrade-raster.sql:33: NOTICE:  Adding coverage tile constraint required for regular blocking
psql:../raster/test/regress/hooks/hook-before-upgrade-raster.sql:33: NOTICE:  Adding spatially unique constraint required for regular blocking
 addrasterconstraints 
----------------------
 t
(1 row)

What would the possible reasons why ST_AsRaster would return NULL in Debbie ?

[Sandro Santilli <strk@…>]

In 194b27a/git:

Enable upgrade-from-unpackaged tests by default, allow disabling

Pass --skip-unpackaged to skip those tests

References #5573
References #5341