To ensure consistency of a sharded database, Shardman imposes some restrictions on SQL commands executed.
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.
ADD COLUMN and DROP COLUMN
are allowed except adding columns of
serial types.
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.
SET/DROP NOT NULL
is allowed.
Setting storage options is allowed for global tables.
CREATE TABLE
Limitations
All limitations for ALTER TABLE apply.
A temporary table cannot be created as sharded.
DROP TABLE
LimitationsSharded tables and local tables cannot be dropped in the same statement.
Partitions of a sharded table cannot be dropped.
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.
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.
ALTER SCHEMA
Limitations
Schemas containing global or sharded tables cannot be renamed. Shardman service schemas
(shardman and shardman_internal) cannot be renamed or dropped.
DROP SERVER
LimitationsShardman cluster servers cannot be dropped. Use Shardman tools to remove servers from the cluster.
DROP TYPE CASCADE
is prohibited if it affects types used in global or sharded tables.