PostGIS Defect : Describe schema fails when advanced types present

[Mike Toews]

I've successfully added/edited spatial data in AutoCAD Map 3D 2008 using a PostgreSQL schema where the columns use basic types (integer, character varying, etc.).

However, if I add a column to a table in that schema (spatial or not) with an advanced type, such as text[], integer[], or timestamp with time zone, the describe schema fails completely and I cannot add or use anything. My error are (e.g.):

• The '_int4' data type is not supported by PostGIS provider.
• The 'timestamptz' data type is not supported by PostGIS provider.

I define custom types as well (such as enums), but I dread to attempt anything more with the current situation.

For "not supported" types, the data should be cast to text for FDO use (like ODBC), and and cast back from text to original type on insert/update.

[bscott]

the timestamptz was a bug in the provider, the next release will support that type whitout any problems.

Looking at this in a more global perspective, it won't be possible to support all extended posgresql extended types. To support a postgres extended type we have to match it with a FDO type. The big problem actually is that the DescribeSchema function fail when there's a table with an unsupported type, making that complete schema unavailable. I would suggest to skip all tables with unsupported type and return only the tables/views with supported type. This way, these schemas will at least be available with the supported tables.

[Mike Toews]

I wasn't suggesting that FDO needs to support advanced types, since these often get complicated with PostgreSQL (custom types are easy to build). Rather, I'm suggesting better support/fallback when they are present.

The suggestion I'm trying to illustrate on PostgreSQL<->FDO communication is:

• PG->FDO: unsupported PG types are cast to text for FDO (e.g., myarray::text)
• FDO->PG: the text from FDO is cast back to the unsupported PG type (e.g., "{1,2,3}"::int[])

This is nicely and seamlessly implemented in the psqlODBC driver, and I have absolutely no problems seeing and editing all my tables with "advanced types" in MS Access. I think all PG types (including custom and extension types) support bidirectional customtype<->text casts, so you could offload all type conversions to PG without much effort from FDO. All that needs to be done by FDO is to manage the casting to/from text for unsupported types on insert/select/update operations.

[bscott]

Each of extended type should be processed one by one. I will create a ticket for the timestampz bug How should we map the _int4(int4[]) to?