Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 75564 invoked from network); 26 Feb 2008 16:48:53 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 26 Feb 2008 16:48:53 -0000 Received: (qmail 98841 invoked by uid 500); 26 Feb 2008 16:48:43 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 98816 invoked by uid 500); 26 Feb 2008 16:48:43 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 98798 invoked by uid 99); 26 Feb 2008 16:48:42 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 26 Feb 2008 08:48:42 -0800 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO eris.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 26 Feb 2008 16:48:03 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 925661A9832; Tue, 26 Feb 2008 08:48:22 -0800 (PST) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r631287 - in /db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests: master/access.out tests/store/access.sql Date: Tue, 26 Feb 2008 16:48:22 -0000 To: derby-commits@db.apache.org From: kmarsden@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20080226164822.925661A9832@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: kmarsden Date: Tue Feb 26 08:48:21 2008 New Revision: 631287 URL: http://svn.apache.org/viewvc?rev=631287&view=rev Log: DERBY-990 store/access.sql fails - incorrect index? port revision 393896 from trunk Comments from original checkin: Removed piece of this test that is machine/jvm/thread timing dependent on the background space reclamation work. Filed a new issue DERBY-1213, to rewrite this testing in a more reproducible manner. Contributed by Mike Matrigali Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/access.out db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/access.sql Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/access.out URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/access.out?rev=631287&r1=631286&r2=631287&view=diff ============================================================================== --- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/access.out (original) +++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/access.out Tue Feb 26 08:48:21 2008 @@ -2604,163 +2604,6 @@ 0 |0_col02 |0_col03 |0_col04|0_col05 |0_06 |0_07 |0_ |0_09 |0_col10llllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll012340_col10lllllllllll |0_ |0 |0_col13|0_col14 |0 |0_col16 |0_col17 |0_ 1 |1_col02 |1_col03 |1_col04|1_col05 |1_06 |1_07 |1_ |1_09 |1_col10llllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll012340_col10lllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll012340_col10lllllllllllxxxxxxxxxxxxxxxxxxx |1_ |1 |1_col13|1_col14 |1 |1_col16 |1_col17 |1_ ij> commit; -ij> -- buddy test case for reclaiming deleted rows during split. --- test space reclamation using the SpaceTable VTI. --- o insert bunch of rows with sequential keys. --- o create non-unique index. --- o check space usage for index using SpaceTable VTI --- o delete every other one less than 1000 - this will make normal post --- commit not fire as no page should have all deleted rows (< 1000 avoids --- possible case where last page only has 1 row). --- o commit --- o now reinsert rows into the "holes" which before the fix would cause --- splits, but now will force reclaim space and reuse existing space in --- btree. --- o redo delete, commit and insert several times over --- o check space usage for index using SpaceTable VTI is same as above --- set page size to default. -call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096'); -0 rows inserted/updated/deleted -ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.minimumRecordSize', '1'); -0 rows inserted/updated/deleted -ij> autocommit off; -ij> commit; -ij> drop table foo; -ERROR 42Y55: 'DROP TABLE' cannot be performed on 'FOO' because it does not exist. -ij> drop table foo2; -ERROR 42Y55: 'DROP TABLE' cannot be performed on 'FOO2' because it does not exist. -ij> -- create and load a table with values from 1024 down to 1, -create table foo (a int, b char(200), c int); -0 rows inserted/updated/deleted -ij> insert into foo values (1024, 'even', 0); -1 row inserted/updated/deleted -ij> insert into foo (select foo.a - 1, 'odd' , 1 from foo); -1 row inserted/updated/deleted -ij> insert into foo (select foo.a - 2, foo.b, foo.c from foo); -2 rows inserted/updated/deleted -ij> insert into foo (select foo.a - 4, foo.b, foo.c from foo); -4 rows inserted/updated/deleted -ij> insert into foo (select foo.a - 8, foo.b, foo.c from foo); -8 rows inserted/updated/deleted -ij> insert into foo (select foo.a - 16, foo.b, foo.c from foo); -16 rows inserted/updated/deleted -ij> insert into foo (select foo.a - 32, foo.b, foo.c from foo); -32 rows inserted/updated/deleted -ij> insert into foo (select foo.a - 64, foo.b, foo.c from foo); -64 rows inserted/updated/deleted -ij> insert into foo (select foo.a - 128, foo.b, foo.c from foo); -128 rows inserted/updated/deleted -ij> insert into foo (select foo.a - 256, foo.b, foo.c from foo); -256 rows inserted/updated/deleted -ij> insert into foo (select foo.a - 512, foo.b, foo.c from foo); -512 rows inserted/updated/deleted -ij> -- insert into the "holes", but different keys (even2 instead of even) -create table foo2 (a int, b char(200), c int); -0 rows inserted/updated/deleted -ij> insert into foo2 (select * from foo); -1024 rows inserted/updated/deleted -ij> delete from foo2 where foo2.c = 1; -512 rows inserted/updated/deleted -ij> delete from foo2 where foo2.a >= 1000; -13 rows inserted/updated/deleted -ij> -- create "packed" index. -create index a on foo (a, b); -0 rows inserted/updated/deleted -ij> commit; -ij> create table spaceused (a int); -0 rows inserted/updated/deleted -ij> -- check space usage on the index -insert into spaceused -select numallocatedpages -from new org.apache.derby.diag.SpaceTable('FOO') t -where conglomeratename = 'A'; -1 row inserted/updated/deleted -ij> -- delete every other row -delete from foo where foo.c = 0 and foo.a < 1000; -499 rows inserted/updated/deleted -ij> -- turn all the deletes into "committed deletes" -commit; -ij> -- wait for post commit on heap deleted rows to finish, so that there is --- no interference between post commit heap processing, and btree reclamation --- during split. -CALL WAIT_FOR_POST_COMMIT(); -0 rows inserted/updated/deleted -ij> -- reinsert -insert into foo (select * from foo2); -499 rows inserted/updated/deleted -ij> commit; -ij> -- do the above several times -delete from foo where foo.c = 0 and foo.a < 1000; -499 rows inserted/updated/deleted -ij> commit; -ij> -- wait for post commit on heap deleted rows to finish, so that there is --- no interference between post commit heap processing, and btree reclamation --- during split. -CALL WAIT_FOR_POST_COMMIT(); -0 rows inserted/updated/deleted -ij> insert into foo (select * from foo2); -499 rows inserted/updated/deleted -ij> delete from foo where foo.c = 0 and foo.a < 1000; -499 rows inserted/updated/deleted -ij> commit; -ij> -- wait for post commit on heap deleted rows to finish, so that there is --- no interference between post commit heap processing, and btree reclamation --- during split. -CALL WAIT_FOR_POST_COMMIT(); -0 rows inserted/updated/deleted -ij> insert into foo (select * from foo2); -499 rows inserted/updated/deleted -ij> delete from foo where foo.c = 0 and foo.a < 1000; -499 rows inserted/updated/deleted -ij> commit; -ij> -- wait for post commit on heap deleted rows to finish, so that there is --- no interference between post commit heap processing, and btree reclamation --- during split. -CALL WAIT_FOR_POST_COMMIT(); -0 rows inserted/updated/deleted -ij> insert into foo (select * from foo2); -499 rows inserted/updated/deleted -ij> delete from foo where foo.c = 0 and foo.a < 1000; -499 rows inserted/updated/deleted -ij> commit; -ij> -- wait for post commit on heap deleted rows to finish, so that there is --- no interference between post commit heap processing, and btree reclamation --- during split. -CALL WAIT_FOR_POST_COMMIT(); -0 rows inserted/updated/deleted -ij> insert into foo (select * from foo2); -499 rows inserted/updated/deleted -ij> delete from foo where foo.c = 0 and foo.a < 1000; -499 rows inserted/updated/deleted -ij> commit; -ij> -- wait for post commit on heap deleted rows to finish, so that there is --- no interference between post commit heap processing, and btree reclamation --- during split. -CALL WAIT_FOR_POST_COMMIT(); -0 rows inserted/updated/deleted -ij> insert into foo (select * from foo2); -499 rows inserted/updated/deleted -ij> commit; -ij> -- check space usage on the index -insert into spaceused -select numallocatedpages -from new org.apache.derby.diag.SpaceTable('FOO') t -where conglomeratename = 'A'; -1 row inserted/updated/deleted -ij> -- should only be 1 distinct row in this table since space used by the --- index should be the same before and after -select count(distinct a) -from spaceused; -1 ------------ -1 -ij> -- cleanup -drop table foo; -0 rows inserted/updated/deleted -ij> drop table foo2; -0 rows inserted/updated/deleted -ij> drop table spaceused; -0 rows inserted/updated/deleted ij> -- test case for track 3149, improving max on btree optimization autocommit off; ij> create table foo (a int, b varchar(500), c int); Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/access.sql URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/access.sql?rev=631287&r1=631286&r2=631287&view=diff ============================================================================== --- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/access.sql (original) +++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/access.sql Tue Feb 26 08:48:21 2008 @@ -822,136 +822,6 @@ commit; --- buddy test case for reclaiming deleted rows during split. --- test space reclamation using the SpaceTable VTI. --- o insert bunch of rows with sequential keys. --- o create non-unique index. --- o check space usage for index using SpaceTable VTI --- o delete every other one less than 1000 - this will make normal post --- commit not fire as no page should have all deleted rows (< 1000 avoids --- possible case where last page only has 1 row). --- o commit --- o now reinsert rows into the "holes" which before the fix would cause --- splits, but now will force reclaim space and reuse existing space in --- btree. --- o redo delete, commit and insert several times over --- o check space usage for index using SpaceTable VTI is same as above - --- set page size to default. -call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096'); -call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.minimumRecordSize', '1'); - -autocommit off; -commit; -drop table foo; -drop table foo2; - --- create and load a table with values from 1024 down to 1, -create table foo (a int, b char(200), c int); - -insert into foo values (1024, 'even', 0); -insert into foo (select foo.a - 1, 'odd' , 1 from foo); - -insert into foo (select foo.a - 2, foo.b, foo.c from foo); -insert into foo (select foo.a - 4, foo.b, foo.c from foo); -insert into foo (select foo.a - 8, foo.b, foo.c from foo); -insert into foo (select foo.a - 16, foo.b, foo.c from foo); -insert into foo (select foo.a - 32, foo.b, foo.c from foo); -insert into foo (select foo.a - 64, foo.b, foo.c from foo); -insert into foo (select foo.a - 128, foo.b, foo.c from foo); -insert into foo (select foo.a - 256, foo.b, foo.c from foo); -insert into foo (select foo.a - 512, foo.b, foo.c from foo); - --- insert into the "holes", but different keys (even2 instead of even) -create table foo2 (a int, b char(200), c int); -insert into foo2 (select * from foo); -delete from foo2 where foo2.c = 1; -delete from foo2 where foo2.a >= 1000; - --- create "packed" index. -create index a on foo (a, b); -commit; - -create table spaceused (a int); - --- check space usage on the index -insert into spaceused -select numallocatedpages -from new org.apache.derby.diag.SpaceTable('FOO') t -where conglomeratename = 'A'; - --- delete every other row -delete from foo where foo.c = 0 and foo.a < 1000; --- turn all the deletes into "committed deletes" -commit; --- wait for post commit on heap deleted rows to finish, so that there is --- no interference between post commit heap processing, and btree reclamation --- during split. -CALL WAIT_FOR_POST_COMMIT(); - --- reinsert -insert into foo (select * from foo2); -commit; --- do the above several times -delete from foo where foo.c = 0 and foo.a < 1000; -commit; --- wait for post commit on heap deleted rows to finish, so that there is --- no interference between post commit heap processing, and btree reclamation --- during split. -CALL WAIT_FOR_POST_COMMIT(); - -insert into foo (select * from foo2); -delete from foo where foo.c = 0 and foo.a < 1000; -commit; --- wait for post commit on heap deleted rows to finish, so that there is --- no interference between post commit heap processing, and btree reclamation --- during split. -CALL WAIT_FOR_POST_COMMIT(); - -insert into foo (select * from foo2); -delete from foo where foo.c = 0 and foo.a < 1000; -commit; --- wait for post commit on heap deleted rows to finish, so that there is --- no interference between post commit heap processing, and btree reclamation --- during split. -CALL WAIT_FOR_POST_COMMIT(); - -insert into foo (select * from foo2); -delete from foo where foo.c = 0 and foo.a < 1000; -commit; --- wait for post commit on heap deleted rows to finish, so that there is --- no interference between post commit heap processing, and btree reclamation --- during split. -CALL WAIT_FOR_POST_COMMIT(); - -insert into foo (select * from foo2); -delete from foo where foo.c = 0 and foo.a < 1000; -commit; --- wait for post commit on heap deleted rows to finish, so that there is --- no interference between post commit heap processing, and btree reclamation --- during split. -CALL WAIT_FOR_POST_COMMIT(); - - -insert into foo (select * from foo2); - -commit; - --- check space usage on the index -insert into spaceused -select numallocatedpages -from new org.apache.derby.diag.SpaceTable('FOO') t -where conglomeratename = 'A'; - --- should only be 1 distinct row in this table since space used by the --- index should be the same before and after -select count(distinct a) -from spaceused; - --- cleanup -drop table foo; -drop table foo2; -drop table spaceused; -- test case for track 3149, improving max on btree optimization autocommit off;