Re: [jira] Commented: (DERBY-347) Document SYNONYM support

Wed, 15 Jun 2005 23:32:50 -0700

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 <[EMAIL PROTECTED]> 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
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 <[EMAIL PROTECTED] > 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.


Reply via email to