You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by "Williamson, Nick" <ni...@mentor.com> on 2007/08/14 12:40:29 UTC
Function-based indexes
Hi all,
In Oracle, it's possible to have an index column that is the result of a
function, for example:
CREATE INDEX index_name ON table_name
(
UPPER("column_name")
);
It doesn't seem possible to do this in Derby. What would be the best way
to mimic this behaviour? I guess I would need a new column to hold the
uppercased value of the original column, but can I get Derby to maintain
the new column for me, perhaps with a trigger? Or is it possible to have
a column that is itself the result of a function on another column?
TIA
Nick
RE: IJ out of memory
Posted by "Williamson, Nick" <ni...@mentor.com>.
JIRA DERBY-3009 created.
Regards,
Nick
-----Original Message-----
From: Williamson, Nick
Sent: 15 August 2007 09:53
To: Derby Discussion
Subject: RE: IJ out of memory
Thanks Kristian,
I'll visit the site and create an issue, attaching the script. Thanks
for doing the investigation.
Regards,
Nick
-----Original Message-----
From: Kristian.Waagan@Sun.COM [mailto:Kristian.Waagan@Sun.COM]
Sent: 15 August 2007 09:45
To: Derby Discussion
Subject: Re: IJ out of memory
<snip>
Filing a Jira issue would be good
(https://issues.apache.org/jira/browse/DERBY).
I will do so if the original reporter doesn't. If possible, please
attach the script to be used as a repro for the bug.
RE: IJ out of memory
Posted by "Williamson, Nick" <ni...@mentor.com>.
Thanks Kristian,
I'll visit the site and create an issue, attaching the script. Thanks
for doing the investigation.
Regards,
Nick
-----Original Message-----
From: Kristian.Waagan@Sun.COM [mailto:Kristian.Waagan@Sun.COM]
Sent: 15 August 2007 09:45
To: Derby Discussion
Subject: Re: IJ out of memory
<snip>
Filing a Jira issue would be good
(https://issues.apache.org/jira/browse/DERBY).
I will do so if the original reporter doesn't. If possible, please
attach the script to be used as a repro for the bug.
Re: IJ out of memory
Posted by Kristian Waagan <Kr...@Sun.COM>.
Williamson, Nick wrote:
> Thanks Bryan,
>
> Unfortunately, I'm working with an existing Oracle DDL script which has
> separate FK definitions so I can't easily include them in the table
> definition. I'm just running the Oracle DDL script through a
> reformatting routine that I wrote which removes tokens and statements
> that Derby doesn't like, and running the reformatted version in IJ.
>
> Anyway, I've got a solution, albeit not an ideal one. Having done a bit
> more testing, I have found that:
>
> 1) The main script runs fine if don't create the LOGICPROPERTY table
> 2) Creating the LOGICPROPERTY table in another script, in another
> session, still raises the OOME
> 3) Creating the LOGICPROPERTY table in several small scripts, in another
> session, still raises the OOME
> 4) Creating the LOGICPROPERTY table in several small scripts, running
> each one in a separate IJ session, is successful.
>
> It's like the definition of LOGICPROPERTY + its 50-odd indexes + its
> 50-odd constraints is just too much for a single IJ session to handle,
> even if I feed it in several chunks. The only solution seems to be to
> create it one step at a time, in separate IJ sessions.
>
> I'll happily share the script if that would help; at around 650k, I
> think it's too big for the list. If anyone would like to see it, please
> e-mail me off-list.
Hi all,
I asked Nick for the script generating the schema.
With default settings on my machine, I also get the OOME.
A brief investigation revealed a few things:
1) The OOME occurs during constraint additions (with ALTER TABLE ...
ADD CONSTRAINT). I could observe this by monitoring the heap usage.
2) The complete script can be run by increasing the heap size. I tried
with 256 MB, but the monitoring showed usage peaked at around 150 MB.
3) The stack traces produced when the OOME occurs varies (as could be
expected).
4) It is the Derby engine that "produce" the OOME, not ij (i.e. when I
ran with the network server, the server failed).
I have not had time to examine the heap content, but I do believe there
is a bug in Derby. It seems some resource is not freed after use.
Filing a Jira issue would be good
(https://issues.apache.org/jira/browse/DERBY).
I will do so if the original reporter doesn't. If possible, please
attach the script to be used as a repro for the bug.
regards,
--
Kristian
>
> Regards,
> Nick
>
>
>
>
> -----Original Message-----
> From: Bryan Pendleton [mailto:bpendleton@amberpoint.com]
> Sent: 14 August 2007 15:41
> To: Derby Discussion
> Subject: Re: IJ out of memory
>
>> Failed Statement is: ALTER TABLE logicproperty
>> ADD CONSTRAINT r_186
>> FOREIGN KEY
>> ( zone_id )
>> REFERENCES zone
>> ON DELETE CASCADE
>
> You can include the foreign key constraints in the initial table
> definition itself, instead of creating the table and then adding the
> constraints to it later.
>
> Do you still get the OutOfMemory problem if you include all the foreign
> key constraints in the table definition itself, rather than as separate
> ALTER TABLE statements?
>
> Also, can you share the entire create_db.sql script?
>
> thanks,
>
> bryan
>
>
RE: IJ out of memory
Posted by "Williamson, Nick" <ni...@mentor.com>.
Thanks Bryan,
Unfortunately, I'm working with an existing Oracle DDL script which has
separate FK definitions so I can't easily include them in the table
definition. I'm just running the Oracle DDL script through a
reformatting routine that I wrote which removes tokens and statements
that Derby doesn't like, and running the reformatted version in IJ.
Anyway, I've got a solution, albeit not an ideal one. Having done a bit
more testing, I have found that:
1) The main script runs fine if don't create the LOGICPROPERTY table
2) Creating the LOGICPROPERTY table in another script, in another
session, still raises the OOME
3) Creating the LOGICPROPERTY table in several small scripts, in another
session, still raises the OOME
4) Creating the LOGICPROPERTY table in several small scripts, running
each one in a separate IJ session, is successful.
It's like the definition of LOGICPROPERTY + its 50-odd indexes + its
50-odd constraints is just too much for a single IJ session to handle,
even if I feed it in several chunks. The only solution seems to be to
create it one step at a time, in separate IJ sessions.
I'll happily share the script if that would help; at around 650k, I
think it's too big for the list. If anyone would like to see it, please
e-mail me off-list.
Regards,
Nick
-----Original Message-----
From: Bryan Pendleton [mailto:bpendleton@amberpoint.com]
Sent: 14 August 2007 15:41
To: Derby Discussion
Subject: Re: IJ out of memory
> Failed Statement is: ALTER TABLE logicproperty
> ADD CONSTRAINT r_186
> FOREIGN KEY
> ( zone_id )
> REFERENCES zone
> ON DELETE CASCADE
You can include the foreign key constraints in the initial table
definition itself, instead of creating the table and then adding the
constraints to it later.
Do you still get the OutOfMemory problem if you include all the foreign
key constraints in the table definition itself, rather than as separate
ALTER TABLE statements?
Also, can you share the entire create_db.sql script?
thanks,
bryan
Re: IJ out of memory
Posted by Bryan Pendleton <bp...@amberpoint.com>.
> Failed Statement is: ALTER TABLE logicproperty
> ADD CONSTRAINT r_186
> FOREIGN KEY
> ( zone_id )
> REFERENCES zone
> ON DELETE CASCADE
You can include the foreign key constraints in the
initial table definition itself, instead of creating the
table and then adding the constraints to it later.
Do you still get the OutOfMemory problem if you include
all the foreign key constraints in the table definition itself,
rather than as separate ALTER TABLE statements?
Also, can you share the entire create_db.sql script?
thanks,
bryan
RE: IJ out of memory
Posted by "Williamson, Nick" <ni...@mentor.com>.
Thanks Kristian,
...and apologies for the lack of information. I knew as soon as I'd
posted that I should have said more about the environment...
My config is default AFAIK, with the exception that I have the error
logging set to 0. That said, I still get the OOME if I set error logging
back to the default 40k. I'm on XP Pro, running in a DOS shell. I invoke
IJ (10.2), then I "run create_db.sql" and it's towards the end of the
process that I get the OOME, after having created the tables, at the
point where I'm trying to create a load of FK constraints.
I don't get a great deal of info in the derby.log file. Here are the
last few entries (I think the stack trace is actually related to the
problem with the PIN_FK8 constraint rather than the OOME) :
---------------------------------------------------
ERROR 42915: Foreign Key 'PIN_FK8' is invalid because 'the delete rule
of foreign key must be CASCADE. (The relationship would cause the table
to be delete-connected to the same table through multiple relationships
and such relationships must have the same delete rule (NO ACTION,
RESTRICT or CASCADE).) '.
at
org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at
org.apache.derby.iapi.sql.dictionary.DDUtils.generateError(Unknown
Source)
at
org.apache.derby.iapi.sql.dictionary.DDUtils.checkForMultiplePathInvalid
Cases(Unknown Source)
at
org.apache.derby.iapi.sql.dictionary.DDUtils.validateDeleteConnection(Un
known Source)
at
org.apache.derby.iapi.sql.dictionary.DDUtils.validateDeleteConnection(Un
known Source)
at
org.apache.derby.iapi.sql.dictionary.DDUtils.validateDeleteConnection(Un
known Source)
at
org.apache.derby.iapi.sql.dictionary.DDUtils.validateReferentialActions(
Unknown Source)
at
org.apache.derby.impl.sql.execute.CreateConstraintConstantAction.execute
ConstantAction(Unknown Source)
at
org.apache.derby.impl.sql.execute.AlterTableConstantAction.execGuts(Unkn
own Source)
at
org.apache.derby.impl.sql.execute.AlterTableConstantAction.executeConsta
ntAction(Unknown Source)
at org.apache.derby.impl.sql.execute.MiscResultSet.open(Unknown
Source)
at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown
Source)
at
org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown
Source)
at org.apache.derby.impl.tools.ij.ij.executeImmediate(Unknown
Source)
at org.apache.derby.impl.tools.ij.utilMain.doCatch(Unknown
Source)
at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(Unknown
Source)
at org.apache.derby.impl.tools.ij.utilMain.go(Unknown Source)
at org.apache.derby.impl.tools.ij.Main.go(Unknown Source)
at org.apache.derby.impl.tools.ij.Main.mainCore(Unknown Source)
at org.apache.derby.impl.tools.ij.Main14.main(Unknown Source)
at org.apache.derby.tools.ij.main(Unknown Source)
Cleanup action completed
2007-08-14 14:00:50.978 GMT Thread[main,5,main] (XID = 9977), (SESSIONID
= 0), (DATABASE = chs), (DRDAID = null), Cleanup action starting
2007-08-14 14:00:50.978 GMT Thread[main,5,main] (XID = 9977), (SESSIONID
= 0), (DATABASE = chs), (DRDAID = null),
Failed Statement is: ALTER TABLE logicproperty
ADD CONSTRAINT r_186
FOREIGN KEY
( zone_id )
REFERENCES zone
ON DELETE CASCADE
java.lang.OutOfMemoryError
Cleanup action completed
2007-08-14 14:00:57.087 GMT:
Shutting down instance c013800d-0114-64a7-80cd-000000100a28
----------------------------------------------------------------
BTW, it may be pertinent that the table it is working on at the time of
the OOME (that is, LOGICPROPERTY) is a poorly-constructed thing with
nearly 50 FK constraints. Unfortunately, we're stuck with it. I'll try
creating the schema without those constraints to see if I still get the
error...
HTH
Nick
Re: IJ out of memory
Posted by Kristian Waagan <Kr...@Sun.COM>.
Williamson, Nick wrote:
> Hi all,
>
> I'm experiencing java.lang.OutOfMemoryError when trying to create a
> large schema (500+ tables, lots of constraints). Is there anything I can
> do about it (perhaps by allocating more memory to IJ) or is it a bug?
Hello Nick,
You say nothing about configuration, so I'm assuming it is as per default.
Is it possible for you to run with Java SE 6 and post the stack trace
when the OOME happens?
Do you already have some stack traces?
Are you using the embedded or the client driver?
(btw, please also check derby.log for stack traces / error messages)
During which statement does it happen?
I can't say that it is not a bug, and your use case sounds a bit special
and is probably not that well tested.
If you provide some more information, we'll try to help you :)
regards,
--
Kristian
>
> TIA
> Nick
IJ out of memory
Posted by "Williamson, Nick" <ni...@mentor.com>.
Hi all,
I'm experiencing java.lang.OutOfMemoryError when trying to create a
large schema (500+ tables, lots of constraints). Is there anything I can
do about it (perhaps by allocating more memory to IJ) or is it a bug?
TIA
Nick
Re: Function-based indexes
Posted by Rick Hillegas <Ri...@Sun.COM>.
Williamson, Nick wrote:
> Hi all,
>
> In Oracle, it's possible to have an index column that is the result of a
> function, for example:
>
> CREATE INDEX index_name ON table_name
> (
> UPPER("column_name")
> );
>
> It doesn't seem possible to do this in Derby. What would be the best way
> to mimic this behaviour? I guess I would need a new column to hold the
> uppercased value of the original column, but can I get Derby to maintain
> the new column for me, perhaps with a trigger? Or is it possible to have
> a column that is itself the result of a function on another column?
>
> TIA
> Nick
>
Hi Nick,
There are two related JIRAs for this missing functionality: DERBY-455
(function indexes) and DERBY-481 (computed columns). In my opinion,
DERBY-481 is the easier of the two to implement. It has become even more
attractive now that the new 10.3 release lets you drop columns--this
means that you can drop and recreate computed columns as you debug your
application.
As you say, you can use Triggers to hand-roll your own computed columns.
This won't perform as well as a good solution to DERBY-481 (but I don't
have any performance figures to support that opinion). It's also a
little brittle: your computed column's value will depend on the contents
of other input columns in the row and you will have to make sure that
your Trigger is sensitive to changes in all of those input columns.
Another solution would be to encapsulate your writes in methods which
pre-calculate the computed result before stuffing the columns. This is a
little scary because it's hard to prove to yourself that unauthorized
writes aren't leaking into your database from dark corners of your
application. You could regain that confidence by adding a table-level
Check constraint--that Check constraint would verify that your computed
column has the correct value. Again, this won't perform as well as a
good solution to DERBY-481. The Check constraint will be fired every
time the row is altered rather than just when the input columns change.
Hope this helps,
-Rick