Ticket #394 (closed defect: fixed)

Opened 1 year ago

Last modified 10 months ago

PostGIS Defect : Describe schema fails when advanced types present

Reported by: mwtoews Assigned to:
Priority: critical Milestone:
Component: PostGIS Provider Version: 3.2.3
Severity: 2 Keywords:
Cc: External ID:

Description

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.

Change History

09/16/2008 02:56:13 PM changed by mwtoews

  • priority changed from major to critical.

04/28/2009 05:08:56 AM changed by 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.

04/29/2009 03:37:37 AM changed by mwtoews

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.

04/29/2009 05:04:23 AM changed by 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?

04/29/2009 05:41:41 AM changed by bscott

  • status changed from new to closed.
  • resolution set to fixed.

04/29/2009 06:19:10 AM changed by mloskot

  • status changed from closed to reopened.
  • resolution deleted.

04/29/2009 06:19:14 AM changed by mloskot

  • owner deleted.
  • status changed from reopened to new.

04/29/2009 06:19:17 AM changed by mloskot

  • status changed from new to closed.
  • resolution set to fixed.