Product Limitations

GemFire XD has limitations and restrictions for SQL statements, clauses, and expressions.

Basic Database Limitations

The following table describes basic limits associated with database objects and statements.

Value Limit
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

This release of GemFire XD has the following restrictions for ALTER TABLE. GemFire XD throws a SQLException "Feature not implemented" with SQLState "0A000" if any of these actions are attempted:
  • 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

This release of GemFire XD supports auto-generated IDENTITY columns, but has the following 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

GemFire XD does not support using columns of the following data types in indexes, ORDER BY clauses, GROUP BY clauses, DISTINCT clauses, UNION clauses, or other set operations:
  • BLOB
  • CLOB
  • 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").

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.

Trigger Limitations

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.

VIEW Limitations

GemFire XD does not support views that involve grouping, aggregate, distinct, or join operations on a partitioned table.