13 Replies Latest reply on Jul 12, 2019 4:09 PM by shawkins

    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.

        • 1. Re: UUID type in Postgresql
          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.

          • 2. Re: UUID type in Postgresql
            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.

            • 3. Re: UUID type in Postgresql
              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?

              • 4. Re: UUID type in Postgresql
                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.

                • 5. Re: UUID type in Postgresql
                  lukyer

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

                  • 6. Re: UUID type in Postgresql
                    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); " />

                    • 7. Re: UUID type in Postgresql
                      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.

                      • 8. Re: UUID type in Postgresql
                        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.

                        • 9. Re: UUID type in Postgresql
                          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

                          • 10. Re: UUID type in Postgresql
                            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?

                            • 11. Re: UUID type in Postgresql
                              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.

                              • 12. Re: UUID type in Postgresql
                                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?

                                • 13. Re: UUID type in Postgresql
                                  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.