Zerando Large Objects numa base PostgreSQL

Se tem uma coisa que eu realmente não recomendo é sair guardando zilhões de imagens dentro do banco de dados. Uma vez ou outra vá lá… se as imagens forem poucas e pequenas. Bom, mas sabe aquela coisa desenvolvida há décadas, com zilhões de otimizações, pesquisas e intermináveis discussões para se chegar num produto ótimo  chamado Sistema de Arquivos? Então, por algum motivo bizarro algumas pessoas realmente acham que o seu banco de dados é um lugar melhor que o Sistema de arquivos para guardar…. arquivos!

Bom, estes dias eu precisei lidar com um destes sistemas “inteligentes”, desenvolvido por pessoas realmente “inteligentes” que guarda milhões de imagens no pobre do PostgreSQL. Não se engane, se fosse no Oracle ou no SQL Server, você estaria no sal do mesmo jeito. Em determinado momento, o sistema que estava em fase de rollout precisou parar tudo e começar de novo. Na verdade eu estou adiantando um pouco a história. Houveram capítulos de terror antes. Eles utilizam os Large Objects e excluem as linhas das tabelas e esquecem de apagar os arquivos da tabela pg_largeobjects. A solução seria rodar o vacuumlo, que faz este tipo de limpeza para nós. Veja a situação:

• A base tem uns 150GB;
• 1GB de dados em tabelas normais;
• Aproximadamente 75 milhões de arquivos na pg_largeobjects em 149GB;
• Previsão de excluir 80% dos arquivos, ou seja: 60 milhões de arquivos em 119GB.

Resultado: rodamos o vacuumlo por 12 horas e…. continuou rodando….

Bom, como todo mundo tem chefe a nova solicitação seria zerar TODOS os arquivos, e rápido. Claro, um TRUNCATE é muito mais fácil que um DELETE. Ops, Large Object não faz DELETE. Para remover um arquivo do tipo Large Object você tem de fazer um lo_unlink.

Ok, você poderia pensar que agora é só dar um simples TRUNCATE na tabela pg_largeobjects e tudo estaria resolvido, certo? Então veja isso:

teste=# TRUNCATE pg_largeobject;
ERROR:  permission denied: "pg_largeobject" is a system catalog

Ah… claro, não é possível sair truncando tabelas de sistema assim numa boa. Bom, existe um jeitinho de se fazer isso. Você pode inicializar o postgres com o parâmetro allow_system_table_mods = TRUE no postgresql.conf. Claro, você tem de reiniciar o postgres para fazer isso.

teste=# TRUNCATE pg_largeobject;
TRUNCATE TABLE
teste=# SELECT * from pg_largeobject;
 loid | pageno | data
------+--------+------
(0 rows)

Depois disso, melhor você remover o parâmetro do seu postgresql.conf e reiniciar o servidor novamente. Mas…. um requisito importante apareceu. Eu não poderia reiniciar o Postgres. Outra base no mesmo cluster estava em produção… e a próxima janela para reiniciar o Postgres estava muito longe, isto poderia atrasar todo o projeto em uma semana. Qual foi a solução? Usar o velho e bom pg_dump. A questão é que se você especificar tabelas e/ou esquemas no seu dump (--schema ou --table), o nosso amigo pg_dump NÃO traz os dados da pg_largeobjects. Como os dados representam apenas 1GB, a solução foi considerada rápida o suficiente para nós:

# Gerar dump sem os Large Objects
pg_dump -Fc -n public teste > teste_sem_lo.dmp

# Apagar base do SQN
dropdb teste

# Recriar a base zerada
createdb teste

# Importar a base novamente
pg_restore -d teste teste_sem_lo.dmp

# Entrar no psql
psql teste

Encontrando as tabelas que referenciam Large Objects

Isto realmente funcionou bem. Agora faltava truncar as tabelas que usam OID ou LO, que são as colunas que apontam para os nossos Large Objects. Para descobrir quais são estas tabelas, este SELECT pode lhe ajudar:

SELECT nspname AS esquema, relname AS tabela, attname AS coluna
FROM
             pg_type t
        JOIN pg_attribute a  ON a.atttypid = t.oid
        JOIN pg_class c      ON a.attrelid = c.oid
        JOIN pg_namespace n  ON c.relnamespace = n.oid
WHERE
    typname IN  ('oid','lo') AND
    attname NOT IN ('oid', 'tableoid') AND
    nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema');

Segue um exemplo do que você pode encontrar. Primeiro vamos adicionar algumas tabelas contendo OID e LO:

CREATE TABLE teste_lo (aaa varchar, bbb oid, ccc lo);
CREATE TABLE teste2_lo (aaa varchar, ccc lo);
CREATE TABLE teste3_lo (aaa varchar, bbb oid);

Depois vemos o resultado da consulta:

 esquema |  tabela   |  coluna
---------+-----------+----------
 public  | teste_lo  | bbb
 public  | teste_lo  | ccc
 public  | teste_lo2 | ccc
 public  | teste_lo3 | bbb
(7 rows)

Com isso em mãos, fizemos um TRUNCATE nas tabelas restantes e zeramos finalmente todas os arquivos binários e suas referências na base.