Gestione di oggetti di grandi dimensioni con il modulo lo

Il modulo lo (estensione) è per utenti di database e sviluppatori che utilizzano database PostgreSQL tramite driver JDBC o ODBC. JDBC e ODBC presumono entrambi che il database gestisca l'eliminazione di oggetti di grandi dimensioni quando i riferimenti ad essi cambiano. Tuttavia, PostgreSQL non funziona in questo modo. PostgreSQL non ipotizza che un oggetto venga eliminato quando il suo riferimento cambia. Il risultato è che gli oggetti rimangono su disco, senza riferimenti. L'estensione lo include una funzione utilizzata per attivare le modifiche dei riferimenti per eliminare gli oggetti se necessario.

Suggerimento

Per determinare se il database può sfruttare l'estensione lo, utilizza l'utilità vacuumlo per verificare la presenza di oggetti orfani di grandi dimensioni. Per ottenere il conteggio di oggetti orfani di grandi dimensioni senza eseguire alcuna azione, esegui l'utilità con l'opzione -n(no-op). Per scoprire come, consulta vacuumlo utility seguente.

Il modulo lo è disponibile per Aurora PostgreSQL 13.7, 12.11, 11.16, 10.21 e versioni secondarie successive.

Per installare il modulo (estensione), sono necessari privilegi rds_superuser. L'installazione dell'estensione lo aggiunge quanto segue al database:

• lo – Questo è un tipo di dati (lo) oggetto di grandi dimensioni che puoi utilizzare per oggetti binari di grandi dimensioni (BLOB) e altri oggetti di grandi dimensioni. Il tipo di dati lo è un dominio del tipo di dati oid. In altre parole, è un identificatore di oggetti con vincoli opzionali. Per ulteriori informazioni, consulta Identificatori di oggetti nella documentazione di PostgreSQL. In poche parole, puoi utilizzare il tipo di dati lo per distinguere le colonne del database che contengono riferimenti di oggetti di grandi dimensioni da altri identificatori di oggetti (OID).

• lo_manage – Questa è una funzione che puoi utilizzare nei trigger sulle colonne di tabella contenenti riferimenti di oggetti di grandi dimensioni. Ogni volta che elimini o modifichi un valore che fa riferimento a un oggetto di grandi dimensioni, il trigger scollega l'oggetto (lo_unlink) dal suo riferimento. Utilizza il trigger su una colonna solo se la colonna è l'unico riferimento del database all'oggetto di grandi dimensioni.

Per ulteriori informazioni sul modulo di oggetti di grandi dimensioni, consulta lo nella documentazione di PostgreSQL.

Installazione dell'estensione lo

Prima di installare l'estensione lo, assicurati di disporre dei privilegi rds_superuser.

Installare l'estensione

1. Utilizza psql per connetterti all'istanza database principale del cluster database Aurora PostgreSQL.

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

   Specifica la password, quando richiesto. Il client psql si connette e visualizza il database di connessione amministrativa predefinito postgres=> come prompt.

2. Installa l'estensione come segue:

   postgres=> CREATE EXTENSION lo;
   CREATE EXTENSION

Ora puoi utilizzare il tipo di dati lo per definire le colonne nelle tabelle. Ad esempio, puoi creare una tabella (images) contenente dati immagine raster. Puoi utilizzare il tipo di dati lo per una colonna raster, come mostrato nell'esempio seguente, che crea una tabella.

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

Utilizzo della funzione di trigger lo_manage per eliminare gli oggetti

Puoi utilizzare la funzione lo_manage in un trigger su un lo o altre colonne di oggetti di grandi dimensioni per rimuovere (e prevenire oggetti orfani) quando lo viene aggiornato o eliminato.

Impostare trigger su colonne che fanno riferimento a oggetti di grandi dimensioni

• Completa una delle seguenti operazioni:

  • Crea un trigger BEFORE UPDATE OR DELETE su ogni colonna per contenere riferimenti univoci a oggetti di grandi dimensioni, utilizzando il nome della colonna come argomento.

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

  • Applica un trigger solo quando la colonna viene aggiornata.

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

La funzione di trigger lo_manage funziona solo nel contesto dell'inserimento o dell'eliminazione dei dati di colonna, a seconda di come definisci il trigger. Non ha alcun effetto quando esegui un'operazione DROP o TRUNCATE su un database. Ciò significa che devi eliminare le colonne di oggetti da qualsiasi tabella prima del rilascio, per evitare la creazione di oggetti orfani.

Ad esempio, supponi di voler rilasciare il database contenente la tabella images. Per eliminare la colonna procedi come segue.

postgres=> DELETE FROM images COLUMN raster

Ipotizzando che la funzione lo_manage sia definita su tale colonna per gestire le eliminazioni, ora puoi rilasciare la tabella in modo sicuro.

Utilizzo dell'utilità vacuumlo

L'utilità vacuumlo identifica e può rimuovere oggetti orfani di grandi dimensioni dai database. Questa utilità è disponibile a partire da PostgreSQL 9.1.24. Se gli utenti del database utilizzano regolarmente oggetti di grandi dimensioni, si consiglia di eseguire vacuumlo occasionalmente per rimuovere oggetti di grandi dimensioni orfani.

Prima di installare l'estensione lo, puoi utilizzare vacuumlo per valutare se il cluster database Aurora PostgreSQL può trarne vantaggio. A questo scopo, esegui vacuumlo con l'opzione -n (no-op) per mostrare cosa viene rimosso, come illustrato di seguito:

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

Come mostra l'output, gli oggetti orfani di grandi dimensioni non sono un problema per questo particolare database.

Per ulteriori informazioni su questa utilità, consulta vacuumlo nella documentazione di PostgreSQL.