Skip to content

Latest commit

 

History

History
73 lines (50 loc) · 1.98 KB

sql-statement-drop-placement-policy.md

File metadata and controls

73 lines (50 loc) · 1.98 KB
Raw
title summary
DROP PLACEMENT POLICY
The usage of ALTER PLACEMENT POLICY in TiDB.

DROP PLACEMENT POLICY

DROP PLACEMENT POLICY is used to drop a previously created placement policy.

Note:

This feature is not available on TiDB Serverless clusters.

Synopsis

DropPolicyStmt ::=
    "DROP" "PLACEMENT" "POLICY" IfExists PolicyName

PolicyName ::=
    Identifier

Examples

Placement policies can only be dropped when they are not referenced by any tables or partitions.

{{< copyable "sql" >}}

CREATE PLACEMENT POLICY p1 FOLLOWERS=4;
CREATE TABLE t1 (a INT PRIMARY KEY) PLACEMENT POLICY=p1;
DROP PLACEMENT POLICY p1;  -- This statement fails because the placement policy p1 is referenced.

-- Finds which tables and partitions reference the placement policy.
SELECT table_schema, table_name FROM information_schema.tables WHERE tidb_placement_policy_name='p1';
SELECT table_schema, table_name FROM information_schema.partitions WHERE tidb_placement_policy_name='p1';

ALTER TABLE t1 PLACEMENT POLICY=default;  -- Removes the placement policy from t1.
DROP PLACEMENT POLICY p1;  -- Succeeds.
Query OK, 0 rows affected (0.10 sec)

Query OK, 0 rows affected (0.11 sec)

ERROR 8241 (HY000): Placement policy 'p1' is still in use

+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| test         | t1         |
+--------------+------------+
1 row in set (0.00 sec)

Empty set (0.01 sec)

Query OK, 0 rows affected (0.08 sec)

Query OK, 0 rows affected (0.21 sec)

MySQL compatibility

This statement is a TiDB extension to MySQL syntax.

See also