[postgis-tickets] [PostGIS] #3501: [raster] add raster constraint max extent exceeds array size limit

[PostGIS]

#3501: [raster] add raster constraint max extent exceeds array size limit
------------------------+---------------------------
  Reporter:  hypostase  |      Owner:  dustymugs
      Type:  defect     |     Status:  reopened
  Priority:  medium     |  Milestone:  PostGIS 2.2.3
 Component:  raster     |    Version:  2.2.x
Resolution:             |   Keywords:
------------------------+---------------------------

Comment (by robe):

 avernar,

 Given you said it takes 30 minutes to create the check constraint, I'm
 tempted to just CREATE it with NOT VALID.  Since the new constraint was
 created from the extent, it should be valid for existing, so no need to
 check it on existing data and it will still work for future inserts and
 most importantly will still show in raster_columns.

 But it would shave off 30 minutes from restore and creation for your big
 case.

 I did a test and a not valid constraint comes back not valid so should
 save on restore as well.  Only issue is with user perception.  For 2.4
 I'll definitely do that.  Not sure if I should for 2.3 though.

 That useless check of existing data has always annoyed me since we are
 creating the constraints from the data.

 There are some like srid I'll keep since we count on them failing if more
 than one srid to not create the constraint.

 So the add constraint will then end up looking like:


 {{{
        sql := 'ALTER TABLE ' || fqtn
             || ' ADD CONSTRAINT ' || quote_ident(cn)
             || ' CHECK (st_envelope('
             || quote_ident($3)
             || ') @ ''' || attr || '''::geometry) NOT VALID';
         RETURN _add_raster_constraint(cn, sql);
 }}}


 I also did a check on @. I guess it really only checks box intersects so
 ignores the SRID.
 That means that mixed srids compared against a single srid will work fine
 as long as we don't attempt an ST_Transform (the transform would screw up
 the box and make it not work since PostGIS can't auto transform).

 I'm torn between doing a more intensive srid check (takes 9 secs for
 379,232 sample I did),
 or just as you have take the first srid and tough if they mixed a bunch of
 srids, their extent will show the first one - their penalty for being
 dirty mixing srids in same table.  I feel a little dirty making that
 short-cut for the sake of performance though but I feel it's a rare enough
 case and a case that if people hit it there is no harm, except my
 conscience (and pramsey or strk or dustymugs screaming at me :) ).

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3501#comment:12>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.