DBA: οργανώστε σωστά τους συγχρονισμούς και τις εισαγωγές

Για πολύπλοκη επεξεργασία μεγάλων συνόλων δεδομένων (διαφορετικά Διαδικασίες ETL: εισαγωγές, μετατροπές και συγχρονισμός με εξωτερική πηγή) συχνά υπάρχει ανάγκη προσωρινά «θυμηθείτε» και αμέσως γρήγορη επεξεργασία κάτι ογκώδες.

Μια τυπική εργασία αυτού του είδους συνήθως ακούγεται κάπως έτσι: "Ακριβώς εδώ λογιστήριο που εκφορτώνεται από την τράπεζα πελατών τις τελευταίες ληφθείσες πληρωμές, πρέπει να τις ανεβάσετε γρήγορα στον ιστότοπο και να τις συνδέσετε με τους λογαριασμούς σας"

Αλλά όταν ο όγκος αυτού του «κάτι» αρχίζει να μετριέται σε εκατοντάδες megabyte και η υπηρεσία πρέπει να συνεχίσει να λειτουργεί με τη βάση δεδομένων 24x7, προκύπτουν πολλές παρενέργειες που θα καταστρέψουν τη ζωή σας.

Για να τα αντιμετωπίσετε στην PostgreSQL (και όχι μόνο σε αυτήν), μπορείτε να χρησιμοποιήσετε ορισμένες βελτιστοποιήσεις που θα σας επιτρέψουν να επεξεργαστείτε τα πάντα πιο γρήγορα και με λιγότερη κατανάλωση πόρων.

1. Πού να αποσταλεί;

Αρχικά, ας αποφασίσουμε πού μπορούμε να ανεβάσουμε τα δεδομένα που θέλουμε να «επεξεργαζόμαστε».

1.1. Προσωρινά τραπέζια (ΠΡΟΣΩΡΙΝΟΣ ΠΙΝΑΚΑΣ)

Κατ 'αρχήν, για την PostgreSQL οι προσωρινοί πίνακες είναι ίδιοι με οποιονδήποτε άλλο. Ως εκ τούτου, δεισιδαιμονίες όπως «Τα πάντα εκεί είναι αποθηκευμένα μόνο στη μνήμη και μπορούν να τελειώσουν». Υπάρχουν όμως και αρκετές σημαντικές διαφορές.

Το δικό σας "χώρο ονομάτων" για κάθε σύνδεση στη βάση δεδομένων

Εάν δύο συνδέσεις προσπαθήσουν να συνδεθούν ταυτόχρονα CREATE TABLE x, τότε κάποιος θα πάρει σίγουρα σφάλμα μη μοναδικότητας αντικείμενα βάσης δεδομένων.

Αν όμως προσπαθήσουν και οι δύο να το εκτελέσουν CREATE TEMPORARY TABLE x, τότε και οι δύο θα το κάνουν κανονικά, και θα πάρουν όλοι το αντίγραφό σας τραπέζια. Και δεν θα υπάρχει τίποτα κοινό μεταξύ τους.

«Αυτοκαταστρέφεται» κατά την αποσύνδεση

Όταν η σύνδεση είναι κλειστή, όλοι οι προσωρινοί πίνακες διαγράφονται αυτόματα, άρα μη αυτόματα DROP TABLE x δεν υπάρχει νόημα εκτός από...

Εάν εργάζεστε pgbouncer σε λειτουργία συναλλαγής, τότε η βάση δεδομένων συνεχίζει να πιστεύει ότι αυτή η σύνδεση είναι ακόμα ενεργή και σε αυτήν αυτός ο προσωρινός πίνακας εξακολουθεί να υπάρχει.

Επομένως, εάν προσπαθήσετε να το δημιουργήσετε ξανά, από διαφορετική σύνδεση στο pgbouncer, θα προκύψει σφάλμα. Αλλά αυτό μπορεί να παρακαμφθεί χρησιμοποιώντας CREATE TEMPORARY TABLE IF NOT EXISTS x.

Είναι αλήθεια ότι είναι καλύτερο να μην το κάνετε ούτως ή άλλως, γιατί τότε μπορείτε "ξαφνικά" να βρείτε εκεί τα δεδομένα που απομένουν από τον "προηγούμενο ιδιοκτήτη". Αντίθετα, είναι πολύ καλύτερο να διαβάσετε το εγχειρίδιο και να δείτε ότι κατά τη δημιουργία ενός πίνακα είναι δυνατή η προσθήκη ON COMMIT DROP - δηλαδή όταν ολοκληρωθεί η συναλλαγή, ο πίνακας θα διαγραφεί αυτόματα.

Μη αντιγραφή

Επειδή ανήκουν μόνο σε μια συγκεκριμένη σύνδεση, οι προσωρινοί πίνακες δεν αναπαράγονται. Αλλά Αυτό εξαλείφει την ανάγκη για διπλή καταγραφή δεδομένων σε σωρό + WAL, οπότε η ΕΙΣΑΓΩΓΗ/ΕΝΗΜΕΡΩΣΗ/ΔΙΑΓΡΑΦΗ σε αυτό είναι πολύ πιο γρήγορη.

Αλλά επειδή ένας προσωρινός πίνακας εξακολουθεί να είναι ένας "σχεδόν συνηθισμένος" πίνακας, δεν μπορεί να δημιουργηθεί ούτε σε αντίγραφο. Τουλάχιστον προς το παρόν, αν και το αντίστοιχο patch κυκλοφορεί εδώ και καιρό.

1.2. ΞΕΧΩΡΙΣΤΟ ΤΡΑΠΕΖΙ

Αλλά τι πρέπει να κάνετε, για παράδειγμα, εάν έχετε κάποιο είδος δυσκίνητης διαδικασίας ETL που δεν μπορεί να εφαρμοστεί σε μία συναλλαγή, αλλά εξακολουθείτε να έχετε pgbouncer σε λειτουργία συναλλαγής; ..

Ή η ροή δεδομένων είναι τόσο μεγάλη που Δεν υπάρχει αρκετό εύρος ζώνης σε μία σύνδεση από μια βάση δεδομένων (ανάγνωση, μία διεργασία ανά CPU);..

Ή γίνονται κάποιες επεμβάσεις ασύγχρονα σε διαφορετικές συνδέσεις;..

Υπάρχει μόνο μία επιλογή εδώ - δημιουργήστε προσωρινά έναν μη προσωρινό πίνακα. λογοπαίγνιο, ναι. Αυτό είναι:

• δημιούργησα «τους δικούς μου» πίνακες με τυχαία ονόματα ώστε να μην διασταυρώνονται με κανέναν
• Εκχύλισμα: τα γέμισε με δεδομένα από εξωτερική πηγή
• Μεταμορφώστε: μετατροπή, συμπλήρωση πεδίων σύνδεσης κλειδιών
• Φορτίο: χύθηκε έτοιμα δεδομένα σε πίνακες-στόχους
• διέγραψε τους πίνακες "μου".

Και τώρα - μια μύγα στην αλοιφή. Στην πραγματικότητα, Όλες οι εγγραφές στην PostgreSQL γίνονται δύο φορές - πρώτη στο WAL, μετά στα σώματα πίνακα/ευρετηρίου. Όλα αυτά γίνονται για την υποστήριξη ACID και τη σωστή ορατότητα δεδομένων μεταξύ COMMIT«ανησυχώ και ROLLBACK«μηδενικές συναλλαγές.

Αλλά δεν το χρειαζόμαστε αυτό! Έχουμε όλη τη διαδικασία Είτε ήταν απόλυτα επιτυχημένο είτε όχι.. Δεν έχει σημασία πόσες ενδιάμεσες συναλλαγές θα υπάρξουν - δεν μας ενδιαφέρει να "συνεχίσουμε τη διαδικασία από τη μέση", ειδικά όταν δεν είναι σαφές πού ήταν.

Για να γίνει αυτό, οι προγραμματιστές PostgreSQL, στην έκδοση 9.1, εισήγαγαν κάτι τέτοιο όπως ΞΕΧΩΡΙΣΤΑ τραπέζια:

Με αυτήν την ένδειξη, ο πίνακας δημιουργείται ως μη συνδεδεμένος. Τα δεδομένα που εγγράφονται σε μη καταγεγραμμένους πίνακες δεν περνούν από το αρχείο καταγραφής προκαταβολής εγγραφής (βλ. Κεφάλαιο 29), με αποτέλεσμα οι πίνακες αυτοί να λειτουργούν πολύ πιο γρήγορα από το συνηθισμένο. Ωστόσο, δεν έχουν ανοσία στην αποτυχία. σε περίπτωση αποτυχίας του διακομιστή ή τερματισμού έκτακτης ανάγκης, μη συνδεδεμένος πίνακας περικόπτεται αυτόματα. Επιπλέον, τα περιεχόμενα του μη συνδεδεμένου πίνακα δεν επαναλαμβάνεται σε σκλάβους διακομιστές. Τυχόν ευρετήρια που δημιουργούνται σε έναν μη καταγεγραμμένο πίνακα γίνονται αυτόματα unlogged.

Με λίγα λόγια, θα είναι πολύ πιο γρήγορο, αλλά εάν ο διακομιστής της βάσης δεδομένων «πέσει», θα είναι δυσάρεστο. Αλλά πόσο συχνά συμβαίνει αυτό και η διαδικασία ETL σας ξέρει πώς να το διορθώσει σωστά «από τη μέση» μετά την «αναζωογόνηση» της βάσης δεδομένων;..

Εάν όχι, και η παραπάνω περίπτωση είναι παρόμοια με τη δική σας, χρησιμοποιήστε UNLOGGEDαλλά ποτέ μην ενεργοποιήσετε αυτό το χαρακτηριστικό σε πραγματικούς πίνακες, τα δεδομένα από τα οποία σας είναι αγαπητά.

1.3. ON COMMIT { ΔΙΑΓΡΑΦΗ ΣΕΙΡΩΝ | ΠΤΩΣΗ}

Αυτή η κατασκευή σάς επιτρέπει να καθορίσετε την αυτόματη συμπεριφορά όταν ολοκληρώνεται μια συναλλαγή κατά τη δημιουργία ενός πίνακα.

επί ON COMMIT DROP Έγραψα ήδη παραπάνω, δημιουργεί DROP TABLE, αλλά με ON COMMIT DELETE ROWS η κατάσταση είναι πιο ενδιαφέρουσα - δημιουργείται εδώ TRUNCATE TABLE.

Δεδομένου ότι ολόκληρη η υποδομή για την αποθήκευση της μετα-περιγραφής ενός προσωρινού πίνακα είναι ακριβώς η ίδια με αυτή ενός κανονικού πίνακα, τότε Η συνεχής δημιουργία και διαγραφή προσωρινών πινάκων οδηγεί σε σοβαρή «διόγκωση» των πινάκων συστήματος pg_class, pg_attribute, pg_attrdef, pg_depend,…

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

Γενικά, μην το κάνετε αυτό! Σε αυτή την περίπτωση είναι πολύ πιο αποτελεσματικό CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS βγάλτε το από τον κύκλο συναλλαγής - τότε μέχρι την αρχή κάθε νέας συναλλαγής οι πίνακες είναι ήδη θα υπάρχει (αποθήκευση κλήσης CREATE), αλλά θα είναι άδειο, χάρη σε TRUNCATE (αποθηκεύσαμε και την κλήση του) κατά την ολοκλήρωση της προηγούμενης συναλλαγής.

1.4. ΣΑΝ...ΣΥΜΠΕΡΙΛΑΜΒΑΝΟΜΕΝΩΝ...

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

Όμως η τεμπελιά είναι η μηχανή της προόδου! Να γιατί δημιουργία νέου πίνακα "βάσει δείγματος" μπορεί να είναι πολύ πιο απλό:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

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

Επειδή, συχνά, τα απαιτούμενα ευρετήρια συμπίπτουν με τα ευρετήρια του πίνακα προορισμού, μπορείτε απλά να γράψετε LIKE target_table INCLUDING INDEXES.

Εάν χρειάζεστε επίσης DEFAULT-τιμές (για παράδειγμα, για να συμπληρώσετε τις τιμές του πρωτεύοντος κλειδιού), μπορείτε να χρησιμοποιήσετε LIKE target_table INCLUDING DEFAULTS. Ή απλά - LIKE target_table INCLUDING ALL — αντιγράφει προεπιλογές, ευρετήρια, περιορισμούς,...

Αλλά εδώ πρέπει να καταλάβετε ότι αν δημιουργήσατε Εισαγάγετε αμέσως πίνακα με ευρετήρια, τότε τα δεδομένα θα χρειαστούν περισσότερο χρόνο για να φορτωθούνπαρά αν πρώτα γεμίσετε τα πάντα και μόνο στη συνέχεια βάλετε τα ευρετήρια - δείτε πώς το κάνει αυτό ως παράδειγμα pg_dump.

Σε γενικές γραμμές, RTFM!

2. Πώς να γράψετε;

Επιτρέψτε μου να πω απλώς - χρησιμοποιήστε το COPY- ροή αντί για "πακέτο" INSERT, επιτάχυνση κατά καιρούς. Μπορείτε ακόμη και απευθείας από ένα προ-δημιουργημένο αρχείο.

3. Πώς γίνεται η επεξεργασία;

Λοιπόν, ας αφήσουμε την εισαγωγή μας να μοιάζει κάπως έτσι:

• έχετε έναν πίνακα με δεδομένα πελάτη αποθηκευμένο στη βάση δεδομένων σας Ρεκόρ 1 εκατομμυρίου
• κάθε μέρα ένας πελάτης σας στέλνει ένα νέο πλήρης "εικόνα"
• εκ πείρας ξέρεις ότι κατά καιρούς δεν αλλάζουν περισσότερες από 10 χιλιάδες εγγραφές

Ένα κλασικό παράδειγμα μιας τέτοιας κατάστασης είναι Βάση KLADR — υπάρχουν πολλές διευθύνσεις συνολικά, αλλά σε κάθε εβδομαδιαία μεταφόρτωση υπάρχουν ελάχιστες αλλαγές (μετονομασία οικισμών, συνδυασμός δρόμων, εμφάνιση νέων σπιτιών) ακόμη και σε εθνική κλίμακα.

3.1. Αλγόριθμος πλήρους συγχρονισμού

Για απλότητα, ας πούμε ότι δεν χρειάζεται καν να αναδιαρθρώσετε τα δεδομένα - απλώς φέρτε τον πίνακα στην επιθυμητή μορφή, δηλαδή:

• αφαιρέστε όλα όσα δεν υπάρχουν πια
• ανανέωση όλα όσα ήδη υπήρχαν και πρέπει να ενημερωθούν
• ένθετο όλα όσα δεν έχουν γίνει ακόμα

Γιατί να γίνουν οι πράξεις με αυτή τη σειρά; Επειδή έτσι το μέγεθος του τραπεζιού θα μεγαλώσει ελάχιστα (θυμηθείτε το MVCC!).

ΔΙΑΓΡΑΦΗ ΑΠΟ dst

Όχι, φυσικά μπορείτε να τα βγάλετε πέρα ​​με δύο μόνο λειτουργίες:

• αφαιρέστε (DELETE) τα πάντα γενικά
• ένθετο όλα από τη νέα εικόνα

Αλλά ταυτόχρονα, χάρη στο MVCC, Το μέγεθος του πίνακα θα αυξηθεί ακριβώς δύο φορές! Η λήψη +1 εκατομμυρίων εικόνων εγγραφών στον πίνακα λόγω μιας ενημέρωσης 10K είναι τόσο πλεονασμός...

ΚΟΛΟΦΟΡΟ δστ

Ένας πιο έμπειρος προγραμματιστής γνωρίζει ότι ολόκληρο το tablet μπορεί να καθαριστεί αρκετά φθηνά:

• σαφής (TRUNCATE) ολόκληρο το τραπέζι
• ένθετο όλα από τη νέα εικόνα

Η μέθοδος είναι αποτελεσματική, μερικές φορές αρκετά εφαρμόσιμο, αλλά υπάρχει ένα πρόβλημα... Θα προσθέτουμε εγγραφές 1 εκατομμυρίου για μεγάλο χρονικό διάστημα, επομένως δεν έχουμε την πολυτέλεια να αφήσουμε τον πίνακα άδειο για όλο αυτό το διάστημα (όπως θα συμβεί χωρίς να το τυλίξουμε σε μία μόνο συναλλαγή).

Που σημαίνει:

• ξεκινάμε μακροχρόνια συναλλαγή
• TRUNCATE επιβάλλει Αποκλειστική πρόσβαση-μπλοκάρισμα
• κάνουμε την εισαγωγή για μεγάλο χρονικό διάστημα, και όλοι οι άλλοι αυτή τη στιγμή δεν μπορεί καν SELECT

Κάτι δεν πάει καλά...

ΑΛΛΑΓΗ ΠΙΝΑΚΑ… ΜΕΤΟΝΟΜΑΣΙΑ… / ΑΠΟΡΡΙΨΗ ΠΙΝΑΚΑ…

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

• ακόμα επίσης Αποκλειστική πρόσβαση, αν και σημαντικά λιγότερο χρόνο
• όλα τα σχέδια/στατιστικά ερωτημάτων για αυτόν τον πίνακα επαναφέρονται, πρέπει να εκτελέσετε το ANALYZE
• όλα τα ξένα κλειδιά είναι σπασμένα (FK) στο τραπέζι

Υπήρχε μια ενημερωμένη έκδοση κώδικα WIP από τον Simon Riggs που πρότεινε τη δημιουργία ALTER-μια λειτουργία αντικατάστασης του σώματος του πίνακα σε επίπεδο αρχείου, χωρίς να αγγίξουμε στατιστικά και FK, αλλά δεν συγκέντρωσε απαρτία.

ΔΙΑΓΡΑΦΗ, ΕΝΗΜΕΡΩΣΗ, ΕΙΣΑΓΩΓΗ

Έτσι, συμβιβαζόμαστε με την επιλογή μη αποκλεισμού των τριών λειτουργιών. Σχεδόν τρεις... Πώς να το κάνετε αυτό πιο αποτελεσματικά;

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
BEGIN;

-- создаем временную таблицу с импортируемыми данными
CREATE TEMPORARY TABLE tmp(
  LIKE dst INCLUDING INDEXES -- по образу и подобию, вместе с индексами
) ON COMMIT DROP; -- за рамками транзакции она нам не нужна

-- быстро-быстро вливаем новый образ через COPY
COPY tmp FROM STDIN;
-- ...
-- .

-- удаляем отсутствующие
DELETE FROM
  dst D
USING
  dst X
LEFT JOIN
  tmp Y
    USING(pk1, pk2) -- поля первичного ключа
WHERE
  (D.pk1, D.pk2) = (X.pk1, X.pk2) AND
  Y IS NOT DISTINCT FROM NULL; -- "антиджойн"

-- обновляем оставшиеся
UPDATE
  dst D
SET
  (f1, f2, f3) = (T.f1, T.f2, T.f3)
FROM
  tmp T
WHERE
  (D.pk1, D.pk2) = (T.pk1, T.pk2) AND
  (D.f1, D.f2, D.f3) IS DISTINCT FROM (T.f1, T.f2, T.f3); -- незачем обновлять совпадающие

-- вставляем отсутствующие
INSERT INTO
  dst
SELECT
  T.*
FROM
  tmp T
LEFT JOIN
  dst D
    USING(pk1, pk2)
WHERE
  D IS NOT DISTINCT FROM NULL;

COMMIT;

3.2. Εισαγωγή μετεπεξεργασίας

Στο ίδιο KLADR, όλες οι αλλαγμένες εγγραφές πρέπει επιπλέον να εκτελούνται μέσω μετα-επεξεργασίας - κανονικοποιούνται, επισημαίνονται οι λέξεις-κλειδιά και περιορίζονται στις απαιτούμενες δομές. Μα πώς ξέρεις... τι ακριβώς άλλαξεχωρίς να περιπλέκεται ο κώδικας συγχρονισμού, ιδανικά χωρίς να τον αγγίξω καθόλου;

Εάν μόνο η διεργασία σας έχει πρόσβαση εγγραφής τη στιγμή του συγχρονισμού, τότε μπορείτε να χρησιμοποιήσετε έναν κανόνα που θα συλλέξει όλες τις αλλαγές για εμάς:

-- целевые таблицы
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);

-- таблицы с историей изменений
CREATE TABLE kladr$log(
  ro kladr, -- тут лежат целые образы записей старой/новой
  rn kladr
);

CREATE TABLE kladr_house$log(
  ro kladr_house,
  rn kladr_house
);

-- общая функция логирования изменений
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
  dst varchar = TG_TABLE_NAME || '$log';
  stmt text = '';
BEGIN
  -- проверяем необходимость логгирования при обновлении записи
  IF TG_OP = 'UPDATE' THEN
    IF NEW IS NOT DISTINCT FROM OLD THEN
      RETURN NEW;
    END IF;
  END IF;
  -- создаем запись лога
  stmt = 'INSERT INTO ' || dst::text || '(ro,rn)VALUES(';
  CASE TG_OP
    WHEN 'INSERT' THEN
      EXECUTE stmt || 'NULL,$1)' USING NEW;
    WHEN 'UPDATE' THEN
      EXECUTE stmt || '$1,$2)' USING OLD, NEW;
    WHEN 'DELETE' THEN
      EXECUTE stmt || '$1,NULL)' USING OLD;
  END CASE;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Τώρα μπορούμε να εφαρμόσουμε έναυσμα πριν ξεκινήσουμε τον συγχρονισμό (ή να τους ενεργοποιήσουμε μέσω ALTER TABLE ... ENABLE TRIGGER ...):

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr_house
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

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

3.3. Εισαγωγή συνδεδεμένων συνόλων

Παραπάνω εξετάσαμε περιπτώσεις όπου οι δομές δεδομένων της πηγής και του προορισμού είναι ίδιες. Τι γίνεται όμως αν η μεταφόρτωση από εξωτερικό σύστημα έχει διαφορετική μορφή από τη δομή αποθήκευσης στη βάση δεδομένων μας;

Ας πάρουμε ως παράδειγμα την αποθήκευση των πελατών και των λογαριασμών τους, την κλασική επιλογή "πολλοί προς έναν":

CREATE TABLE client(
  client_id
    serial
      PRIMARY KEY
, inn
    varchar
      UNIQUE
, name
    varchar
);

CREATE TABLE invoice(
  invoice_id
    serial
      PRIMARY KEY
, client_id
    integer
      REFERENCES client(client_id)
, number
    varchar
, dt
    date
, sum
    numeric(32,2)
);

Αλλά η λήψη από μια εξωτερική πηγή έρχεται σε εμάς με τη μορφή "όλα σε ένα":

CREATE TEMPORARY TABLE invoice_import(
  client_inn
    varchar
, client_name
    varchar
, invoice_number
    varchar
, invoice_dt
    date
, invoice_sum
    numeric(32,2)
);

Προφανώς, τα δεδομένα πελατών μπορούν να αντιγραφούν σε αυτήν την έκδοση και η κύρια εγγραφή είναι "λογαριασμός":

0123456789;Вася;A-01;2020-03-16;1000.00
9876543210;Петя;A-02;2020-03-16;666.00
0123456789;Вася;B-03;2020-03-16;9999.00

Για το μοντέλο, απλώς θα εισαγάγουμε τα δεδομένα δοκιμής μας, αλλά θυμηθείτε - COPY πιο αποτελεσματικό!

INSERT INTO invoice_import
VALUES
  ('0123456789', 'Вася', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'Петя', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'Вася', 'B-03', '2020-03-16', 9999.00);

Αρχικά, ας επισημάνουμε εκείνες τις «περικοπές» στις οποίες αναφέρονται τα «γεγονότα» μας. Στην περίπτωσή μας, τα τιμολόγια αναφέρονται σε πελάτες:

CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
  client_inn inn
, client_name "name"
FROM
  invoice_import;

Για να συσχετίσουμε σωστά τους λογαριασμούς με τα αναγνωριστικά πελατών, πρέπει πρώτα να βρούμε ή να δημιουργήσουμε αυτά τα αναγνωριστικά. Ας προσθέσουμε πεδία κάτω από αυτά:

ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;

Ας χρησιμοποιήσουμε τη μέθοδο συγχρονισμού πινάκων που περιγράφεται παραπάνω με μια μικρή τροποποίηση - δεν θα ενημερώσουμε ούτε θα διαγράψουμε τίποτα στον πίνακα προορισμού, επειδή εισάγουμε πελάτες "μόνο για προσθήκη":

-- проставляем в таблице импорта ID уже существующих записей
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  client D
WHERE
  T.inn = D.inn; -- unique key

-- вставляем отсутствовавшие записи и проставляем их ID
WITH ins AS (
  INSERT INTO client(
    inn
  , name
  )
  SELECT
    inn
  , name
  FROM
    client_import
  WHERE
    client_id IS NULL -- если ID не проставился
  RETURNING *
)
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  ins D
WHERE
  T.inn = D.inn; -- unique key

-- проставляем ID клиентов у записей счетов
UPDATE
  invoice_import T
SET
  client_id = D.client_id
FROM
  client_import D
WHERE
  T.client_inn = D.inn; -- прикладной ключ

Στην πραγματικότητα, όλα είναι μέσα invoice_import Τώρα έχουμε συμπληρώσει το πεδίο επαφής client_id, με το οποίο θα εισάγουμε το τιμολόγιο.

Πηγή: www.habr.com