Teiid - Changing behavior of User Defined Functions after created

Question

I'm with difficult to work with User-Defined Functions in Teiid. The issue is about changing the behavior of the functions and reply to Teiid.

It did this function in PostgreSQL.

create or replace function test() returns varchar language plpgsql as $$
begin
  return 'test version 1';
end;
$$;

After that, It created this VDB from Java code.

FunctionMethod function = target.addFunction("test");
function.setOutputParameter(new FunctionParameter("p_value", "varchar"));
function.setPushdown(FunctionMethod.PushDown.MUST_PUSHDOWN);
function.setDeterminism(FunctionMethod.Determinism.NONDETERMINISTIC);

VDB

<?xml version="1.0" encoding="UTF-8"?>
<vdb name="test-vdb" version="105">
   <description>VDB for tenant test</description>
   <connection-type>BY_VERSION</connection-type>
      <model name="vdb-test" type="PHYSICAL" visible="true">
      <property name="cache-metadata" value="true" />
      <property name="include-pg-metadata" value="false" />
      <source name="vdb-test-source" translator-name="postgresql" connection-jndi-name="XXXXXXXXXXXXXXXXXXX" />
      <metadata type="ddl"><![CDATA[CREATE FOREIGN FUNCTION test() RETURNS varchar OPTIONS (DETERMINISM 'NONDETERMINISTIC');]]></metadata>
   </model>
</vdb>

It works perfectly when executing a query in Teiid.

After the tests, It was changing the behavior of the function like above.

create or replace function test() returns varchar language plpgsql as $$
begin
  return 'test version 2';
end;
$$;

The only change was the output text but when I'm executing the query I always got the return of the first version of the function.

I was trying to set the deterministic to the User-Defined Function like "NONDETERMINISTIC" but the behavior still the same (I updated and restart the VDB).

There are some questions about this issue:

1. How do I set up the Teiid always to get the new behavior of the function from the data-source?
2. There is some property to set this config?
3. Does Teiid persist the function behavior internally?

Teiid version: 9.1

Thanks for your helping :)

Answer 1

Generally there is nothing that you need to do. Unless you are creating a materialization, using result set caching, etc., you will get the latest result from the database.

You should ensure that function change has taken place on the database in the way you expect - for example try directly executing the Teiid source query with the same user account as Teiid.

If you obtain a query plan / execution log from Teiid, you can confirm that it's not doing anything out of the ordinary.