With TEIID-2558 JDBC translators can create temporary tables for dependent join pushdown, but I'm hesitant to tie that to this feature since that usage is more focused and there is no need for higher level connection management - unless a real source table is created, the same connection must be reused to maintain access to the temporary table.

Updated the reference guide for the permissions and syntax to create a foreign temporary table. The initial implementation does not have any pushdown side-effects (there is no source action on create/drop).

Any ease of use features as an internal temporary table replacement can be addressed later.

I think it's best to not address the ease of use for a teiid temporary table for the 8.3 release. The most likely usage pattern to replace an internal temporary table would look like:

//- create the source table
source.native("CREATE [GLOBAL] TEMPORARY TABLE name IF NOT EXISTS ON COMMIT [DELETE ROWS | DROP]");
//- bring the table into Teiid
CREATE FOREIGN TEMPORARY TABLE name ...
//- use the table

For this to be correctly handled with a GLOBAL temporary table, the table name would need to be unique or the GLOBAL / table definition would need to be the same for all sessions with session specific data scoping (such as Oracle) - or the source would need to support session scoped temporary tables (such as PostgreSQL). It would also need to be accessed under a transaction so that:

• the on commit behavior could ensure clean-up - as a Teiid drop is not guaranteed to occur (in some exception cases, loss of db connectivity, hard shutdown, etc.)
• the source pool when using track connections by transaction will ensure that multiple uses of that source by Teiid will use the same connection/session and thus the same temporary table/data.

Satisfied the first item above by requiring a CREATE permission on the targeted schema. This may require a designer change if they don't allow the user to select schema level permissions.

Added the initial implementation, but did not include the AS clause support - since it is just syntactic sugar for a separate native call. The body of the table definition is exactly the same as what is supported via our DDL metadata. The ON clause references a model/schema name, but is only the logically source for the entry - we do not consider the table as belonging to that schema and it will have the same scoping semantics as a normal temporary table.

Additional work:

• security considerations - we have a allow temp table permission, but that may not be specific enough. Unless we disallow the use of the native-query construct (which is an option), then creating a temporary foreign provides a path for relatively arbitrary sql. Also there's no restriction on what source table may be referenced by the create which also implies the need for more restrictive/specific permissions.

• ease of use as an internal temporary table replacement - the initial check-in assumes that the user takes all responsibility for any native statements required to create/drop the source table (which can either be issued directly against the source or via a Teiid native procedure exec). The sql also relies on DDL, which means it defaults to non-updatable and to be fully useful against a particular source may need to be littered with native-type extension metadata, etc. These issues do not make this an easy replacement for internal temporary tables. However on the flip side of this there are lots of issues with assuming full responsibility with the creation of source temporary tables this includes:
  • the need for full bi-directional type mapping (our logic is currently mostly unidirectional)
  • sql generation (which in many cases could be lacking given source specific features that aren't easily captured by Teiid)
  • source scoping/cleanup - since we will generally be using multiple connections for a given user session, the temp scoping would need to be global and server shutdown or other abnormal behavior would imply that we'll need to clean-up those resources later.

The simplest (lowest effort) design would be an extension of our DDL/temp table syntax:

CREATE FOREIGN TEMPORARY TABLE name (...) ON source [AS 'native sql']

The options clauses would specify the name in source etc as appropriate and the native sql would be executed via the native query procedure facility if applicable. This side-steps any need in our language to add support for additional source constructs, such as alternative index types, additional constraints etc. So this would effectively be a hybrid of our internal temp logic and standard pushdown logic.

We would also likely have to allow a drop to be specified with native sql. We might want to still perform some metadata import from the source as to not make the full metadata specification too onerous. From the perspective of Teiid this will be treated as a temporary table in so much as it will be session scoped, but there would not need to be a restriction on the source side as to the table type.

Pushing to Teiid 9

This is quite broad in scope, since it requires new connector capabilities, new engine logic, new hints or metadata, etc. I doubt that we will get to it in 6.0.

Per 4/1 review meeting: beyond scope of 5.5.3