[postgis-users] raster2pgsql, _add_raster_constraint_extent fails with "array size exceeds the maximum allowed"

[Avernar]

I made one last change.  I added ST_Transform in there just for the
case when the SRID constraint is not set and there are more than one
SRID in the raster column.  Only adds 9 seconds to the run time.
Here's what I have now:

CREATE OR REPLACE FUNCTION _add_raster_constraint_extent(
    rastschema name,
    rasttable name,
    rastcolumn name)
  RETURNS boolean AS
$BODY$
    DECLARE
        fqtn text;
        cn name;
        sql text;
        attr text;
        srid integer;
    BEGIN
        fqtn := '';
        IF length($1) > 0 THEN
            fqtn := quote_ident($1) || '.';
        END IF;
        fqtn := fqtn || quote_ident($2);

        cn := 'enforce_max_extent_' || $3;

    sql := 'SELECT ST_SRID('
            || quote_ident($3)
      || ') FROM '
            || fqtn
            || ' LIMIT 1;';
    EXECUTE sql INTO srid;

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

        sql := 'ALTER TABLE ' || fqtn
            || ' ADD CONSTRAINT ' || quote_ident(cn)
            || ' CHECK (st_envelope('
            || quote_ident($3)
            || ') @ ''' || attr || '''::geometry)';
        RETURN _add_raster_constraint(cn, sql);
    END;
    $BODY$
  LANGUAGE plpgsql VOLATILE STRICT
  COST 100;

On Sat, Nov 19, 2016 at 1:53 PM, Regina Obe <lr at pcorp.us> wrote:
> I'm a little suspicious you don't have ST_MemUnion.  That's been around for
> a while (since 1.* days) and hasn't been deprecated.  Which version of
> PostGIS are you using?  And did you upgrade from earlier versions?
>
>
>
> Changing the logic to
>
>
>
> SELECT st_ashexewkb(st_envelope(st_memunion(st_envelope(…
>
>
>
> Should just work.  In thinking about this, what I really would have liked to
> do is just use ST_Extent which takes advantage that it's just collapsing
> boxes and needs to return a box
>
> And does do it one at a time similar to ST_MemUnion, but it has a downside
> that it returns a box and not a geometry with SRID so we'd have to throw a
> ST_SetSRID in there to convert the box to a geometry with srid, but then we
> can dispense with the last ST_Envelope.
>
>
>
> As far as whether you need an extent constraint, there is no harm in leaving
> it out in most cases.  Main reason it exists is for tools like QGIS that
> interrogate the
>
>
>
> raster_columns table to be able to return a quick extent.  I think for
> geometry they use ST_EstimateExtent.
>
> Even then with those tools, I think no biggie if you have another layer
> limiting the map extent.  If you have a table that big, using raster
> overviews is more important.
>
>
>
> One annoying thing about having an extent constraint is you can't add more
> records without removing the constraint and recomputing.  So if you plan to
> add more records,
>
> I would suggest A) dispense with the constraint  or B) Hand-code the extent
> constraint to cover the full area you expect your dataset to cover in
> future.
>
>
>
> Question:  How many rows do you have and what is the pixel width height?
>
>
>
> Hope that helps,
>
> Regina
>
> http://www.postgis.us
>
> http://postgis.net
>
>
>
>
>
>
>
>
> --- ORIGINAL MESSAGE --
>
> I was importing a very large raster set and after a day it failed with
> array size exceeds the maximum allowed when setting the extent
> constraint.
>
> I redid the import again by just doing the table create and then
> adding the data and then doing the final bunch of sql statements
> manually to avoid having the whole thing rollback again.
>
> I first tried the fix discussed here
> https://trac.osgeo.org/postgis/ticket/3501 and implemented here
> https://trac.osgeo.org/postgis/changeset/15115 but that failed with
> the array size error as well.  So I redid the constraints without the
> extent one.
>
> I would try ST_MemUnion but unfortunately that one is missing in my
> installation.  I guess it's because it also handles 3D and the 3D
> option is not compiled in by default on my platform.
>
> So, what would a pl/pgsql script to do what MemUnion does, ie
> accumulate one at a time, look like?
>
> Also, what issues would not having an extent constrain have?
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users