amending missing teiid ODBC PG metadata from actual PG DB

[mnozin]

Hi,

We are trying to connect native PG client to teiid. While Teiid has some system metadata objects, there are few rwuired by our native client are missing. One approach was to change code for teiid metadata to return missing objects, for instance to satisfy this query:

Select count(rolname) From pg_catalog.pg_roles

where rolsuper = true and rolname = lower('postgres')

but is is possible to create a source model

<model name="pg_catalog1">

              <source name="pg_SQL" connection-jndi-name="java:comp/env/jdbc/Postgre_SQL" translator-name="postgresql"/>

</model>

and get teiid to use its metadata first?

Right now, the query:

select OID from pg_type where typname = 'st_geometry'

results in exception:

WARNING: TEIID30020 Processing exception for request 3rSbQ3UFC9FN.0 'Group specified is ambiguous, resubmit the query by fully qualifying the group name: pg_type'. Originally QueryResolverException ResolverUtil.java:839. Enable more detailed logging to see the entire stacktrace.

Thanks,

Max

[shawkins]

> and get teiid to use its metadata first?

The current logic is based upon us being a full virtualization layer, so the metadata we present through our pg system tables matches our expectations and is specifically designed for consumption by known odbc clients.

If there is a need, you can certainly have us add more postgresql system tables, but to have them implemented with some interaction with an actual postgresl source is not something that we'd generally want.

[mnozin]

makes sense from strategic point of view but it will take a while when Teiid adds full support for native PG clients. Right now, I can't even browse VDB in PG 9.3 admin app due to missing objects in metadata mostly. While our product supports OLE DB for ODBC, the next release of doesn't have OLE DB it which leaves us with only native option. BTW, are there any plans to have increased level of PG compliance in Teiid in the future?

Thanks,

Max

[shawkins]

> but it will take a while when Teiid adds full support for native PG clients

As there is virtually no limit to what clients could be doing, we have to add compatibility in a targeted manner.

> are there any plans to have increased level of PG compliance in Teiid in the future

Yes, we are always increasing both the syntax and metadata support.  So if there are additional needs that you have, please open a JIRA with the client and/or source queries that it is issuing and we can see about adding more compatibility.