Gerenciar objetos grandes com o módulo lo

O módulo lo (extensão) é para usuários de banco de dados e desenvolvedores que trabalham com bancos de dados PostgreSQL por meio de drivers JDBC ou ODBC. Tanto no caso do JDBC quanto no caso do ODBC, é esperado que o banco de dados processe a exclusão de objetos grandes quando as referências a eles mudam. No entanto, o PostgreSQL não funciona dessa maneira. No PostgreSQL, não se espera que um objeto seja excluído quando sua referência é alterada. O resultado é que os objetos permanecem no disco, sem referência. A extensão lo inclui uma função usada para acionar alterações de referência para excluir objetos, se necessário.

dica

Para determinar se seu banco de dados pode se beneficiar da extensão lo, use o utilitário vacuumlo para verificar se há objetos grandes órfãos. Para obter contagens de objetos grandes órfãos sem realizar nenhuma ação, execute o utilitário com a opção -n (no-op). Para saber como, consulte vacuumlo utility a seguir.

O módulo lo está disponível para Aurora PostgreSQL 13.7, 12.11, 11.16, 10.21 e versões secundárias superiores.

Para instalar o módulo (extensão), você precisa de privilégios rds_superuser. Instalar a extensão lo adiciona o seguinte ao seu banco de dados:

• lo: é um tipo de dados de objeto grande (lo) que você pode usar para objetos grandes binários (BLOBs) e outros objetos grandes. O tipo de dados lo é um domínio do tipo de dados oid. Em outras palavras, é um identificador de objeto com restrições opcionais. Para saber mais, consulte Identificadores de objeto na documentação do PostgreSQL. Em termos simples, você pode usar o tipo de dados lo para distinguir suas colunas de banco de dados que contêm referências de objetos grandes de outros identificadores de objeto (OIDs).

• lo_manage: é uma função que você pode usar em gatilhos em colunas de tabela que contêm referências a objetos grandes. Sempre que você excluir ou modificar um valor que faça referência a um objeto grande, o gatilho desvincula o objeto (lo_unlink) de sua referência. Use o gatilho em uma coluna somente se a coluna for a única referência de banco de dados ao objeto grande.

Para obter mais informações sobre o módulo de objetos grandes, consulte lo na documentação do PostgreSQL.

Instalar a extensão lo

Antes de instalar a extensão lo, verifique se você tem privilégios rds_superuser.

Como instalar a extensão lo

1. Use o psql para conectar-se à instância de banco de dados primária do cluster de banco de dados Aurora PostgreSQL.

   psql --host=your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password

   Insira sua senha quando for solicitado. O cliente psql conecta-se e exibe o banco de dados de conexão administrativa padrão, postgres=>, como o prompt.

2. Instale a extensão da forma a seguir.

   postgres=> CREATE EXTENSION lo;
   CREATE EXTENSION

Agora é possível usar o tipo de dados lo para definir colunas em suas tabelas. Por exemplo, você pode criar uma tabela (images) que contém dados de imagem raster. Você pode usar o tipo de dados lo para uma coluna raster, conforme mostrado no exemplo a seguir, que cria uma tabela.

postgres=> CREATE TABLE images (image_name text, raster lo);

Usar a função de gatilho lo_manage para excluir objetos

É possível usar a função lo_manage em um gatilho em uma lo ou outras colunas de objetos grandes para limpar (e evitar objetos órfãos) quando a lo é atualizada ou excluída.

Como configurar gatilhos em colunas que fazem referência a objetos grandes

• Faça um dos seguintes procedimentos:

  • Crie um gatilho BEFORE UPDATE OR DELETE em cada coluna para conter referências exclusivas a objetos grandes, usando o nome da coluna como argumento.

    postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON images
        FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);

  • Aplique um gatilho somente quando a coluna estiver sendo atualizada.

    postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OF images
        FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);

A função de gatilho lo_manage funciona apenas no contexto de inserção ou exclusão de dados de colunas, dependendo de como você define o gatilho. Isso não tem efeito quando você executa uma operação DROP ou TRUNCATEem um banco de dados. Isso significa que é necessário excluir colunas de objeto de qualquer tabela antes de soltá-las, para evitar a criação de objetos órfãos.

Por exemplo, suponha que você queira descartar o banco de dados que contém a tabela images. Exclua a coluna da maneira a seguir.

postgres=> DELETE FROM images COLUMN raster

Supondo que a função lo_manage seja definida nessa coluna para lidar com exclusões, agora você pode descartar a tabela com segurança.

Usar o utilitário vacuumlo

O utilitário vacuumlo identifica e pode remover objetos grandes órfãos dos bancos de dados. Esse utilitário está disponível desde o PostgreSQL 9.1.24. Se os usuários do banco de dados trabalham rotineiramente com objetos grandes, recomendamos executar o vacuumlo ocasionalmente para limpar objetos grandes órfãos.

Antes de instalar a extensão lo, você pode usar o vacuumlo para avaliar se o cluster de banco de dados do Aurora PostgreSQL pode se beneficiar. Para isso, execute vacuumlo com a opção -n (no-op) para mostrar o que seria removido, conforme mostrado no seguinte:

$ vacuumlo -v -n -h your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com -p 5433 -U postgres docs-lab-spatial-db
Password:*****
Connected to database "docs-lab-spatial-db"
Test run: no large objects will be removed!
Would remove 0 large objects from database "docs-lab-spatial-db".

Como mostra a saída, objetos grandes órfãos não são um problema para esse banco de dados específico.

Para obter mais informações sobre esse utilitário, consulte vacuumlo na documentação do PostgreSQL.