PostgreSQL Antipatterns: αλλαγή δεδομένων παρακάμπτοντας έναν κανόνα

Αργά ή γρήγορα, πολλοί έρχονται αντιμέτωποι με την ανάγκη να διορθώσουν μαζικά κάτι στα αρχεία του πίνακα. Έχω ήδη πες μου πώς να το κάνω καλύτερα, και πώς - είναι καλύτερα να μην το κάνετε. Σήμερα θα μιλήσω για τη δεύτερη πτυχή της μαζικής ενημέρωσης - σχετικά με τα ερεθίσματα.

Για παράδειγμα, σε ένα τραπέζι στο οποίο πρέπει να διορθώσετε κάτι, κρέμεται μια κακή σκανδάλη ON UPDATE, μεταφέροντας όλες τις αλλαγές σε ορισμένα συγκεντρωτικά στοιχεία. Και πρέπει να ενημερώσετε τα πάντα (π.χ. αρχικοποιήστε ένα νέο πεδίο) τόσο προσεκτικά ώστε να μην επηρεαστούν αυτά τα συγκεντρωτικά στοιχεία.

Ας απενεργοποιήσουμε απλώς τις σκανδάλες!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- тут долго-долго
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Στην πραγματικότητα, αυτό είναι όλο - όλα κρέμονται.

Επειδή ALTER TABLE επιβάλλει Αποκλειστική πρόσβαση- κλειδαριά κάτω από την οποία κανείς δεν τρέχει παράλληλα, έστω και απλή SELECT, δεν θα μπορεί να διαβάσει τίποτα από τον πίνακα. Δηλαδή, μέχρι να τελειώσει αυτή η συναλλαγή, όλοι όσοι θέλουν να «απλώς διαβάσουν» θα περιμένουν. Και το θυμόμαστε UPDATE έχουμε πολύ...

Ας το απενεργοποιήσουμε γρήγορα και μετά ας το ενεργοποιήσουμε γρήγορα!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
COMMIT;

UPDATE ...;

BEGIN;
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Εδώ η κατάσταση είναι ήδη καλύτερη, ο χρόνος αναμονής είναι πολύ μικρότερος. Αλλά μόνο δύο προβλήματα χαλάνε όλη την ομορφιά:

• ALTER TABLE η ίδια περιμένει για όλες τις άλλες λειτουργίες στο τραπέζι, συμπεριλαμβανομένων των μεγάλων SELECT
• Ενώ η σκανδάλη είναι απενεργοποιημένη, "πετάξτε" οποιαδήποτε αλλαγή στον πίνακα, ούτε καν το δικό μας. Και δεν θα μπει στα συγκεντρωτικά στοιχεία, αν και θα έπρεπε. Ταλαιπωρία!

Διαχείριση μεταβλητών συνεδρίας

Έτσι, στην προηγούμενη έκδοση, πέσαμε σε ένα θεμελιώδες σημείο - πρέπει να διδάξουμε με κάποιο τρόπο τη σκανδάλη να διακρίνει τις αλλαγές "μας" στον πίνακα από "όχι δικές μας". Τα «δικά μας» παραλείπονται ως έχουν, αλλά στο «όχι τα δικά μας» ενεργοποιούνται. Για αυτό μπορείτε να χρησιμοποιήσετε μεταβλητές συνεδρίας.

session_replication_role

Διαβάστε εγχειρίδιο:

Ο μηχανισμός ενεργοποίησης επηρεάζεται επίσης από τη μεταβλητή διαμόρφωσης session_replication_role. Ενεργοποιημένο χωρίς πρόσθετες οδηγίες (προεπιλογή), οι κανόνες ενεργοποίησης θα ενεργοποιηθούν όταν ο ρόλος αναπαραγωγής είναι "origin" (προεπιλογή) ή "τοπικός". Ενεργοποιήθηκαν τα εναύσματα με τον καθορισμό ENABLE REPLICA, θα λειτουργήσει μόνο εάν τρέχουσα λειτουργία συνεδρίας - "αντίγραφο" και ενεργοποιούνται με τον καθορισμό ENABLE ALWAYS, θα λειτουργήσει ανεξάρτητα από την τρέχουσα λειτουργία αναπαραγωγής.

Θα τονίσω ιδιαίτερα ότι η ρύθμιση δεν ισχύει για όλα ταυτόχρονα, όπως ALTER TABLE, αλλά μόνο στην ξεχωριστή ειδική σύνδεσή μας. Συνολικά, έτσι ώστε καμία εφαρμογή να μην λειτουργεί:

SET session_replication_role = replica; -- выключили триггеры
UPDATE ...;
SET session_replication_role = DEFAULT; -- вернули в исходное состояние

Κατάσταση εσωτερικής σκανδάλης

Ωστόσο, η παραπάνω επιλογή λειτουργεί για όλους τους κανόνες ταυτόχρονα (ή πρέπει να "εναλλάξετε" εκ των προτέρων τους κανόνες που δεν θέλετε να απενεργοποιήσετε). Και αν χρειαστούμε «απενεργοποιήστε» μια συγκεκριμένη σκανδάλη?

Αυτό θα μας βοηθήσει μεταβλητή συνεδρίας "χρήστης".:

Τα ονόματα των παραμέτρων επέκτασης γράφονται ως εξής: το όνομα της επέκτασης ακολουθούμενο από μια τελεία και μετά το ίδιο το όνομα της παραμέτρου, παρόμοια με τα ονόματα πλήρων αντικειμένων στην SQL. Για παράδειγμα: plpgsql.variable_conflict.
Επειδή οι επιλογές εκτός συστήματος μπορούν να οριστούν σε διαδικασίες που δεν φορτώνουν την κατάλληλη μονάδα επέκτασης, η PostgreSQL αποδέχεται τιμές για οποιαδήποτε ονόματα με δύο συστατικά.

Αρχικά, οριστικοποιούμε τη σκανδάλη, κάπως έτσι:

BEGIN
    -- процессу конвертации можно делать все
    IF current_setting('mycfg.my_table_convert_process') = 'TRUE' THEN
        IF TG_OP IN ('INSERT', 'UPDATE') THEN
            RETURN NEW;
        ELSE
            RETURN OLD;
        END IF;
    END IF;
...

Παρεμπιπτόντως, αυτό μπορεί να γίνει "για το κέρδος", χωρίς αποκλεισμό, μέσω CREATE OR REPLACE για τη λειτουργία σκανδάλης. Και μετά στην ειδική σύνδεση τυλίγουμε τη μεταβλητή "μας":

SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- вернули в исходное состояние

Ξέρεις άλλους τρόπους; Κοινοποιήστε στα σχόλια.

Πηγή: www.habr.com