6 Replies Latest reply on Sep 4, 2017 4:14 AM by lukyer

    Updatable view with joins

    lukyer

      Hi,

      i'm trying to create View with very simple join over two tables (pk/fk correctly set) over postgres datasource. However i'm unable to make UPDATE/INSERT queries working without defining custom triggers.
      Could you please point me to some resource explaining how to achieve implicit updatable mode with some simple example? Maybe i'm overlooking something important.

       

      I always end up with "A procedure may be needed to handle the Update since: The updatable view has no valid target for UPDATEs."

      Views without inner joins work correctly.

       

      I'm running TEIID 9.3.1.

       

      Thank you.

       

      Regards,

      Lukas.

        • 1. Re: Updatable view with joins
          shawkins

          > Could you please point me to some resource explaining how to achieve implicit updatable mode with some simple example?

           

          See Updatable Views · Teiid Documentation

           

          > Maybe i'm overlooking something important.

           

          We currently require that the insert/update modifies only a single target and that the target is key-preserved. 

           

          If you think that your scenario should be updatable or there is a bug, can you provide more of the details here or in an issue?

          • 2. Re: Updatable view with joins
            lukyer

            I read whole documentation. However i didn't find any example with updatable view with joins.

            Could you please explain "single target and that the target is key-preserved. " with example in normal SQL92?

            • 3. Re: Updatable view with joins
              rareddy

              what he is saying is if the view represents a single table that has PK/Unique key on it. If a transformation is more complex the intention of the update can not fully be guessed as there may be duplicate columns across the join tables etc. It is best if you define explicit update procedures.

              • 4. Re: Updatable view with joins
                lukyer

                I understand the single table scenario. However i'm interested in view with joins scenarios. According to the docs it should be possible, but there is no simple example:

                 

                If a view is defined by a join query or has a WITH clause it may still be inherently updatable. However in these situations there are further restrictions and the resulting query plan may execute multiple statements. For a non-simple query to be updatable, it is required:

                • 5. Re: Updatable view with joins
                  shawkins

                  Using an example like:

                   

                  create foreign table parent_table (pk_col integer primary key, name string) options (updatable true);

                  create foreign table child_table (pk_col integer primary key, name string, fk_col integer, foreign key (fk_col) references parent_table (pk_col)) options (updatable true);

                  create view denormalized options (updatable true) as select c.fk_col, c.name as child_name, p.name from parent_table as p, child_table as c where p.pk_col = c.fk_col;

                   

                  You could issue "insert into denormalized (fk_col, child_name) values (1, 'a')", which targets a single key-preserved table, but not "insert into denormalized (name) values ('a')" as it issue an insert that is not visible to the view.

                   

                  However the logic is limited.  You may be doing something like "insert into denormalized (pk_col, child_name) values (1, 'a')" with a view that is defined using the p.pk_col - unfortunately the logic doesn't yet consider the equivalency of the key values.  It also for example doesn't consider left out join scenarios as a way of preserving the parent_table key.  If you need a scenario like that to work, please log an issue.

                   

                  I can also update the docs with more information on this topic - could you log an issue for that?

                  • 6. Re: Updatable view with joins
                    lukyer

                    Thanks for your and Ramesh's explanation. Now i understand it. I will try to profit from described automatic behaviour and generate update triggers where needed. If it was problematic, i would log an issue with concrete scenario.