Gestion des objets volumineux avec le module lo

Le module lo (extension) est destiné aux utilisateurs et aux développeurs de base de données qui travaillent avec des bases de données PostgreSQL via des pilotes JDBC ou ODBC. JDBC et ODBC s'attendent à ce que la base de données gère la suppression des objets volumineux lorsque les références à ces derniers changent. Cependant, PostgreSQL ne fonctionne pas de cette façon. PostgreSQL ne suppose pas qu'un objet doit être supprimé lorsque sa référence change. Les objets restent donc sur le disque, sans référence. L'extension lo inclut une fonction qui se déclenche en cas de changement de référence afin de supprimer des objets si nécessaire.

Astuce

Pour déterminer si votre base de données peut bénéficier de l'extension lo, utilisez l'utilitaire vacuumlo pour vérifier la présence d'objets volumineux orphelins. Pour obtenir le nombre d'objets volumineux orphelins sans effectuer aucune action, exécutez l'utilitaire avec l'option -n (no-op). Pour savoir comment procéder, consultez vacuumlo utility.

Le module lo est disponible pour Aurora PostgreSQL 13.7, 12.11, 11.16, 10.21 et versions mineures ultérieures.

Pour installer le module (extension), vous avez besoin de privilèges rds_superuser. L'installation de l'extension lo ajoute ce qui suit à votre base de données :

• lo : type de données d'objet volumineux (lo) que vous pouvez utiliser pour les objets volumineux binaires (BLOB) et d'autres objets volumineux. Le type de données lo est un domaine du type de données oid. En d'autres termes, il s'agit d'un identificateur d'objet avec des contraintes facultatives. Pour en savoir plus, consultez Identificateurs d'objet dans la documentation PostgreSQL. En termes simples, vous pouvez utiliser le type de données lo pour distinguer les colonnes de votre base de données contenant des références d'objets volumineux des autres identificateurs d'objet (OID).

• lo_manage : fonction que vous pouvez utiliser dans les déclencheurs sur les colonnes de la table contenant des références d'objets volumineux. Lorsque vous supprimez ou modifiez une valeur qui fait référence à un objet volumineux, le déclencheur dissocie l'objet (lo_unlink) de sa référence. Utilisez le déclencheur sur une colonne uniquement si la colonne est la seule référence de base de données à l'objet volumineux.

Pour en savoir plus sur le module d'objets volumineux, consultez lo dans la documentation PostgreSQL.

Installation de l'extension lo

Avant d'installer l'extension lo, assurez-vous que vous disposez de privilèges rds_superuser.

Pour installer l'extension Io

1. Utilisez psql pour vous connecter à l'instance de base de données principale de votre cluster de bases de données Aurora PostgreSQL.

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

   Lorsque vous y êtes invité, saisissez votre mot de passe. Le client psql se connecte à la base de données de connexions administratives par défaut, postgres=>, et l'affiche sous forme d'invite.

2. Installez l'extension comme suit.

   postgres=> CREATE EXTENSION lo;
   CREATE EXTENSION

Vous pouvez désormais utiliser le type de données lo pour définir des colonnes dans vos tables. Vous pouvez, par exemple, créer une table (images) qui contient des données d'image tramée. Vous pouvez utiliser le type de données lo pour une colonne raster, comme illustré dans l'exemple suivant, qui crée une table.

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

Utilisation de la fonction de déclenchement lo_manage pour supprimer des objets

Vous pouvez utiliser la fonction lo_manage dans un déclencheur sur une colonne lo ou d'autres colonnes d'objets volumineux pour les nettoyer (et éviter les objets orphelins) lorsque lo est mis à jour ou supprimé.

Pour configurer des déclencheurs sur les colonnes qui font référence à des objets volumineux

• Effectuez l'une des actions suivantes :

  • Créez un déclencheur BEFORE UPDATE OR DELETE sur chaque colonne de sorte qu'il contienne des références uniques à des objets volumineux, en utilisant le nom de colonne comme argument.

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

  • Appliquez un déclencheur uniquement lorsque la colonne est en cours de mise à jour.

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

La fonction de déclenchement lo_manage fonctionne uniquement dans le contexte de l'insertion ou de la suppression de données de colonne, en fonction de la façon dont vous définissez le déclencheur. Elle n'a aucun effet lorsque vous effectuez une opération DROP ou TRUNCATE sur une base de données. Cela signifie que vous devriez supprimer les colonnes d'objets de n'importe quelle table avant de procéder à la suppression de la base de données, pour éviter la création d'objets orphelins.

Par exemple, supposons que vous souhaitiez supprimer la base de données contenant la table images. Vous supprimez la colonne comme suit.

postgres=> DELETE FROM images COLUMN raster

En supposant que la fonction lo_manage est définie sur cette colonne pour gérer les suppressions, vous pouvez maintenant supprimer la table en toute sécurité.

Utilisation de l'utilitaire vacuumlo

L'utilitaire vacuumlo identifie les objets volumineux orphelins et peut les supprimer des bases de données. Cet utilitaire est disponible depuis PostgreSQL 9.1.24. Si les utilisateurs de base de données travaillent régulièrement avec des objets volumineux, nous vous recommandons d'exécuter vacuumlo occasionnellement pour nettoyer les objets volumineux orphelins.

Avant d'installer l'extension lo, vous pouvez utiliser vacuumlo pour déterminer si votre cluster de bases de données Aurora PostgreSQL peut en bénéficier. Pour ce faire, exécutez vacuumlo avec l'option -n (no-op) pour afficher les objets qui seraient supprimés, comme illustré dans l'exemple suivant :

$ 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".

Comme indiqué dans la sortie, les objets volumineux orphelins ne posent aucun problème pour cette base de données.

Pour plus d'informations sur cet utilitaire, consultez vacuumlo dans la documentation PostgreSQL.