db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: [jira] Commented: (DERBY-347) Document SYNONYM support
Date Thu, 16 Jun 2005 06:05:50 GMT
Hi Satheesh,
 As you summarized in your comment to "Derby-362 Some of the DDL checks to 
prevent incorrect temporary table use is incorrect. 
These may incorrectly stop access to valid permanant tables in SESSION 
schema.", the reason for not supporting SESSION 
schema objects in views and triggers had to with statement caching and 
statement plan invalidation.
 Let's take an eg where say views do support SESSION schema objects
create view v1 as select * from SESSION.t1; //say SESSION.t1 here is a 
permanent table.
Now declare a temporary table with name t1
The view at this point should be invalidated because as per the temporary 
table definition, we always look for 
temporary tables first to do the table resolution, and if no temporary table 
found, then look for permanent table 
in SESSION schema. But switching to temporary table t1 is probably not what 
the view developer intended.
 This is also the reason why statements referring to SESSION schema objects 
don't get cached. Following eg
will demonstrate that.
Connection 1 say has persistent table T and hence plan for query select * 
from SESSION.T should refer to 
persistent table T.
Connection 2 say declares a global temp table T with column a int, then 
select * from SESSION.T should refer to
that temporary table T.
Connection 3 say declares a global temp table T with column b char(2), 
column c short, and select * from SESSION.T
for this connection should refer to its own definition of T
So, there is no way, these 3 connections could share the same statement plan 
for select * from SESSION.T 
by having the plan in common statement cache. And that is why Derby does not 
cache statements referencing 
SESSION schema objects.
 I hope this clarifies some issues,
Mamta

 On 6/15/05, Satheesh Bandaram <satheesh@sourcery.org> wrote: 
> 
> Do you remember the reasoning for this? Guess we should add synonyms to 
> the list, with triggers and views.
> 
> Satheesh 
> 
> Mamta Satoor wrote:
> 
> Hi Satheesh,
>  The behavior you are noticing with views and triggers is the documented 
> behavior. 
> "DECLARE GLOBAL TEMPORARY TABLE statement is documented at 
> http://incubator.apache.org/derby/docs/ref/rrefdeclaretemptable.html. At 
> the 
> bottom of the page, there is a section called "Restrictions Specific to 
> Derby"
> and it says following for views and triggers.
> 
> Derby does not support the following on temporary tables:
> 
>    - index support 
>    - *triggers and views on SESSION schema tables (including physical 
>    tables and temporary tables) *
> 
> If we choose to not support SYNONYMS on temporary table, that we should 
> add it 
> the list. If we choose not to support SYNONYMS on any SESSION schema 
> table,
> then we should add it to the list alongwith triggers and views 
> restriction.
>  Also, the reason the index creation passed is that it was created on a 
> permanent
> table in SESSION schema. If it was attempted on a temporary table, you 
> would
> have gotten an exception (which is inline with the documentation, where we 
> say
> that there is no index support on temporary tables).
>  thanks,
> Mamta
> 
>  On 6/14/05, Satheesh Bandaram <satheesh@sourcery.org> wrote: 
> > 
> > Good point... I think many DDL statements need fixing in that case:
> > 
> > ij> create table session.permTable ( i int);
> > 0 rows inserted/updated/deleted
> > ij> *create view *view1 as select * from session.permTable;
> > ERROR XCL51: The requested function can not reference tables in SESSION 
> > schema.
> > ij> *create trigger *trig1 after insert on session.permTable for each 
> > row mode db2
> > sql insert into t1 values (1);
> > ERROR XCL51: The requested function can not reference tables in SESSION 
> > schema.
> > ij> *create synonym* syn1 for session.permTable;
> > ERROR XCL51: The requested function can not reference tables in SESSION 
> > schema.
> > ij> *create index *indSession on session.permTable (i); <============ 
> > Works correctly.
> > 0 rows inserted/updated/deleted 
> > 
> > I seem to have changed from the correct way to check in original 
> > submitted patch to incorrect way in the last "cleanup"... :-( 
> > 
> > Satheesh 
> > 
> > Daniel John Debrunner wrote:
> > 
> > Satheesh Bandaram (JIRA) wrote:
> > 
> >   
> > 
> >     [ http://issues.apache.org/jira/browse/DERBY-347?page=comments#action_12313495
] 
> > 
> > Satheesh Bandaram commented on DERBY-347:
> > -----------------------------------------
> > 
> > The patch looks good, though I would add more description to the CREATE SYNONYM
section. Can we also add the following information?
> > 
> >   *  A synonym can be defined for a table/view that doesn't exists at the synonym
creation time. If it doesn't exists, a warning (SQLSTATE 01522) is raised. The referenced
object must be present when a synonym is used in a DML statement.
> >     
> > 
> > [snip]
> >   
> > 
> >   *  A synonym can't be defined on a temporary table. (SQLSTATE XCL51)
> >     
> > 
> > Is the temp table restriction a DDL time (CREATE SYNONYM) failure or a
> > runtime (when the SYNONYM is used in a statement)?
> > 
> > Because error XCL51 doesn't exactly match what you describe. XCL51
> > indicates the operation cannot reference a table in the SESSION schema,
> > which is where temp tables are defined but that schema can also support
> > normal tables.
> > 
> > Dan.
> > 
> > 
> > 
> > 
> > 
> >   
> > 
> > 
>

Mime
View raw message