Documentazione di PostgreSQL 9.0 > Programmazione del server > PL/pgSQL - Linguaggio procedurale SQL > PL/pgSQL Under the Hood
PrecedenteProcedure TriggerConsigli per lo sviluppo in PL/pgSQLSuccessivo

39.10. PL/pgSQL Under the Hood

Questa sezione discute alcuni dettagli dell'implementazione che frequentemente sono importanti da sapere per gli utenti PL/pgSQL.

39.10.1. Sostituzione di variabili

Le funzioni SQL e le espressioni all'interno di funzioni PL/pgSQL possono fare riferimento a variabili e parametri della funzione. Dietro le quinte, PL/pgSQL sostituisce i parametri della query per tali riferimenti. I parametri saranno sostituiti solo in posti dove un riferimento a un parametro o colonna è permesso sintatticamente. Come caso estremo, considerare questo esempio di scarso stile di programmazione:

INSERT INTO foo (foo) VALUES (foo);

La prima occorenza di foo dev'essere sintatticamente il nome di una tabella, quindi non sarà sostituito, anche se la funzione ha una variabile chiamata foo. La seconda occorrenza deve essere il nome di una colonna della tabella, quindi non sarà sostituita. Solo la terza occorrenza è un candidato per essere un riferimento alla variabile della funzione.

[Nota]

Nota

Le versioni di PostgreSQL™ precedenti la 9.0 avrebbero provato a sostituire la variabile in tutti e tre i casi, generando errori di sintassi.

Dato che i nomi delle variabili non sono sintatticamente diversi dai nomi delle colonne della tabella, ci può essere ambiguità in istruzioni che si riferiscono anche alle tabelle: un fato nome è inteso riferirsi a una colonna di tabella, o a una variabile? Si cambi l'esempio precedente in

INSERT INTO dest (col) SELECT foo + bar FROM src;

Qui, dest e src devono essere nomi di tabella, e col deve essere una colonna di dest, ma foo e bar potrebbero ragionevolmente essere variabili della funzione o colonne di src.

Come predefinito, PL/pgSQL riporterà un errore se un nome in un'istruzione SQL potrebbe riferirsi sia a una variabile che alla colonna di una tabella. Si può risolvere tale problema rinominando la variabile o la colonna, o qualificando il riferimento ambiguo, o dicendo a PL/pgSQL quale interpretazione preferire.

La soluzione più semplice è di rinominare la variabile o la colonna. Una regola comune di programmazione è di usare una convenzione di nomenclatura diversa per le variabili PL/pgSQL rispetto a quella usata per i nomi delle colonne. Per esempio, se coerentemente le variabili delle funzioni si chiamano v_something mentre nessuno dei nomi delle colonne comincia con v_, non si verificheranno conflitti.

Alternativamente si possono qualificare riferimenti ambigui per renderli chiari. Nell'esempio sopra, src.foo sarebbe un riferimento non ambiguo alla colonna della tabella. Per creare un riferimento non ambiguo a una variabile, dichiararla in un blocco con etichetta ed usare l'etichetta del blocco (si veda Sezione 39.2, «Struttura di PL/pgSQL»). Per esempio,

<<block>>
DECLARE
    foo int;
BEgin
    foo := ...;
    INSERT INTO dest (col) SELECT block.foo + bar FROM src;

Qui block.foo infica la variabile anche se c'è una colonna foo in src. I parametri di funzione, così come variabili speciali tipo FOUND, possono essere qualificate dal nome della funzione, dato che sono dichiarati implicitamente in un blocco esterno etichettato con il nome della funzione.

A volte è impraticabile risolvere tutti i riferimenti ambigui in un sostanzioso pezzo di codice PL/pgSQL. In tali casi è possibile specificare che PL/pgSQL dovrebbe risolvere riferimenti ambigui come riferimenti a variabile (che è compatibile con il comportamento di PL/pgSQL prima di PostgreSQL™ 9.0), o come riferimento alla colonna della tabella (che è compatibile con alcuni altri sistemi come Oracle™).

Per cambiare questo comportamento a livello di sistema, impostare il parametro di configurazione plpgsql.variable_conflict ad un valora tra error, use_variable, o use_column (dove error è il predefinito). Questo parametro influisce successive compilazioni o istruzioni in funzioni PL/pgSQL, ma non istruzioni già compilate nella sessione corrente. Per impostare il parametro prima che PL/pgSQL sia stato caricato, è necessario aver aggiunto «plpgsql» all'elenco custom_variable_classes in postgresql.conf. Dato che cambiare quest'impostazione può causare cambiamenti inaspettati nel comportamento di funzioni PL/pgSQL, può essere cambiato solo da un superutente.

È anche possibile impostare il comportamento funzione per funzione, inserendo uno di questi comandi speciali all'inizio del testo della funzione:

#variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column

Questi comandi hanno consegnuenze solo sulla funzione in cui sono scritti, e sovrascrivono l'impostazione di plpgsql.variable_conflict. Un esempio è

CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
    #variable_conflict use_variable
    DECLARE
        curtime timestamp := now();
    BEGIN
        UPDATE users SET last_modified = curtime, comment = comment
          WHERE users.id = id;
    END;
$$ LANGUAGE plpgsql;

Nel comando UPDATE, curtime, comment e id faranno riferimento alle variabili e parametri della funzione sia che users abbia o no colonne chiamate in quel modo. Notare che si è dovuto qualificare il riferimento a users.id nella clausola WHERE per renderlo un riferimento alla colonna della tabella. Ma non si è dovuto qualificare il riferimento a comment come obiettivo dell'elenco UPDATE, dato che sintatticamente quella deve essere una colonna di users. Si poteva scrivere la stessa funzione senza dipendere dall'impostazione di variable_conflict in questo modo:

CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
    <<fn>>
    DECLARE
        curtime timestamp := now();
    BEgin
        UPDATE users SET last_modified = fn.curtime, comment = stamp_user.comment
          WHERE users.id = stamp_user.id;
    END;
$$ LANGUAGE plpgsql;

La sostituzione di variabili non avviene nella stringa del comando fornito a EXECUTE o una delle sue varianti. Se si ha bisogno di inserire un valore variabile in tale comando, farlo come parte della costruzione del valore stringa, o usare USING, come illustrato in Sezione 39.5.4, «Eseguire comandi dinamici».

La sostituzione di variabile correntemente funziona solo in comandi SELECT, INSERT, UPDATE, and DELETE, dato che il motore SQL principale permette parametri per query solo in questi comandi. Per usare un nome o un valore non costante in altri tipi di istruzione (chiamate genericamente instruzioni di utilità), si deve costruire l'istruzione di utilità come una stringa ed eseguirla con EXECUTE.

39.10.2. Cache del piano

L'interprete PL/pgSQL fa il parsing del sorgente della funzione e produce un albero di istruzioni binarie interne la prima volta che la funzione viene chiamata (all'interno di ogni sessione). L'albero di istruzioni traduce completamente la struttura delle istruzioni PL/pgSQL, ma espressioni e comandi SQL individuali usate nella funzione non sono tradotte immediatamente.

Ogni volta che un espressione e comando SQL viene eseguita, l'interprete PL/pgSQL crea un piano di esecuzione (usando le funzioni del gestore SPI SPI_prepare e SPI_saveplan). Visite successive a questa espressione o comando riusano il piano preparato. Perciò, una funzione con codice condizionale che contiene istruzioni per la cui esecuzione potrebbero essere necessari piani preparerà e salvera solo quei piani che sono realmente usati durante il ciclo di vita della connessione al database. Questo può ridurre sostanzialmente l'ammontare totale del tempo richiesto per fare il parsing e generare i piani di esecuzione per le istruzioni in una funzione PL/pgSQL. Uno svantaggio è che errori in una specifica espressione o comando non possono essere individuati finchè quella parte della funzione viene raggiunta durante l'esecuzione. (Errori di sintassi trascurabili saranno individuati durante il passo iniziale di parsing, ma qualsiasi cosa più approfondita non sarà individuata fino all'esecuzione).

Un piano salvato sarà ripianificato automaticamente se c'è qualsiasi cambiamento allo schema in qualsiasi tabella usata nella query, o se una qualsiasi funzione definita dall'utente usata nella query viene ridefinita. Questo rende il riuso di piano preparati trasparente nella maggior parte dei casi, ma ci sono casi limite dove un vecchio piano potrebbe essere riusato. Un esempio è che eliminare e ricreare un operatore definito dall'utente non influenzerà piani già sottoposti a cache; loro continueranno a chiamare la funzione originale sottostante dell'operatore, se non è stato cambiato. Quando necessario, la cache può essere svuotata cominciando una nuova sessione di database.

Dato che PL/pgSQL salva i piani di esecuzione in questo modo, i comando SQL che appaiono direttamente in una funzione PL/pgSQL devono fare riferimento alle stesse tabelle e colonne ad ogni esecuzione; cioè, non è possbile usare un parametro come nome di una tabella o colonna in un comando SQL. Per superare questa restrizione, è possibile costruire comandi dinamici usando l'istruzione PL/pgSQL EXECUTE - al prezzo di costruire un nuovo piano di esecuzione ad ogni esecuzione.

Un altro punto importante è che i piani preparati vengono parametrizzati per permettere ai valori delle variabili PL/pgSQL di cambiare da un uso al prossimo, come discusso in dettaglio sopra. A volte questo significa che un piano è meno efficiente rispetto a quello che sarebbe stato se generato per un valore della variabile specifico. Come esempio, considerare

SELECT * INTO myrec FROM dictionary WHERE word LIKE search_term;

dove search_term è una variabile PL/pgSQL. Il piano sottoposto a cache per questa query non userà mai un indice su word, dato che il pianificatore non può assumere che il modello LIKE sia fissato a sinistra durante l'esecuzione. Per usare un indice la query deve essere pianificata fornendo con uno specifico modello LIKE. Questa è un'altra situazione dove EXECUTE può essere usato per forzare la generazione di un nuovo piano per ogni esecuzione.

La natura mutabile delle variabili record presenta un altro problema in questa connessione. Quando i campi di una variabile record sono usati in espressioni o istruzioni, i tipi di dato del campo non devono cambiare da una chiamata della funzione alla successiva, dato che ogni espressione sarà pianificata usando il tipo di dato che è presente quando l'espressione viene raggiunta per la prima volta. EXECUTE può essere usata per scavalcare questo problema quando necessario.

Se la stessa funzione viene usata come trigger per più di una tabella, PL/pgSQL prepara e sottopone a cache i piani indipendentemente per ogni tabella - cioè, c'è una cache per ogni funzione trigger e combinazione di tabella, non solo per ogni funzione. Questo allieva alcuni dei problemi con tipi di dato variabili; per esempio, una funzione trigger sarà capace di funzionare con successo con una colonna chiamata key anche se ha tipi diversi in tabelle diverse.

Ugualmente, funzioni aventi tipi di argomento polimorfici hanno una cache di piano separata per ogni combinazione dei tipi effettivi degli argomenti per i quali sono invocate, così quelle differenze tra tipi di dato non causano fallimenti inaspettati.

La cache del piano può a volte avere effetti sull'interpretazione di valori sensibili al tempo. Per esempio c'è una differenza tra quello che queste due funzioni fanno:

CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$
    BEGIN
        INSERT INTO logtable VALUES (logtxt, 'now');
    END;
$$ LANGUAGE plpgsql;

e:

CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$
    DECLARE
        curtime timestamp;
    BEGIN
        curtime := 'now';
        INSERT INTO logtable VALUES (logtxt, curtime);
    END;
$$ LANGUAGE plpgsql;

Nel caso di logfunc1, il parser principale di PostgreSQL™ sa quando preparare il piano per la INSERT che la stringa 'now' dovrebbe essere interpretrata come timestamp, dato che la colonna obiettivo di logtable è di quel tipo. Perciò, 'now' sarà convertito a una costante quando la INSERT è pianificata, e quindi usata in tutte le invocazioni di logfunc1 durante il ciclo di vita della sessione. Inutile dire che questo non è quello che voleva il programmatore.

Nel caso di logfunc2, il parser principale di PostgreSQL™ non sa quale tipo dovrebbe diventare 'now' e quindi restituisce un valore di tipo text contenente la stringa now. Durante l'assegnamento conseguente alla variabile locale curtime, l'interprete PL/pgSQL converte questa stringa al tipo timestamp chiamando le funzioni per la conversione text_out e timestamp_in. Così, il timestap calcolato viene aggiornato ad ogni esecuzione come si aspetta il programmatore.

Documentazione di PostgreSQL 9.0 > Programmazione del server > PL/pgSQL - Linguaggio procedurale SQL > PL/pgSQL Under the Hood
PrecedenteProcedure TriggerConsigli per lo sviluppo in PL/pgSQLSuccessivo