[teiid-issues] [JBoss JIRA] (TEIID-196) Support creation of temp tables on physical sources.

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-196?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12752634#comment-12752634 ] 

Steven Hawkins commented on TEIID-196:
--------------------------------------

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.

                
> Support creation of temp tables on physical sources.
> ----------------------------------------------------
>
>                 Key: TEIID-196
>                 URL: https://issues.jboss.org/browse/TEIID-196
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Connector API, Query Engine
>    Affects Versions: 6.0.0
>            Reporter: Ken Johnson
>            Assignee: Steven Hawkins
>             Fix For: 8.3
>
>
> This is a multi-part request.  
> First, the system should support creation of temporary tables using a physical backing store rather than buffer manger.  Given multi-pass SQL's heavy use of temp tables, buffer manager can easily be overloaded with large interim results stored in temp tables.
> Second, this should be a user-configurable behavior.  For example, user might be able to choose a system-level or session-level default from among:
>  -- memory/cache
>  -- a source represented by a connector binding
>  -- a distinct temp source defined with it's own connection parameters (possibly another schema in the repository DB instance)
> Ideally default selectoin should be override-able at temp table creation time through a DDL extension
> In the case where multiple temp tables have been created on a source via connector, the query planner should recognize this and leverage pushdown to the temp store when later query passes access multiple temp tables. 

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira