GemFire XD has limitations and restrictions for SQL statements, clauses, and expressions.
- Basic Database Limitations
- ALTER TABLE Limitations
- Procedure and Function Limitations
- Auto-Generated Column Limitations
- LONG/LOB Column Restrictions
- Bulk Update Limitations
- Cascade DELETE Not Supported
- Locking Prioritizes DML over DDL
- Expiration and Eviction Limitations
- INSERT with Subselect Limitations
- LOCK TABLE Not Supported
- Procedure Invocation Limitations (Data-Aware and Non-Data-Aware Procedures)
- RENAME Not Supported
- Updatable Result Sets Limitations
- Transaction Isolation Level Support
- HA Failover for Transactions Not Supported
- Trigger Limitations
- UNION, INTERSECT, and EXCEPT Operator Limitations
- VIEW Limitations
Basic Database Limitations
The following table describes basic limits associated with database objects and statements.
|Maximum columns in a table||1,012|
|Maximum columns in a view||5,000|
|Maximum number of parameters in a stored procedure||90|
|Maximum indexes on a table||32,767 or storage capacity|
|Maximum tables referenced in an SQL statement or a view||storage capacity|
|Maximum elements in a select list||1,012|
|Maximum predicates in a WHERE or HAVING clause||storage capacity|
|Maximum number of columns in a GROUP BY clause||32,677|
|Maximum number of columns in an ORDER BY clause||1,012|
|Maximum number of prepared statements||storage capacity|
|Maximum declared cursors in a program||storage capacity|
|Maximum number of cursors opened at one time||storage capacity|
|Maximum number of constraints on a table||storage capacity|
|Maximum level of subquery nesting||storage capacity|
|Maximum number of subqueries in a single statement||storage capacity|
|Maximum number of rows changed in a unit of work||storage capacity|
|Maximum constants in a statement||storage capacity|
|Maximum depth of cascaded triggers||16|
ALTER TABLE Limitations
- Adding or dropping a primary key constraint (or a primary key column) if a table currently stores data, or if a partitioned table has buckets allocated for data. In the case of a partitioned table, GemFire XD allocates buckets for table data if you insert any data, and those buckets are kept even if you use DELETE to remove all data from the table. To remove both data and bucket allocations for a parttitioned table, use TRUNCATE TABLE.
- Dropping a column that is used for table partitioning.
- Adding a unique key to a table that contains data when a foreign key exists on the unique
column is not supported when:
- A subset of the foreign key columns are partitioning columns for the table, and
- The foreign key columns are not colocated.
Converseley, attempting to add a foreign key constraint on unique key columns of a master table fails with an exception if the foreign key table columns are not colocated with the master table.
In addition, the ALTER COLUMN clause as described the SQL-92 standard is not implemented. GemFire XD only supports the ALTER COLUMN clause for changing a non-identity column to a GENERATED ALWAYS AS IDENTITY column.
Procedure and Function Limitations
GemFire XD does not support executing DDL statements in the body of a procedure or function.
Auto-Generated Column Limitations
- Only INT and BIGINT column types can be marked as auto-generated IDENTITY columns.
- The START WITH and INCREMENT BY clauses are supported only for GENERATED BY DEFAULT identity columns.
- If the maximum permissible value for the type is reached in any insert, then GemFire XD rolls over to the initial value (for example, after reaching value 2147483647, the next generated identity for an INT column is -2147483647). This does not necessarily mean that all possible values of that type have been used up, because it is possible that some values remain unused.
Applications should not depend on identity values being incremental across the distributed system, because GemFire XD provides no ordering guarantee for concurrent inserts from multiple members. However, inserts from a single member will have the generated values in ascending order and applications can use that for ordering purposes.
LONG/LOB Column Restrictions
- LONG VARCHAR FOR BIT DATA
Columns of type LONG VARCHAR are supported in these cases.
Bulk Update Limitations
If a SQL statement performs a bulk update operation on multiple GemFire XD members, any exception that occurs during the bulk update can leave some rows updated while other rows are not updated. Use transactions with bulk update statements to ensure that all updates succeed or roll back as a whole. See Atomicity for Bulk Updates.
Cascade DELETE Not Supported
GemFire XD does not support cascade delete operations.
Locking Prioritizes DML over DDL
The GemFire XD locking behavior prioritizes DML execution over DDL statements. DDL statments may receive a lock timeout exception (SQLState: 40XL1) if your system is processing numerous concurrent DML and DDL statements. You can configure the maximum amount of time that DDL statements wait for locks using gemfirexd.max-lock-wait.
Expiration and Eviction Limitations
EXPIRE ENTRY WITH IDLETIME works only when a primary key based query is fired. Otherwise the system will not modify its accessed time when table scans or index scans happen and it gets destroyed.
EXPIRATION or EVICTION with action as DESTROY should not be set on a parent table having child tables with foreign key reference to it. This is due to a lack of cascade delete support in GemFire XD. If an attempt is made to create a child table having foreign key reference to a table with such a policy then a SQLException is thrown (SQLState: "X0Y99").
Eviction with the DESTROY action is not supported for replicated tables. Eviction with the OVERFLOW action is supported for replicated tables, as is the EXPIRE Clause.
INSERT with Subselect Limitations
GemFire XD has a limited support for INSERT statements that use a subselect statement. Nested selects and selects having aggregates are not supported; these queries throw a feature not implemented exception (SQLSTATE 0A000).
LOCK TABLE Not Supported
The LOCK TABLE statement is not supported in this release of GemFire XD.
Procedure Invocation Limitations (Data-Aware and Non-Data-Aware Procedures)
When you use the ON TABLE extension in a CALL statement, the WHERE clause is mandatory. If you need to route a data-aware procedure to all members that host the table (without any pruning), then you must specify some extraneous condition that always evaluates to true (such as WHERE 1=1).
A server can only handle Java procedure definitions that exactly match the JDBC parameter types in a CREATE PROCEDURE statement. If a procedure specifies parameter types that use the base class of a corresponding java type (for example, if a procedure uses java.util.Date instead of java.sql.Date) then the invocation from the client side fails.
RENAME Not Supported
GemFire XD does not support the RENAME statement as specified by the SQL-92 standard. A SQLException with state "0A000" is thrown, although the statement is not treated as a syntactical error.
Updatable Result Sets Limitations
GemFire XD supports updatable result sets only when the result set is obtained from a JDBC peer client connection using a SQL query that includes the FOR UPDATE clause. For example, you cannot use the following JDBC commands to work with an updatable result set:
Statement s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); String sql = "select * from trade.networth where cid > " + lowCid + " and cid <= " + highCid; ResultSet updatableRs = s.executeQuery(sql);
GemFire XD does not support the WHERE CURRENT OF clause with an UPDATE or DELETE statement to perform positioned updates with updatable cursors.
SELECT … FOR UPDATE does not provide updatable result sets for thin client connections. Any attempt to use the result set to update rows results in an exception or undefined behavior. However the SELECT will obtain appropriate locks on the affected rows, and afterward an explicit update statement can be executed to update the rows. Use the JDBC peer client driver instead.
The SELECT … FOR UPDATE statement does not lock any rows if the statement is not part of a transaction. If you use this statement outside of a transactional context (isolation level Connection.TRANSACTION_NONE) then a SQLWarning is raised and logged. This occurs because in a non-transactional context, appropriate row locks are not obtained during the select phase, and the update phase might see a modified row.
In a transaction, GemFire XD locks all rows returned in a SELECT ... FOR UPDATE statement. It releases them only after the result set is closed (with ResultSet.close() or because of a transaction commit or rollback).
GemFire XD does not support holdable result sets (ResultSet.HOLD_CURSORS_OVER_COMMIT).
Transaction Isolation Level Support
This release of GemFire XD supports the READ_COMMITTED, READ_UNCOMMITTED, and REPEATABLE_READ transaction isolation levels. Any transaction that uses the READ_UNCOMMITTED level is implicitly upgraded to be READ_COMMITTED. When nothing is specified, then JDBC operations use TRANSACTION_NONE isolation which denotes the absence of a transaction. However, GemFire XD still offers certain data consistency and atomicity guarantees as described in Understanding the Data Consistency Model.
Using Distributed Transactions in Your Applications provides an overview of the distributed transaction implementation and semantics.
HA Failover for Transactions Not Supported
This version of GemFire XD does not support transparent failover for transactions. If a member that is participating in a transaction goes down in the middle of the transaction or during a commit, then the commit throws a commit failure exception (SQLState: X0Z16) and the transaction is rolled back. Similarly, GemFire XD does not support automatically copying an existing transactional state to new members that join the system. If a member joins the distributed system in the middle of a transaction and that member is a data store for one of the tables being updated in the transaction, then the transaction fails at commit and is rolled back (SQLState: X0Z16). See Rollback Behavior and Member Failures.
This release of GemFire XD does not support statement triggers.
UNION, INTERSECT, and EXCEPT Operator Limitations
GemFire XD does not support any query that has either nested set operators or a set operator with either a join, function expression, SQL procedure, view, or sub-query. There is no explicit support provided for ORDER BY, GROUP BY, or complex filters in the WHERE clause in either child of a query that uses a set operator. Also, transactions and high availability features are not supported for queries that use a set operator.
In this context, a set operator includes any of these operators: UNION DISTINCT, UNION, UNION ALL, INTERSECT DISTINCT, INTERSECT, INTERSECT ALL, EXCEPT DISTINCT, EXCEPT, or EXCEPT ALL.
GemFire XD does not support views that involve grouping, aggregate, distinct, or join operations on a partitioned table.