Como lidar com dados duplicados no PostgreSQL usando a coluna de sistema ctid

Como lidar com dados duplicados no PostgreSQL usando a coluna de sistema ctid

Você que utiliza PostgreSQL, já deve ter se deparado com o problema dos dados duplicados. É possível que em uma tabela existam campos que contenham valores repetidos e sua necessidade seja tornar esses registros únicos.
Como proceder com valores repetidos sem eliminar todos eles? Seria possível deixar somente o mais atual armazenado? É o que veremos a seguir.

Coluna de sistema ctid

Toda tabela tem algumas colunas implicitamente definidas pelo sistema e cujos nomes são palavras-chave reservadas.
Atualmente as colunas de sistema [1] são: tableoid, xmin, cmin, xmax, cmax e ctid. Cada uma possui os metadados da tabela à qual pertencem.

A coluna de sistema ctid tem por finalidade armazenar a versão da localização física da linha. Essa versão pode mudar caso a linha seja atualizada (UPDATE) ou a tabela passe por um VACUUM FULL (esvaziamento completo).

A coluna ctid é do tipo tid [2], que significa tuple identifier (ou row identifier), que é um par (número do bloco, índice de tupla dentro do bloco) que identifica a localização física da linha dentro da tabela. Essa coluna tem sempre seu valor único na tabela, sendo assim, quando há linhas com valores repetidos esta coluna pode ser utilizada como critério para eliminação desses.

Criação de tabela de teste para ctid:

1CREATE TABLE tb_teste_ctid (
2    col1 int,
3    col2 text);

Popular a tabela:

1INSERT INTO tb_teste_ctid VALUES
2    (1, 'foo'),
3    (2, 'bar'),
4    (3, 'baz');

Verificar a tabela:

1SELECT ctid, * FROM tb_teste_ctid;
 ctid  | col1 | col2 
-------+------+------
 (0,1) |    1 | foo
 (0,2) |    2 | bar
 (0,3) |    3 | baz

Atualizar uma linha:

1UPDATE tb_teste_ctid
2    SET col2 = 'spam'
3    WHERE col1 = 1;

Verificando a tabela após o UPDATE:

1SELECT ctid, * FROM tb_teste_ctid;
 ctid | col1 | col2 
-------+------+------
(0,2) | 2 | bar
(0,3) | 3 | baz
(0,4) | 1 | spam

Nota-se que o registro atualizado teve seu ctid alterado também.

Um simples teste de VACUUM:

1VACUUM FULL tb_teste_ctid;

Checando novamente a tabela (pós VACUUM FULL):

1SELECT ctid, * FROM tb_teste_ctid;
ctid   | col1 | col2 
-------+------+------
(0,1)  | 2    | bar
(0,2)  | 3    | baz
(0,3)  | 1    | spam

Nota-se agora que após o processo de VACUUM FULL os ctids foram renovados, com os valores como
se a tabela tivesse recebido os dados pela primeira vez.

Atualizar um registro com a cláusula RETURNING:

1UPDATE tb_teste_ctid
2    SET col2 = 'eggs'
3    WHERE col1 = 1
4    RETURNING ctid;
ctid 
-------
(0,4)

Verificando a tabela novamente após o UPDATE:

1SELECT ctid, * FROM tb_teste_ctid;

 ctid  | col1 | col2 
-------+------+------
 (0,1) |    2 | bar
 (0,2) |    3 | baz
 (0,4) |    1 | eggs

E assim podemos entender o papel da coluna de sistema ctid, vemos na prática como ela controla a versão de cada registro.

Eliminação de valores repetidos com ctid

Se uma tabela tem valores repetidos em um campo e desejamos tornar esse campo único posteriormente, também temos outras ações a tomar.
Vale lembrar que um campo PRIMARY KEY também é único.

Foi decidido que os valores repetidos desse campo serão eliminados.
Agora é necessário estabelecer um critério para decidir dentre esses valores repetidos qual permanecerá.
No caso a seguir o critério é a linha mais atual, ou seja, a linha que tiver o maior valor de ctid.

Criação da tabela de teste:

1CREATE TABLE tb_foo(
2    id_ int-- Futura PRIMARY KEY
3    letter char(1)
4);

Popular a tabela:

1INSERT INTO tb_foo (id_, letter)
2    SELECT generate_series(1, 10), 'a';

Verificar a tabela:

1SELECT id_, letter FROM tb_foo;
 id_ | letter 
-----+--------
   1 | a
   2 | a
   3 | a
   4 | a
   5 | a
   6 | a
   7 | a
   8 | a
   9 | a
  10 | a

Inserir mais 3 registros:

1INSERT INTO tb_foo (id_, letter) SELECT generate_series(1, 3), 'b';

Verificar os itens duplicados:

1SELECT id_, letter FROM tb_foo WHERE id_ <= 3;
 id_ | letter  
-----+--------
   1 | a
   2 | a
   3 | a
   1 | b
   2 | b
   3 | b

Há duplicidades de valores no campo id_ da tabela…

Vamos realizar uma tentativa de fazer com que o campo id_ seja uma chave primária:

1ALTER TABLE tb_foo ADD CONSTRAINT tb_foo_pkey PRIMARY KEY (id_);
ERROR:  could not create unique index "tb_foo_pkey"
DETAIL:  Key (id_)=(3) is duplicated.

Utilizando CTE e funções de janela descobrimos quais valores repetidos manteremos:

01WITH t AS (
02SELECT
03    id_,
04    count(id_) OVER (PARTITION BY id_) AS count_id,  -- Count
05    ctid,
06    max(ctid) OVER (PARTITION BY id_) AS max_ctid  -- Most current ctid
07     
08    FROM tb_foo
09)
10SELECT
11    t.id_,
12    t.max_ctid
13    FROM t
14    WHERE t.count_id > 1  -- Filters which values repeat
15    GROUP by id_, max_ctid;
 id_ | max_ctid 
-----+----------
   3 | (0,13)
   1 | (0,11)
   2 | (0,12)

O próximo passo é limpar a tabela deixando somente valores únicos para o campo id_ removendo as linhas mais antigas:

01WITH
02t1 AS (
03SELECT
04    id_,
05    count(id_) OVER (PARTITION BY id_) AS count_id,
06    ctid,
07    max(ctid) OVER (PARTITION BY id_) AS max_ctid
08     
09    FROM tb_foo
10),
11 
12t2 AS -- Tabela virtual que filtra valores repetidos que permanecerão
13SELECT t1.id_, t1.max_ctid
14    FROM t1
15    WHERE t1.count_id > 1
16    GROUP by t1.id_, t1.max_ctid)
17 
18DELETE  -- DELETE com JOIN
19    FROM tb_foo AS f
20    USING t2
21    WHERE
22        f.id_ = t2.id_ AND  -- tb_foo tenha id_ igual a t2 (valores repetidos)
23        f.ctid < t2.max_ctid;  -- ctid seja menor quer o máximo (mais atual)

Verifique agora os valores da tabela sem os registros duplicados no campo id_:

1SELECT id_, letter FROM tb_foo;
 id_ | letter 
-----+--------
   4 | a
   5 | a
   6 | a
   7 | a
   8 | a
   9 | a
  10 | a
   1 | b
   2 | b
   3 | b        

Agora é possível alterar a tabela para apontar o campo id_ como PRIMARY KEY:

1ALTER TABLE tb_foo ADD CONSTRAINT tb_foo_pkey PRIMARY KEY (id_);

Conclusão

É importante conhecer a respeito de colunas de sistema no PostgreSQL, pois em uma situação como essa, com dados repetidos, temos um critério de unicidade onde deve ser mantido somente o que realmente for necessário.
Além de tudo utilizarmos recursos interessantes do PostgreSQL como CTE (Common Table Expression) [3] e Funções de Janela (Window Functions).

Esperamos que ajude o leitor com problemas semelhantes!

Até mais!

Mais informações

[1] Colunas de sistema: https://www.postgresql.org/docs/current/ddl-system-columns.html

[2] Tipos oid: https://www.postgresql.org/docs/current/datatype-oid.html

[3] CTE: https://www.postgresql.org/docs/current/queries-with.html

[4] Funções de Janela: https://www.postgresql.org/docs/current/tutorial-window.html

 

Líder em Treinamento e serviços de Consultoria, Suporte e Implantação para o mundo open source. Conheça nossas soluções:

CURSOSCONSULTORIA

Anterior Guia completo para instalação do Gitea: Ferramenta open source de gerenciamento de código-fonte
Próxima Tendências de TI para 2021: Prepare sua empresa para o futuro

About author

Juliano Atanazio
Juliano Atanazio 2 posts

Graduado em "Informática para Gestão de Negócios" pela FATEC Zona Sul. Atua com Linux e Software Livre desde 2006. Hoje atua principalmente como DBA PostgreSQL e programador Python.

View all posts by this author →

Você pode gostar também

Banco de Dados

Entenda o Snapshot: a ferramenta de backup que não para seu serviço

Atualmente, o termo snapshot tem se popularizado por sua utilidade nas mais variadas Clouds. Este snapshot em questão funciona na sua casa, na sua infraestrutura local e até mesmo na

Banco de Dados

Pane em sistema apaga 16.500 processos do TCE-AM: 4Linux é contratada para recuperação

O TCE-AM (Tribunal de Contas do Estado do Amazonas) teve 16.500 processos apagados indevidamente devido a uma pane nos sistemas e-Contas e Spede (Sistema de Processos e Documentos Eletrônicos) depois

Banco de Dados

Melhore a performance do seu banco Postgres com o PGbadger

O seu banco Postgres está lento? Você já alterou diversas configurações e mesmo assim não conseguiu identificar os problemas de performance? Pois bem, há uma luz no fim do tunel!