Oracle_FDW and expanded SDO_GEOMETRY ordinates

[pauldzy]

I thought I'd open a quick FYI ticket just to address an issue that I don't think involves the Oracle_FDW code directly but that might come up for users.

The ability of Oracle_FDW to pass geometry back and forth between PostgreSQL and Oracle is fantastic. However when comparing the two GIS systems one difference is while PostGIS ordinates are stored as a binary object, Oracle in contrast wraps ordinates into VARRAY storage for which then any single geometry has a maximum size of 1,048,576 ordinates. When your PostGIS geometry overtops that limit oracle_fdw will return

ERROR:  cannot append to ordinate collection
DETAIL:  OCI-22165: given index [1048576] must be in the range of [0] to [1048575]

which is what it should do I think - all is good.

Now while 1,048,576 ordinates must have seemed like a large amount back last century, now with Lidar data and folks commonly using crazy silly levels of precision it not that rare to encounter geometries that need a boatload of ordinates to model.
This is a very old topic - see https://community.oracle.com/message/2127963#2127963
Anyhow Oracle's official response was to provide an alternative version of SDO_GEOMETRY that allows 10 million ordinates. Why 10 million? No idea as right now I am looking at a single polygon of wetlands in Alaska provided by the US Fish and wildlife service that has 5.56 millions vertices and thus would not fit in the "improved" SDO_GEOMETRY either. But moving on, this altered SDO_GEOMETRY is a bit of a poison pill as once you convert your database to using the larger SDO_GEOMETRY you lose interoperability with all other databases using the classic size geometry.

So finally getting back around to the point of my ticket, I wanted to push some larger PostGIS geometries into an Oracle database that has undergone alteration to the larger ordinate limit. And it doesn't work - returns the exact same OCI error. I don't see anywhere in the Oracle FDW code where you limit anything. So it sure seems to be something at the driver level that enforces a 1,048,575 limit. Anyone have any ideas?

Note when implementing the geometry expansion the ordinate type itself is not rebuilt from scratch but rather is altered

CREATE OR REPLACE TYPE MDSYS.SDO_ORDINATE_ARRAY
AS VARRAY(1048576) OF NUMBER
alter type sdo_ordinate_array modify limit 10000000 cascade
/

The takeaway is it appears at the moment that regardless of which SDO_GEOMETRY you have on your Oracle side, Oracle_FDW is limited to moving geometries with no more than 1,048,576 ordinates.

Cheers,
Paul

[laurenz]

Thanks for the heads up; I have created a Wiki page for that.

I agree with you that this seems to be an Oracle limitation; the error message suggests that strongly.