6.5. SQL Limitations
Prev UpChapter 6. Shardman ReferenceHome Next

6.5. SQL Limitations

6.5.1. ALTER SYSTEM Limitations
6.5.2. ALTER TABLE Limitations
6.5.3. CREATE TABLE Limitations
6.5.4. DROP TABLE Limitations
6.5.5. CREATE INDEX CONCURRENTLY Limitations
6.5.6. UPDATE Limitations
6.5.7. INSERT ON CONFLICT DO UPDATE Limitations
6.5.8. Limitations of Managing Global Roles
6.5.9. ALTER SCHEMA Limitations
6.5.10. DROP SERVER Limitations
6.5.11. CREATE COLLATION Limitations
6.5.12. Other Limitations

To ensure consistency of a sharded database, Shardman imposes some restrictions on SQL commands executed.

6.5.1.  ALTER SYSTEM Limitations

6.5.2.  ALTER TABLE Limitations

  • ALTER TABLE is prohibited for partitions of sharded tables.

  • All forms of ALTER TABLE are prohibited for sharded or global tables except these:

    • ALTER TABLE OWNER is allowed. For sharded table it also changes the owner of table partitions. Only the global user can be an owner of sharded or global table.

    • ALTER TABLE COLUMN TYPE is allowed with limitations. You cannot alter type of sharded table column participating in sharding or partitioning key. You cannot alter type of sharded table column with USING clause (but for global tables it is allowed). Also, it is a user's duty for now to create and keep new type exactly equal on every cluster node.

    • ALTER TABLE COLUMN RENAME is allowed.

    • ADD COLUMN and DROP COLUMN are allowed except adding columns of small serial types ( SMALLSERIAL, SERIAL2, SERIAL and SERIAL4). SERIAL8 and BIGSERIAL are supported, however.

    • Adding or dropping table-wide unique constraints and checks is allowed. For global tables dropping primary key constraint or dropping columns, participating in primary key, is forbidden.

    • Adding foreign keys between sharded tables is possible only when they are colocated and a foreign key references tuples, which are stored in the same replication group. A foreign key on a global table can reference only another global table. A foreign key on a sharded table can reference a colocated sharded table or a global table.

    • SET/DROP NOT NULL is allowed.

    • Setting storage options is allowed for global tables.

6.5.3.  CREATE TABLE Limitations

  • All limitations for ALTER TABLE apply.

  • Using of non-builtin types (types with OIDs >= 10000) or non-base types or arrays are not allowed in 'distributed_by' columns.

  • Only the global user can create sharded or global table.

  • Using of DEFERRABLE constraints for global tables is not allowed.

  • A temporary table cannot be created as sharded or global.

6.5.4.  DROP TABLE Limitations

  • Sharded or global tables and local tables cannot be dropped in the same statement.

  • Partitions of a sharded table cannot be dropped.

6.5.5.  CREATE INDEX CONCURRENTLY Limitations

  • CREATE INDEX CONCURRENTLY is a non-transactional command. If a problem arises while building index on sharded or global tabe, such as network failure, deadlock or a uniqueness violation in a unique index, the CREATE INDEX CONCURRENTLY will partially fail, but can leave behind valid or invalid indexes on Shardman cluster nodes. Also an index can be completely missing on some nodes. In the later case DROP INDEX will fail to drop the index. The recommended way to remove such index cluster-wide is to use DROP INDEX IF EXISTS command. Note that DROP INDEX CONCURRENTLY is not supported on sharded tables, so this operation should be better performed in a maintenance window.

6.5.6.  UPDATE Limitations

  • UPDATE of a sharded table is executed as a series of usual UPDATEs if it doesn't move data between partitions or subpartitions. Otherwise it is executed internally as DELETE from one partition and INSERT into another (so called target partition). If a partition where UPDATE INSERTs data, is going to be UPDATED in the same statement, an error will be raised. In practice this means that if UPDATE moves data between partitions, you should explicitly exclude target partition from updating in WHERE clause of the statement.

6.5.7.  INSERT ON CONFLICT DO UPDATE Limitations

  • INSERT INTO table_name... ON CONFLICT [conflict_target] conflict_action [WHERE condition] command is not supported on foreign tables when conflict_target is DO UPDATE. For sharded tables it is supported if expressions in SET and WHERE clause can be safely deparsed (currently deparsing of sqlvalue-functions, parameters and subqueries inside these clauses is not supported) and a non-partial unique index, containing only table columns (not coulmn-based expressions), corresponds to conflict_target expression. This is usually the case with table's primary key.

6.5.8. Limitations of Managing Global Roles

  • Global users can be created only by user with CREATEROLE permission on all cluster nodes.

  • Global roles cannot be renamed.

  • Global and local roles cannot be dropped in the same statement.

  • GRANT to a local and global role in the same statement is prohibited.

  • REVOKE from a local and global role in the same statement is prohibited.

6.5.9.  ALTER SCHEMA Limitations

  • Schemas containing global or sharded tables cannot be renamed. Shardman service schemas (shardman and shardman_internal) cannot be renamed or dropped.

6.5.10.  DROP SERVER Limitations

  • Shardman cluster servers cannot be dropped. Use Shardman tools to remove servers from the cluster.

6.5.11.  CREATE COLLATION Limitations

  • If you use custom collation, all servers must have same version of icu. Otherwise results of queries on sharded tables may be incorrect.

6.5.12. Other Limitations

Prev Up Next
CREATE TABLESPACE Home 6.6. Shardman CLI Reference