como usar alter table…drop partition para excluir partições e dados - PolarDB: Central de Documentação do Alibaba Cloud

Remove permanentemente uma partição e todos os seus dados de uma tabela particionada. Esta operação é irreversível — os dados excluídos não podem ser recuperados.

Aviso

Faça backup dos dados da partição antes de executar este comando. Após a exclusão, os dados não podem ser restaurados.

Se você precisar apenas limpar os dados sem remover a definição da partição, use ALTER TABLE TRUNCATE PARTITION. O comando TRUNCATE PARTITION remove todas as linhas da partição, mas mantém a estrutura da partição intacta e não elimina os dados permanentemente como faz o DROP PARTITION.

Sintaxe

ALTER TABLE [schema.]table_name DROP PARTITION partition_name;

Parâmetros

Parâmetro	Obrigatório	Descrição	Exemplo
`table_name`	Sim	Nome da tabela particionada da qual se deseja excluir uma partição	`sales_records`
`partition_name`	Sim	Nome da partição a ser excluída	`P_2023_Q1`

Observações de uso

Apenas o proprietário da tabela ou um usuário com privilégios pode executar este comando.
Uma tabela particionada deve ter pelo menos uma partição. Não é permitido excluir a última partição restante.
O comando DROP PARTITION adquire um AccessExclusiveLock no nível da tabela, que bloqueia todas as operações de Linguagem de Manipulação de Dados (DML) e a maioria das operações de Linguagem de Definição de Dados (DDL) na tabela. Execute este comando fora do horário de pico.

Exemplo

O exemplo a seguir cria uma tabela particionada por intervalo, executa verificações prévias, exclui uma partição e verifica o resultado.

Configurar a tabela de exemplo

-- Create a table partitioned by sales date range.
CREATE TABLE sales_records (
    sale_id    INT NOT NULL,
    product_id INT NOT NULL,
    sale_date  DATE NOT NULL
)
PARTITION BY RANGE (sale_date) (
    PARTITION P_2023_Q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
    PARTITION P_2023_Q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD'))
);

-- Create a globally unique index.
CREATE UNIQUE INDEX idx_sale_id_global ON sales_records(sale_id) GLOBAL;

-- Insert data.
INSERT INTO sales_records VALUES (1, 101, TO_DATE('2023-01-15', 'YYYY-MM-DD')); -- Q1
INSERT INTO sales_records VALUES (2, 102, TO_DATE('2023-04-20', 'YYYY-MM-DD')); -- Q2

Verificações prévias

Antes de excluir uma partição, execute estas verificações para evitar perda de dados e interrupções de serviço.

Verifique se a partição de destino existe.

-- List all partitions for the table.
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_RECORDS';
-- Confirm that P_2023_Q1 appears in the result.

Confirme se a partição contém os dados que você espera excluir.

-- Preview the data in the partition before dropping it.
SELECT * FROM sales_records WHERE sale_date < TO_DATE('2023-04-01', 'YYYY-MM-DD');
-- Expected result: rows for Q1 (for example, sale_id = 1).

(Opcional) Faça backup da tabela ou do banco de dados. Consulte Métodos de backup.
Verifique se há locks ativos na tabela. Se transações de longa duração estiverem acessando a tabela, a operação de exclusão será bloqueada até que elas sejam concluídas.
```
-- Check for active locks on the table.
SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE relation = 'SALES_RECORDS'::regclass;
```

Excluir a partição

Após concluir as verificações prévias, execute o comando fora do horário de pico.

ALTER TABLE sales_records DROP PARTITION P_2023_Q1;

Verificar o resultado

Confirme se a partição foi removida.

SELECT partition_name
FROM ALL_TAB_PARTITIONS
WHERE table_name = 'SALES_RECORDS' AND partition_name = 'P_2023_Q1';
-- Expected result: empty result set.

Confirme se os dados foram excluídos.

SELECT *
FROM sales_records
WHERE sale_date < TO_DATE('2023-04-01', 'YYYY-MM-DD');
-- Expected result: empty result set.

Atualize as estatísticas da tabela para que o otimizador de consultas gere planos de execução precisos.
```
ANALYZE SALES_RECORDS;
```

Perguntas frequentes

Por que recebo partition "..." of relation "..." does not exist?

O nome da partição diferencia maiúsculas de minúsculas. Consulte USER_TAB_PARTITIONS para encontrar o nome exato e, se necessário, use aspas duplas para garantir a correspondência exata.

Por que recebo permission denied for table ... ou must be owner of table ...?

O usuário atual não tem permissões suficientes. Alterne para o proprietário da tabela ou para um usuário com privilégios.

Por que recebo cannot drop partition due to dependent objects?

Outros objetos do banco de dados — como restrições de chave estrangeira ou views — dependem desta partição. O comportamento padrão RESTRICT impede a exclusão. Identifique e remova esses objetos dependentes primeiro e tente novamente.

Por que a operação está demorando muito ou parece travada?

O comando DROP PARTITION adquire um AccessExclusiveLock. Se outra transação ativa de longa duração estiver mantendo um lock conflitante, a operação aguardará. Use a consulta a seguir para identificar a sessão bloqueadora pelo seu PID e decida se deve aguardar ou encerrar a transação bloqueadora.

-- Query user information of the blocking thread based on the PID.
SELECT
    pid,
    usename AS username,           -- Database username
    application_name,              -- Application name (such as JDBC, psql)
    client_addr,                   -- Client IP address
    client_hostname,               -- Client hostname
    client_port,                   -- Client port
    backend_start,                 -- Time when the connection started
    xact_start,                    -- Time when the transaction started
    query_start,                   -- Time when the current query started
    state_change,                  -- Time when the status last changed
    state,                         -- Status (active, idle, idle in transaction)
    wait_event_type,               -- Wait event type (such as Lock, IO)
    wait_event,                    -- Specific wait event
    query                          -- Currently running or most recently run SQL statement
FROM
    pg_stat_activity
WHERE
    pid in 'blocking_pid';

Próximos passos

ALTER TABLE ADD PARTITION: Adiciona uma nova partição a uma tabela particionada.
ALTER TABLE TRUNCATE PARTITION: Limpa todos os dados de uma partição mantendo a definição da partição.
ALTER TABLE SPLIT PARTITION: Divide uma partição em duas.