You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by mi...@apache.org on 2014/12/12 21:13:25 UTC
svn commit: r1645055 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/store/access/heap/Heap.java
engine/org/apache/derby/impl/store/access/heap/HeapPostCommit.java
testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
Author: mikem
Date: Fri Dec 12 20:13:25 2014
New Revision: 1645055
URL: http://svn.apache.org/r1645055
Log:
DERBY-6774 background post commit threads cause ASSERTS/errors on interaction with alter table add column
Renable the tests that were failing on some platforms, and adds tests that
were failing consistently in my environment (laptop, windows 7, ibm17).
The fix was to insure that background threads waited on table intent lock
before going to the conglomerate cache.
Before doing this an inconsistent conglomerate would sometimes be faulted into
the cache between time when alter table abort was invalidating the cache and
abort had finished backing out the alter table. Then a subsequent alter table
would fail as it correctly passed in that there was 1 existing column, but the
conglomerate cache thought there were 2 columns.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/heap/Heap.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/heap/HeapPostCommit.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/heap/Heap.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/heap/Heap.java?rev=1645055&r1=1645054&r2=1645055&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/heap/Heap.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/heap/Heap.java Fri Dec 12 20:13:25 2014
@@ -60,6 +60,7 @@ import org.apache.derby.iapi.store.raw.C
import org.apache.derby.iapi.store.raw.LockingPolicy;
import org.apache.derby.iapi.store.raw.Transaction;
import org.apache.derby.iapi.store.raw.Page;
+import org.apache.derby.iapi.store.raw.PageKey;
import org.apache.derby.iapi.store.raw.RawStoreFactory;
import org.apache.derby.iapi.types.DataValueDescriptor;
@@ -432,7 +433,6 @@ public class Heap
// invalidate the cache after an abort of a alter table, but
// I think there is still a race condition.
- /*
if (column_id != format_ids.length)
{
if (SanityManager.DEBUG)
@@ -447,7 +447,6 @@ public class Heap
new Long(column_id),
new Long(this.format_ids.length)));
}
- */
// create a new array, and copy old values to it.
int[] old_format_ids = format_ids;
@@ -723,6 +722,82 @@ public class Heap
}
HeapController heapcontroller = new HeapController();
+
+ heapcontroller.init(open_conglom);
+
+ return(heapcontroller);
+ }
+
+ /**
+ * Open a heap controller given ContainerKey.
+ * <p>
+ * Static routine to open a container given input of the ContainerKey.
+ * Routine will lock the container first, and then get the Heap from
+ * the conglomerate cache. This insures that interaction with the
+ * conglomerate cache is safe with respect to concurrent alter table's
+ * which may or may not commit.
+ *
+ * Currently only package accessible and only used by HeapPostCommit.
+ * Longer term would be better to change all of the open interfaces
+ * to get lock before accessing conglomerate cache rather than have a
+ * specific interface for HeapPostCommit.
+ *
+ * @see Conglomerate#open
+ *
+ * @exception StandardException Standard exception policy.
+ **/
+ static /* package */ ConglomerateController openByContainerKey(
+ ContainerKey container_key,
+ TransactionManager xact_manager,
+ Transaction rawtran,
+ boolean hold,
+ int open_mode,
+ int lock_level,
+ LockingPolicy locking_policy,
+ StaticCompiledOpenConglomInfo static_info,
+ DynamicCompiledOpenConglomInfo dynamic_info)
+ throws StandardException
+ {
+ // get lock on conglomerate before accessing the conglomerate cache.
+ // This prevents background competing threads from loading an
+ // inconsistent conglomerate into the cache while competing with
+ // something like an alter table add column that may or may not
+ // commit.
+
+ if (container_key.getSegmentId() == ContainerHandle.TEMPORARY_SEGMENT)
+ {
+ open_mode |= ContainerHandle.MODE_TEMP_IS_KEPT;
+ }
+
+ // TODO (mikem) - check about open_mode and temp containers.
+ ContainerHandle open_container =
+ rawtran.openContainer(container_key, locking_policy, open_mode);
+
+ Heap heap = (Heap)
+ xact_manager.findExistingConglomerateFromKey(container_key);
+
+ OpenConglomerate open_conglom = new OpenHeap();
+
+
+ if (open_conglom.init(
+ (ContainerHandle) open_container,
+ heap,
+ heap.format_ids,
+ heap.collation_ids,
+ xact_manager,
+ rawtran,
+ hold,
+ open_mode,
+ lock_level,
+ locking_policy,
+ dynamic_info) == null)
+ {
+ throw StandardException.newException(
+ SQLState.HEAP_CONTAINER_NOT_FOUND,
+ new Long(container_key.getContainerId()).toString());
+ }
+
+ HeapController heapcontroller = new HeapController();
heapcontroller.init(open_conglom);
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/heap/HeapPostCommit.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/heap/HeapPostCommit.java?rev=1645055&r1=1645054&r2=1645055&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/heap/HeapPostCommit.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/store/access/heap/HeapPostCommit.java Fri Dec 12 20:13:25 2014
@@ -324,30 +324,24 @@ class HeapPostCommit implements Servicea
// could not be granted immediately.
//Reversed the fix for 4255:
- //page reclaimation is done asynchronosly by raswstore daemon
+ //page reclaimation is done asynchronosly by rawstore daemon
//not good to WAIT FOR LOCKS , as it can freeze the daemon
- //If we can not get the lock this reclamation request will
+ //If we can not get the lock this reclamation request will be
//requeued.
// if does not exist will throw exception, which the code will
// handle in the same way as it does heap.open failing if trying
// to open a dropped container.
- Conglomerate conglom =
- internal_xact.findExistingConglomerateFromKey(
- page_key.getContainerId());
-
- if (SanityManager.DEBUG)
- {
- // This code can only handle Heap conglomerates.
- SanityManager.ASSERT(conglom instanceof Heap,
- "Code expecting PageKey/ContainerKey of a Heap");
- }
-
- Heap heap = (Heap) conglom;
+ // DERBY-6774, changed to use openByContainerKey which insures
+ // that background thread will have a lock on the table before
+ // accessing and possibly loading the conglomerate cache. This
+ // insure it waits for in process alter table calls, before
+ // loading the conglomerate cache.
heapcontroller = (HeapController)
- heap.open(
+ Heap.openByContainerKey(
+ page_key.getContainerId(),
internal_xact,
internal_xact.getRawStoreXact(),
false,
@@ -357,7 +351,7 @@ class HeapPostCommit implements Servicea
internal_xact.getRawStoreXact().newLockingPolicy(
LockingPolicy.MODE_RECORD,
TransactionController.ISOLATION_REPEATABLE_READ, true),
- heap,
+ null,
(DynamicCompiledOpenConglomInfo) null);
// We got a table intent lock, all deleted rows we encounter can
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java?rev=1645055&r1=1645054&r2=1645055&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java Fri Dec 12 20:13:25 2014
@@ -280,14 +280,7 @@ public final class AlterTableTest extend
new String[][]{{"No open scans, etc."}});
}
- /* DERBY-6774, renaming to xtestAddIdentityColumn to stop test from
- * being run in nightly's while I work on fixing the bug. I now have
- * a test that is reproducing the problems so do not need nightly
- * failures to diagnose the problem for now.
- *
- * TODO (mikem) - renenable test after DERBY-6774 is fixed.
- */
- public void xtestAddIdentityColumn() throws SQLException {
+ public void testAddIdentityColumn() throws SQLException {
Statement s = createStatement();
createTestObjects(s);
commit();
@@ -355,6 +348,500 @@ public final class AlterTableTest extend
rollback();
+ // Add an identity column that is generated by default.
+ s.execute("alter table t0 add column "
+ + "id int generated by default as identity");
+ rs = s.executeQuery("select * from t0");
+ JDBC.assertColumnNames(rs, "C1", "ID");
+ rsmd = rs.getMetaData();
+ assertTrue(rsmd.isAutoIncrement(2));
+ assertEquals(ResultSetMetaData.columnNoNulls, rsmd.isNullable(2));
+ rs.close();
+
+ s.execute("insert into t0(c1) values 1,2,3");
+ s.execute("insert into t0(c1, id) values (9, 10)");
+ JDBC.assertFullResultSet(
+ s.executeQuery("select * from t0 order by id"),
+ new String[][] {
+ { "1", "1" },
+ { "2", "2" },
+ { "3", "3" },
+ { "9", "10" },
+ });
+
+ rollback();
+
+ // Add an identity column that is generated by default, to a
+ // non-empty table.
+ s.execute("insert into t0 values 1");
+ s.execute("alter table t0 add column id int "
+ + "generated by default as identity");
+ JDBC.assertSingleValueResultSet(
+ s.executeQuery("select id from t0"), "1");
+ s.execute("insert into t0(c1) values 5,6,7");
+ JDBC.assertFullResultSet(
+ s.executeQuery("select * from t0 where c1 > 1 order by c1"),
+ new String[][] {
+ { "5", "2" },
+ { "6", "3" },
+ { "7", "4" },
+ });
+ rollback();
+
+ // Cannot add an identity column without specifying type.
+ assertCompileError("42XA9", "alter table t0 add column id "
+ + "generated always as identity");
+
+ // Generated identity values cannot grow beyond the limits of
+ // the data type.
+ s.execute("insert into t0 values 1,2,3,4");
+ assertStatementError("2200H", s,
+ "alter table t0 add column id smallint generated always as "
+ + "identity (start with 30000, increment by 1000)");
+ rollback();
+
+ s.execute("insert into t0 values 1,2,3");
+ s.execute("alter table t0 add column id smallint generated always as "
+ + "identity (start with 30000, increment by 1000)");
+ JDBC.assertFullResultSet(
+ s.executeQuery("select * from t0 order by id"),
+ new String[][] {
+ { "1", "30000" },
+ { "2", "31000" },
+ { "3", "32000" },
+ });
+ assertStatementError("2200H", s, "insert into t0(c1) values 4");
+ rollback();
+
+ // Drop an identity column that was added with ALTER TABLE.
+ s.execute("alter table t0 add column "
+ + "id int generated always as identity");
+ s.execute("insert into t0(c1) values 1");
+ s.execute("alter table t0 drop column id");
+ rs = s.executeQuery("select * from t0");
+ JDBC.assertColumnNames(rs, "C1");
+ JDBC.assertSingleValueResultSet(rs, "1");
+ rollback();
+
+ // Drop a table that contains an identity column added with ALTER TABLE.
+ s.execute("alter table t0 add column "
+ + "id int generated always as identity");
+ s.execute("alter table t0 drop column id");
+ s.execute("drop table t0");
+ rollback();
+
+ // Adding a primary key column as an identity column should be allowed.
+ s.execute("alter table t0_1 add column id int primary key "
+ + "generated always as identity");
+ rollback();
+ }
+
+ /**
+ * Slight changes to testAddIdentityColumn.
+ * <p>
+ * Another version of testAddIdenityColumn that provides slightly
+ * different code path, found useful while debugging DERBY-6774.
+ **/
+ public void testAddIdentityColumn5() throws SQLException {
+ Statement s = createStatement();
+ createTestObjects(s);
+ commit();
+
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+
+ rollback();
+
+
+ // Add an identity column that is generated by default.
+ s.execute("alter table t0 add column "
+ + "id int generated by default as identity");
+ ResultSet rs = s.executeQuery("select * from t0");
+ JDBC.assertColumnNames(rs, "C1", "ID");
+ ResultSetMetaData rsmd = rs.getMetaData();
+ assertTrue(rsmd.isAutoIncrement(2));
+ assertEquals(ResultSetMetaData.columnNoNulls, rsmd.isNullable(2));
+ rs.close();
+
+ /*
+
+ s.execute("insert into t0(c1) values 1,2,3");
+ s.execute("insert into t0(c1, id) values (9, 10)");
+ JDBC.assertFullResultSet(
+ s.executeQuery("select * from t0 order by id"),
+ new String[][] {
+ { "1", "1" },
+ { "2", "2" },
+ { "3", "3" },
+ { "9", "10" },
+ });
+ */
+
+
+ rollback();
+
+
+ // Add an identity column that is generated by default, to a
+ // non-empty table.
+ s.execute("insert into t0 values 1");
+ s.execute("alter table t0 add column id int "
+ + "generated by default as identity");
+ JDBC.assertSingleValueResultSet(
+ s.executeQuery("select id from t0"), "1");
+ s.execute("insert into t0(c1) values 5,6,7");
+ JDBC.assertFullResultSet(
+ s.executeQuery("select * from t0 where c1 > 1 order by c1"),
+ new String[][] {
+ { "5", "2" },
+ { "6", "3" },
+ { "7", "4" },
+ });
+ rollback();
+
+ }
+
+
+ /**
+ * Slight changes to testAddIdentityColumn.
+ * <p>
+ * Another version of testAddIdenityColumn that provides slightly
+ * different code path, found useful while debugging DERBY-6774.
+ **/
+ public void testAddIdentityColumn4() throws SQLException {
+ Statement s = createStatement();
+ createTestObjects(s);
+ commit();
+
+
+ // Add an identity column to a empty table.
+ s.execute("alter table t0 add column "
+ + "id int generated always as identity "
+ + "(start with 100, increment by 5)");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+
+ rollback();
+
+ // Add an identity column that is generated by default.
+ s.execute("alter table t0 add column "
+ + "id int generated by default as identity");
+ ResultSet rs = s.executeQuery("select * from t0");
+ JDBC.assertColumnNames(rs, "C1", "ID");
+ ResultSetMetaData rsmd = rs.getMetaData();
+ assertTrue(rsmd.isAutoIncrement(2));
+ assertEquals(ResultSetMetaData.columnNoNulls, rsmd.isNullable(2));
+ rs.close();
+
+ s.execute("insert into t0(c1) values 1,2,3");
+ s.execute("insert into t0(c1, id) values (9, 10)");
+ JDBC.assertFullResultSet(
+ s.executeQuery("select * from t0 order by id"),
+ new String[][] {
+ { "1", "1" },
+ { "2", "2" },
+ { "3", "3" },
+ { "9", "10" },
+ });
+
+ rollback();
+
+ // Add an identity column that is generated by default, to a
+ // non-empty table.
+ s.execute("insert into t0 values 1");
+ s.execute("alter table t0 add column id int "
+ + "generated by default as identity");
+ JDBC.assertSingleValueResultSet(
+ s.executeQuery("select id from t0"), "1");
+ s.execute("insert into t0(c1) values 5,6,7");
+ JDBC.assertFullResultSet(
+ s.executeQuery("select * from t0 where c1 > 1 order by c1"),
+ new String[][] {
+ { "5", "2" },
+ { "6", "3" },
+ { "7", "4" },
+ });
+ rollback();
+
+ }
+
+ /**
+ * Slight changes to testAddIdentityColumn.
+ * <p>
+ * Another version of testAddIdenityColumn that provides slightly
+ * different code path, found useful while debugging DERBY-6774.
+ **/
+ public void testAddIdentityColumn3() throws SQLException {
+ Statement s = createStatement();
+ createTestObjects(s);
+ commit();
+
+
+ // Add an identity column to a non-empty table.
+ s.execute("insert into t0 values 1,2,3,4,5");
+ s.execute("alter table t0 add column "
+ + "id int generated always as identity "
+ + "(start with 100, increment by 5)");
+ JDBC.assertFullResultSet(
+ s.executeQuery("select * from t0 order by id"),
+ new String[][] {
+ { "1", "100" },
+ { "2", "105" },
+ { "3", "110" },
+ { "4", "115" },
+ { "5", "120" },
+ });
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3");
+ JDBC.assertFullResultSet(
+ s.executeQuery("select * from t0 order by id"),
+ new String[][] {
+ { "1", "125" },
+ { "2", "130" },
+ { "3", "135" },
+ });
+
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+
+ rollback();
+
+ // Add an identity column that is generated by default.
+ s.execute("alter table t0 add column "
+ + "id int generated by default as identity");
+ ResultSet rs = s.executeQuery("select * from t0");
+ JDBC.assertColumnNames(rs, "C1", "ID");
+ ResultSetMetaData rsmd = rs.getMetaData();
+ assertTrue(rsmd.isAutoIncrement(2));
+ assertEquals(ResultSetMetaData.columnNoNulls, rsmd.isNullable(2));
+ rs.close();
+
+ s.execute("insert into t0(c1) values 1,2,3");
+ s.execute("insert into t0(c1, id) values (9, 10)");
+ JDBC.assertFullResultSet(
+ s.executeQuery("select * from t0 order by id"),
+ new String[][] {
+ { "1", "1" },
+ { "2", "2" },
+ { "3", "3" },
+ { "9", "10" },
+ });
+
+ rollback();
+
+ // Add an identity column that is generated by default, to a
+ // non-empty table.
+ s.execute("insert into t0 values 1");
+ s.execute("alter table t0 add column id int "
+ + "generated by default as identity");
+ JDBC.assertSingleValueResultSet(
+ s.executeQuery("select id from t0"), "1");
+ s.execute("insert into t0(c1) values 5,6,7");
+ JDBC.assertFullResultSet(
+ s.executeQuery("select * from t0 where c1 > 1 order by c1"),
+ new String[][] {
+ { "5", "2" },
+ { "6", "3" },
+ { "7", "4" },
+ });
+ rollback();
+
+ // Cannot add an identity column without specifying type.
+ assertCompileError("42XA9", "alter table t0 add column id "
+ + "generated always as identity");
+
+ // Generated identity values cannot grow beyond the limits of
+ // the data type.
+ s.execute("insert into t0 values 1,2,3,4");
+ assertStatementError("2200H", s,
+ "alter table t0 add column id smallint generated always as "
+ + "identity (start with 30000, increment by 1000)");
+ rollback();
+
+ s.execute("insert into t0 values 1,2,3");
+ s.execute("alter table t0 add column id smallint generated always as "
+ + "identity (start with 30000, increment by 1000)");
+ JDBC.assertFullResultSet(
+ s.executeQuery("select * from t0 order by id"),
+ new String[][] {
+ { "1", "30000" },
+ { "2", "31000" },
+ { "3", "32000" },
+ });
+ assertStatementError("2200H", s, "insert into t0(c1) values 4");
+ rollback();
+
+ // Drop an identity column that was added with ALTER TABLE.
+ s.execute("alter table t0 add column "
+ + "id int generated always as identity");
+ s.execute("insert into t0(c1) values 1");
+ s.execute("alter table t0 drop column id");
+ rs = s.executeQuery("select * from t0");
+ JDBC.assertColumnNames(rs, "C1");
+ JDBC.assertSingleValueResultSet(rs, "1");
+ rollback();
+
+ // Drop a table that contains an identity column added with ALTER TABLE.
+ s.execute("alter table t0 add column "
+ + "id int generated always as identity");
+ s.execute("alter table t0 drop column id");
+ s.execute("drop table t0");
+ rollback();
+
+ // Adding a primary key column as an identity column should be allowed.
+ s.execute("alter table t0_1 add column id int primary key "
+ + "generated always as identity");
+ rollback();
+ }
+
+ /**
+ * Slight changes to testAddIdentityColumn.
+ * <p>
+ * Another version of testAddIdenityColumn that provides slightly
+ * different code path, found useful while debugging DERBY-6774.
+ **/
+ public void testAddIdentityColumn2() throws SQLException {
+ Statement s = createStatement();
+ createTestObjects(s);
+ commit();
+
+ // Add an identity column, and verify that it is correctly identified
+ // as one.
+ s.execute("alter table t0 add column "
+ + "id int generated always as identity");
+ ResultSet rs = s.executeQuery("select * from t0");
+ JDBC.assertColumnNames(rs, "C1", "ID");
+ ResultSetMetaData rsmd = rs.getMetaData();
+ assertTrue(rsmd.isAutoIncrement(2));
+ assertEquals(ResultSetMetaData.columnNoNulls, rsmd.isNullable(2));
+ rs.close();
+
+ // Cannot set the value of an identity column that is GENERATED
+ // ALWAYS AS.
+ assertCompileError("42Z23", "insert into t0(c1, id) values (1, 1)");
+
+ s.execute("insert into t0(c1, id) values (1, default)");
+ s.execute("insert into t0(c1) values 2,3,4");
+ JDBC.assertFullResultSet(
+ s.executeQuery("select * from t0 order by id"),
+ new String[][] {
+ { "1", "1" },
+ { "2", "2" },
+ { "3", "3" },
+ { "4", "4" },
+ });
+
+ // Only one identity column is allowed per table.
+ assertCompileError("428C1", "alter table t0 add column "
+ + "id2 bigint generated always as identity");
+ s.execute("create table table_with_identity("
+ + "id int generated always as identity)");
+ assertCompileError("428C1",
+ "alter table table_with_identity add column "
+ + "id2 bigint generated always as identity");
+
+ rollback();
+
+ // Add an identity column to a non-empty table.
+ s.execute("insert into t0 values 1,2,3,4,5");
+ s.execute("alter table t0 add column "
+ + "id int generated always as identity "
+ + "(start with 100, increment by 5)");
+ JDBC.assertFullResultSet(
+ s.executeQuery("select * from t0 order by id"),
+ new String[][] {
+ { "1", "100" },
+ { "2", "105" },
+ { "3", "110" },
+ { "4", "115" },
+ { "5", "120" },
+ });
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3");
+ JDBC.assertFullResultSet(
+ s.executeQuery("select * from t0 order by id"),
+ new String[][] {
+ { "1", "125" },
+ { "2", "130" },
+ { "3", "135" },
+ });
+
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+ s.execute("delete from t0");
+ s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
+
+ rollback();
+
// Add an identity column that is generated by default.
s.execute("alter table t0 add column "
+ "id int generated by default as identity");