drop column functionality

[Tim Dudgeon <td...@informaticsmatters.com>]

This is a continuation of a discussion on the users mailing list which I 
was advised to transfer here. See:
http://thread.gmane.org/gmane.comp.apache.db.derby.user/4836/focus=4836

I've applied the patch for dropping columns and confirmed it works in a 
basic scenario. Here are some follow up questions/issues:

1. Dropping the column seems very slow for large tables. I suppose this 
is inevitable as lots of data needs to be deleted. But are improvements 
possible?

2. I'm reluctant to use the SVN trunk in my application, preferring a 
stable release. Are there any plans for when this patch will get into a 
stable release.

3. The version from trunk does not appear able to read databases created 
with the 10.1.3.1 release. Has the database file syntax changed?

Thanks
Tim

Re: drop column functionality

[Daniel John Debrunner <dj...@apache.org>]

Deepa Remesh wrote:

> On 8/21/06, Bryan Pendleton <bp...@amberpoint.com> wrote:
> 
>>
>> > 3. The version from trunk does not appear able to read databases
>> created
>> > with the 10.1.3.1 release. Has the database file syntax changed?
>>
>> I'm hoping one of the other developers can help with this question.
>> I think that this is on purpose, to avoid accidentally mixing releases
>> that may differ in major ways, but I think there is a configuration
>> flag that you can set that will authorize the trunk code to open and
>> read a previous-release database.
>>
>> Can anybody help us out here? How can Tim use some of his 10.1 databases
>> in experimenting with a patched release built from the trunk?
>>
> 
> Setting "derby.database.allowPreReleaseUpgrade" system property to
> true will allow pre-release code (like trunk code) to read databases
> from previous release. Note that this property is only to be used for
> testing. Please take a look at these links for additional info:
> http://www.nabble.com/Re%3A-Soft-Upgrade-question-p3441560.html
> http://wiki.apache.org/db-derby/UpgradingTen

and be warned since the trunk has only just been bumped to reflect
version 10.3 the corresponding upgrade code has *not* been updated. This
may mean the upgrade code is non-functional at this point in time on the
trunk.

Dan.

Re: drop column functionality

[Deepa Remesh <dr...@gmail.com>]

On 8/21/06, Bryan Pendleton <bp...@amberpoint.com> wrote:

>
> > 3. The version from trunk does not appear able to read databases created
> > with the 10.1.3.1 release. Has the database file syntax changed?
>
> I'm hoping one of the other developers can help with this question.
> I think that this is on purpose, to avoid accidentally mixing releases
> that may differ in major ways, but I think there is a configuration
> flag that you can set that will authorize the trunk code to open and
> read a previous-release database.
>
> Can anybody help us out here? How can Tim use some of his 10.1 databases
> in experimenting with a patched release built from the trunk?
>

Setting "derby.database.allowPreReleaseUpgrade" system property to
true will allow pre-release code (like trunk code) to read databases
from previous release. Note that this property is only to be used for
testing. Please take a look at these links for additional info:
http://www.nabble.com/Re%3A-Soft-Upgrade-question-p3441560.html
http://wiki.apache.org/db-derby/UpgradingTen

Thanks,
Deepa

Re: drop column functionality

[Tim Dudgeon <td...@informaticsmatters.com>]

Mike Matrigali wrote:
> I don't think performance should stop you from checking in this
> functionality.  As you say, for a number of users it won't
> matter at all, the big thing will be whether the functionality
> is there or not.  Worry about performance later.

+1

> I am a little surprised as I thought the current system did not
> require this for drop.  Is it possible the test is measuring
> the time to both add and drop a column?

Yes, timings for add and drop columns is definitely separate.
Adding is fast, dropping is slow.

Tim

Re: drop column functionality

[Mike Matrigali <mi...@sbcglobal.net>]

I don't think performance should stop you from checking in this
functionality.  As you say, for a number of users it won't
matter at all, the big thing will be whether the functionality
is there or not.  Worry about performance later.

I know with a number of the alter column changes, the current
store support is going to require all the rows to be touched.

I am a little surprised as I thought the current system did not
require this for drop.  Is it possible the test is measuring
the time to both add and drop a column?

Tim Dudgeon wrote:
> 
> 
> Bryan Pendleton wrote:
> 
>>> 1. Dropping the column seems very slow for large tables. 
>>
>>
>> This is good to know. I don't think anybody has really experimented with
>> this yet. Can you quantify "very slow" with some real measurements
>> you've taken? Do you think it would be reasonable to treat this as
>> a follow-on improvement to the DERBY-1489 changes, or do you feel that
>> the performance is so bad that DERBY-1489 is unusable as is?
>>
>> I guess my assumption was that DROP COLUMN usage in large database
>> scenarios would be rare, and that it would be more common for people
>> to want to use DROP COLUMN during their initial application development,
>> when tables are typically smaller. But perhaps this is a bad assumption?
>>
>> If you think this would be reasonable to treat as a follow-on
>> improvement, then please open a new JIRA issue for it, describe
>> it as well as you can with the data you can provide, and link
>> the new issue to DERBY-1489 so that we know they're related.
>>
> 
> I added some performance comparisons with Oracle to the JIRA issue.
> http://issues.apache.org/jira/browse/DERBY-1489
> This shows that Derby appears to be approx 3-4x slower at dropping a 
> column in large tables.
> I don't think this is a show stopper, but obviously any improvements 
> would be of benefit.
> 
> Tim
> 
> 
> 

Re: drop column functionality

[Tim Dudgeon <td...@informaticsmatters.com>]

Bryan Pendleton wrote:
>> 1. Dropping the column seems very slow for large tables. 
> 
> This is good to know. I don't think anybody has really experimented with
> this yet. Can you quantify "very slow" with some real measurements
> you've taken? Do you think it would be reasonable to treat this as
> a follow-on improvement to the DERBY-1489 changes, or do you feel that
> the performance is so bad that DERBY-1489 is unusable as is?
> 
> I guess my assumption was that DROP COLUMN usage in large database
> scenarios would be rare, and that it would be more common for people
> to want to use DROP COLUMN during their initial application development,
> when tables are typically smaller. But perhaps this is a bad assumption?
> 
> If you think this would be reasonable to treat as a follow-on
> improvement, then please open a new JIRA issue for it, describe
> it as well as you can with the data you can provide, and link
> the new issue to DERBY-1489 so that we know they're related.
> 

I added some performance comparisons with Oracle to the JIRA issue.
http://issues.apache.org/jira/browse/DERBY-1489
This shows that Derby appears to be approx 3-4x slower at dropping a 
column in large tables.
I don't think this is a show stopper, but obviously any improvements 
would be of benefit.

Tim

Re: drop column functionality

[Bryan Pendleton <bp...@amberpoint.com>]

> 1. Dropping the column seems very slow for large tables. 

This is good to know. I don't think anybody has really experimented with
this yet. Can you quantify "very slow" with some real measurements
you've taken? Do you think it would be reasonable to treat this as
a follow-on improvement to the DERBY-1489 changes, or do you feel that
the performance is so bad that DERBY-1489 is unusable as is?

I guess my assumption was that DROP COLUMN usage in large database
scenarios would be rare, and that it would be more common for people
to want to use DROP COLUMN during their initial application development,
when tables are typically smaller. But perhaps this is a bad assumption?

If you think this would be reasonable to treat as a follow-on
improvement, then please open a new JIRA issue for it, describe
it as well as you can with the data you can provide, and link
the new issue to DERBY-1489 so that we know they're related.

> 2. I'm reluctant to use the SVN trunk in my application, preferring a 
> stable release. Are there any plans for when this patch will get into a 
> stable release.

In general, I think the process is something like:
  - the community reviews the DERBY-1489 changes (that's underway now)
  - those changes get committed to the trunk
  - those changes could appear in future release branches, or could be
    back-ported to patch release of prior release branches.

I think we typically would *not* back-port a new feature to a prior
release branch, so the DERBY-1489 changes would probably next appear
in a stable release in the next release after they are committed to
the trunk.

However, I believe there's nothing that prevents you from constructing
this yourself. You can take the 10.1 source code, and apply the trunk
patch for DROP COLUMN to it, and create your own version of Derby which
is "10.1 with DROP COLUMN functionality". I believe that the DERBY-1489
changes can be successfully applied to the 10.1 source base without too
much work.

> 3. The version from trunk does not appear able to read databases created 
> with the 10.1.3.1 release. Has the database file syntax changed?

I'm hoping one of the other developers can help with this question.
I think that this is on purpose, to avoid accidentally mixing releases
that may differ in major ways, but I think there is a configuration
flag that you can set that will authorize the trunk code to open and
read a previous-release database.

Can anybody help us out here? How can Tim use some of his 10.1 databases
in experimenting with a patched release built from the trunk?

thanks,

bryan