UUID type in Postgresql

[lukyer]

Hi,

i'm trying to find out how to teach Postgresql datasource translator to work correctly with UUID datatype. Teiid sees it as Object instead of, for example, String datatype. It causes one can't do any ordering/where in sql queries.
One way is to make view with manual CASTing UUID datatype. It is not suitable for dynamic VDB generation.

<metadata type="DDL">

<![CDATA[

CREATE VIEW "_view_workflow_data" OPTIONS (UPDATABLE 'true') AS

SELECT

  CAST("lukyer_table"."id" as string) AS "id",

"id" AS "id_original"

FROM "lukyer_table"

]]>

</metadata>

What is recommended way how to achieve automatic UUID casting to String? Should i overwrite JDBC translator and implement it somehow there? Is it even possible?

Thanks.

[lukyer]

Another options is to define CREATE FOREIGN TABLE and redefine column type to string. However you will get possible errors only in runtime, not in deploy time. So view is probably better option anyway.

[rareddy]

Yes, the view is a definitely better option, but if you think that UUID should be handled as string always, then modification in the Teiid Postgres translator is needed. I am not sure if that is the right approach or not. I think I even modeled UUID from other databases strings before.

[lukyer]

What would be better approach than casting uuid to string? Searching in tables with uuid as primary key is the very simple use case, i think. How can i achieve simple table filtering based on uuid value without casting or translator modification? Am i missing something? Could you please tell me more about that you modelled uuid from other databases strings before?

[rareddy]

what I was saying is in MongoDB like data sources, I automatically treated UUID as string at the time import from a source in dynamic VDB. So, there is automatic casting. Currently I can't think of any other way other than casting or code modification.

[lukyer]

MongoDB maybe, but Postgresql doesn't have automatic casting. Or i'm doing something wrong.

[lukyer]

Another option i can think of is to define custom domain-dll and rewrite existing datetype and use it in FOREIGN TABLE/CREATE VIEW definition. It seems working as well.
What do you think about this option?

<property name="domain-ddl" value="CREATE DOMAIN uuid AS VARCHAR(100); " />

[rareddy]

That will work, but you are using this at the view level. If you want to avoid type casting this is another way, but here you would need to change the data type to uuid on view's column then Teiid system is providing implicit castings. My earlier comments were for the changing the type at import time.

[lukyer]

Could you expand on import time? When exactly import time happens? It would be probably better to do casting just once or at low level instead of view level.  I'm not using TEIID designer, only AdminAPI, so i'm deploying -vdb.xml using teiid api.

[rareddy]

Like when you deploy the VDB, the source model metadata is fetched. The Teiid translator is making decisions how the native types are matched with the Teiid types. When you define DDL like "CREATE FOREIGN TABLE" explicitly in the source model, the system may be skipping interrogation of metadata and uses what you defined directly

[lukyer]

Okay, i understand that, but you wrote that type casting is better to do in custom VIEW than in CREATE FOREIGN TABLE. Is it still valid? What is the difference between these two approaches?

[rareddy]

No difference in approach, it is recommended that you keep the source model as close to native representation so that you know all the data type or any other changes you made are in single place.

[nicholas.d]

Hello, do i understand correctly that there is still no uuid support out of the box for postgres with automatic schema generation?

If i'm going to cast(some_id as string) where i need to use uuid - will i get performance hit or teiid can handle it gracefully?

[shawkins]

> Hello, do i understand correctly that there is still no uuid support out of the box for postgres with automatic schema generation?

There is no uuid type in Teiid, but the import will map pg uuid to the Teiid string type.

> If i'm going to cast(some_id as string) where i need to use uuid - will i get performance hit or teiid can handle it gracefully?

Teiid won't need to perform any explicit casting.  The conversion from string literals or bind values to uuid will happen in the pg layer.