make raster max extent constraint apply faster at some lose of correctness

[robe]

In #3501 we determined that ST_Extent solves the issue of max array sizes when computing the constraint extent for a raster table.

It conveniently is just as fast as the old way of ST_Collect(..).

The problem is ST_Extent returns a box and not a geometry and raster_columns needs a geometry.

So the easiest both programmatically and performance wise solution is to just set the SRID ST_SetSRID(ST_Extent(,first_srid_in_table)).

That works all fine in at least 90% of cases. The issue arises is if you have a raster table with mixed set of srids.

The most correct thing to do would be to not to set a extent constraint at all in the mixed srid case.

However to determine that you do have mixed would require inspecting each row to determine there is only one srid.

With a query something like:

SELECT MAX(ST_SRID(rast))
FROM some_table
HAVING COUNT(DISTINCT ST_SRID(rast) ) = 1;

This query is much more costly than:

SELECT ST_SRID(rast)
FROM some_table
LIMIT 1;

So in r15243, I took the easier, but granted (some like strk would say less correct way).

I also set the constraint to NOT VALID, because for a big table, do we really need to check if we violated the constraint we formulated from taking the extent of the data. And future would be checked even when constraint is marked as not valid.

Even in a mixed srid case, the constraint would not be violated.

Interestingly I noticed, the SRID constraint and some other do need the default VALID check. Reason being is they take the lazy way too.

SRID extent, simply tries to apply the SRID constraint based on the first raster tile. and the constraint gracefully fails in the check process if you've got mixed and doesn't get created. Same with some others.

In this particular case, the only way the constraint would ever fail is if the extent fails to get created because the extent coordinates are out of bounds of the srid we happened to pick.

So what to do? Does anyone care that if they have a mixed back of spatial ref rasters, they get an extent which kinda works, but may not reflect the true mixed upness of the spatial ref of the table?

By kinda works I mean the checking done with @ ( geometry_within function) from what I can tell just looks at boxes and dispenses with the srid anyway.

[strk]

I shall note that the BOX returned by ST_Extent is also based on floats, not double, so is probably slightly larger than the strict envelope. Is that acceptable ?

[robe]

yes.

[robe]

My desired approach was already applied to 2.4. For 2.3 and 2.2 I'm going with the more proper but annoyingly slower solution of not applying valid and doing due diligence checking that all rasters have the same srid.

[robe]

I'm not going to bother backporting to 2.3 unless there is great demand.