La clausola CHECK sulle viste aggiornabili

Continuiamo a parlare delle novità sulle viste introdotte in PostgreSQL 9.4. Abbiamo già discusso sulla possibilità di poter effettuare un refresh concorrente di una vista materializzata. Approfondiremo adesso la nuova funzionalità di CHECK nell’inserimento su viste aggiornabili.

Dalla versione 9.3 di PostgreSQL è possibile aggiornare e inserire nuovi dati direttamente su viste. Le operazioni vengono in modo trasparente e automatico indirizzate sulla tabella sottostante (è infatti necessario che la struttura delle viste sia semplice – ad esempio, che ci sia un solo elemento FROM).
Con PostgreSQL 9.4 è possibile aggiungere l’opzione CHECK per l’INSERT sulla vista.

Consideriamo, ad esempio, una tabella contenente un solo campo di numeri interi e due viste, una relativa ai numeri della tabella divisibili per 2 e l’altra relativa ai numeri divisibili per 3.

Proviamo ad inserire il numero 123 sulla prima vista, chiamata first:

$ CREATE TABLE some_data(id int4 PRIMARY KEY);
CREATE TABLE
$ CREATE VIEW first AS SELECT * FROM some_data WHERE 0 = id%2;
CREATE VIEW
$ CREATE VIEW second AS SELECT * FROM some_data WHERE 0 = id%3;
CREATE VIEW
$ INSERT INTO first(id) VALUES (123);

Questo verrà inserito nella tabella sottostante (some_data), nonostante la vista sia dedicata solo ai numeri divisibili per 2 (ma non sarà visibile in essa).
L’opzione CHECK in PostgreSQL 9.4 serve proprio a gestire i casi di inserimento sulle viste eseguendo preventivamente un controllo dei valori che si intende inserire, compatibilmente con la definizione della vista.

Sono previste due possibili opzioni:

• CASCADED CHECK (default)  in cui i check vengono applicati in cascata anche sulle altre viste eventualmente presenti sulla stessa tabella;
• LOCAL CHECK in cui i check vengono applicati sulla singola vista in cui viene effettuato la INSERT.

Riprendendo l’esempio già descritto, proviamo a utilizzare l’opzione CHECK:

$ CREATE TABLE some_data (id int4 PRIMARY KEY);
CREATE TABLE
$ CREATE VIEW first AS SELECT * FROM some_data WHERE 0 = id % 2 WITH CHECK OPTION;
CREATE VIEW
$ CREATE VIEW second AS SELECT * FROM first WHERE 0 = id % 3 WITH CHECK OPTION;
CREATE VIEW
$ INSERT INTO first(id) VALUES (14);
INSERT 0 1
$ INSERT INTO first(id) VALUES (15);
ERROR:  new row violates WITH CHECK OPTION for view "first"
$ INSERT INTO second(id) VALUES (15);
ERROR:  new row violates WITH CHECK OPTION for view "first"

Il valore 14 viene correttamente inserito nella prima vista, mentre il valore 15 no – come è lecito attendersi.
Meno chiaro è l’errore sull’inserimento di 15 sulla seconda vista: non viene inserito (seppure divisibile per 3) in quanto l’opzione CHECK deve essere definita in una sola delle due viste, e di tipo LOCAL CHECK.

Non è sufficente definire su entrambe le viste l’opzione LOCAL CHECK per aggirare il problema:

$ DROP VIEW first;
DROP VIEW
$ DROP VIEW second;
DROP VIEW
$ CREATE VIEW first AS SELECT * FROM some_data WHERE 0 = id % 2 WITH LOCAL CHECK OPTION;
CREATE VIEW
$ CREATE VIEW second AS SELECT * FROM first WHERE 0 = id % 3 WITH LOCAL CHECK OPTION;
CREATE VIEW
$ INSERT INTO second(id) VALUES (15);
ERROR:  new row violates WITH CHECK OPTION for view "first"

Di seguito l’esempio funzionante:

$ DROP VIEW first;
DROP VIEW
$ DROP VIEW second;
DROP VIEW
$ CREATE VIEW first AS SELECT * FROM some_data WHERE 0 = id % 2;
CREATE VIEW
$ CREATE VIEW second AS SELECT * FROM first WHERE 0 = id % 3 WITH LOCAL CHECK OPTION;
CREATE VIEW
$ INSERT INTO second(id) VALUES (15);
INSERT 0 1

La clausola CHECK sulle viste aggiornabili rappresenta un nuovo meccanismo di controllo da inserire nel database, direttamente sull’inserimento di nuovi dati utilizzando viste. Un ulteriore passo che rafforza sempre di più il ruolo del database in materia di applicazione di integrità dei dati.