Obsługa błędów podczas ładowania danych ( ON CONFLICT)

Witaj w samym sercu dramatycznych scenariuszy masowego ładowania danych! Dzisiaj nauczymy się skutecznie radzić sobie z błędami pojawiającymi się podczas ładowania danych, używając konstrukcji ON CONFLICT. To trochę jak włączenie autopilota w samolocie: nawet jeśli coś pójdzie nie tak, będziesz wiedzieć, co zrobić, żeby nie było katastrofy. Lecimy rozgryźć triki PostgreSQL!

Nikt nie lubi niespodzianek, szczególnie gdy dane nie chcą się załadować! W procesach masowego ładowania możesz natknąć się na kilka typowych problemów:

• Duplikacja danych. Na przykład, jeśli w tabeli masz ograniczenie UNIQUE, a Twój plik z danymi jest pełen powtórek.
• Konflikty z ograniczeniami. Na przykład, spróbuj załadować pustą wartość do kolumny z ograniczeniem NOT NULL. Efekt? Błąd. PostgreSQL zawsze jest sztywny w takich sytuacjach.
• Duplikujące się kluczowe informacje. Tabela może już zawierać dane z tymi samymi identyfikatorami, co Twój plik CSV.

Sprawdźmy, jak uniknąć tych "podwodnych kamieni" z ON CONFLICT.

Użycie ON CONFLICT do obsługi błędów

Składnia konstrukcji ON CONFLICT pozwala określić, co zrobić w przypadku konfliktu z ograniczeniami (np. UNIQUE lub PRIMARY KEY). PostgreSQL daje Ci możliwość albo zaktualizowania istniejących danych, albo zignorowania konfliktującego wiersza.

Tak wygląda podstawowa składnia konstrukcji ON CONFLICT:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target)
DO UPDATE SET column1 = new_value1, column2 = new_value2;

Możesz zamienić DO UPDATE na DO NOTHING, jeśli chcesz po prostu zignorować konflikt.

Przykład: aktualizacja danych przy konflikcie

Załóżmy, że mamy tabelę students:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT
);

Teraz chcemy załadować nowe dane, ale niektóre z nich już są w bazie:

INSERT INTO students (id, name, age)
VALUES 
    (1, 'Peter', 22),  -- Ten student już jest
    (2, 'Anna', 20),  -- Nowy student
    (3, 'Mal', 25) -- Nowy student
ON CONFLICT (id) DO UPDATE SET 
    name = EXCLUDED.name, 
    age = EXCLUDED.age;

W tym przykładzie, jeśli już jest student z ID, który chcemy dodać, jego dane zostaną zaktualizowane:

ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name, 
    age = EXCLUDED.age;

Zwróć uwagę na magiczne słowo EXCLUDED. Oznacza ono "wartości, które próbowałeś wstawić, ale zostały wykluczone z powodu konfliktu".

Efekt:

• Student z id = 1 zaktualizuje swoje dane (imię i wiek).
• Studenci z id = 2 i id = 3 zostaną dodani do tabeli.

Przykład: ignorowanie konfliktów

Jeśli nie chcesz aktualizować danych, a tylko zignorować wiersze, które powodują konflikt, użyj DO NOTHING:

INSERT INTO students (id, name, age)
VALUES 
    (1, 'Peter', 22),  -- Ten student już jest
    (2, 'Anna', 20),  -- Nowy student
    (3, 'Mal', 25) -- Nowy student
ON CONFLICT (id) DO NOTHING;

Teraz konfliktujące wiersze po prostu nie zostaną wstawione, a reszta spokojnie zamieszka w Twojej bazie.

Logowanie błędów

Czasem ignorowanie lub aktualizacja to za mało. Na przykład, musisz zapisywać konflikty do późniejszej analizy. Możemy stworzyć specjalną tabelę do logowania błędów:

CREATE TABLE conflict_log (
    conflict_time TIMESTAMP DEFAULT NOW(),
    id INT,
    name TEXT,
    age INT,
    conflict_reason TEXT
);

Potem dodajemy obsługę błędów z logowaniem:

INSERT INTO students (id, name, age)
VALUES 
    (1, 'Peter', 22), 
    (2, 'Anna', 20), 
    (3, 'Mal', 25)
ON CONFLICT (id) DO UPDATE SET 
    name = EXCLUDED.name, 
    age = EXCLUDED.age
RETURNING EXCLUDED.id, EXCLUDED.name, EXCLUDED.age
INTO conflict_log;

Ostatni przykład zadziała tylko wewnątrz procedur składowanych. Jak to dokładnie działa, dowiesz się, gdy będziemy przerabiać PL-SQL. Trochę wybiegłem w przyszłość, ale chciałem pokazać jeszcze jeden sposób rozwiązania konfliktu ładowania danych: logowanie wszystkich problematycznych wierszy.

Teraz możesz analizować przyczyny konfliktów. Ta technika jest szczególnie przydatna w złożonych systemach, gdzie ważne jest zachowanie "śladów" podczas masowych ładowań danych.

Praktyczny przykład

Połączmy całą naszą wiedzę w jednym prostym zadaniu. Wyobraź sobie, że masz plik CSV z aktualizacjami studentów, który chcesz załadować do tabeli:

Plik students_update.csv

Ładowanie danych i obsługa konfliktów

1. Najpierw tworzymy tymczasową tabelę tmp_students:

CREATE TEMP TABLE tmp_students (
  id   INTEGER,
  name TEXT,
  age  INTEGER
);

2. Ładujemy dane z pliku używając \COPY:

\COPY tmp_students FROM 'students_update.csv' DELIMITER ',' CSV HEADER

3. Wstawiamy dane z tymczasowej tabeli do stałej za pomocą INSERT ON CONFLICT:

INSERT INTO students (id, name, age)
SELECT id, name, age FROM tmp_students
ON CONFLICT (id) DO UPDATE
  SET name = EXCLUDED.name,
      age = EXCLUDED.age;

Teraz wszystkie dane, włącznie z aktualizacjami (wiersz z id = 1), zostały pomyślnie załadowane.

Typowe błędy i jak ich unikać

Błędy zdarzają się nawet najbardziej doświadczonym programistom, ale wiedząc, jak ich unikać, możesz zaoszczędzić godziny (a może i dni) nerwów.

• Konflikt z ograniczeniem UNIQUE. Upewnij się, że wskazałeś poprawne pole w ON CONFLICT. Na przykład, jeśli wskażesz zły klucz (id zamiast email), PostgreSQL po prostu powie "do widzenia" Twojemu zapytaniu.
• Nieprawidłowe użycie EXCLUDED. Ten alias odnosi się tylko do wartości przekazanych w bieżącym zapytaniu. Nie próbuj używać go w innych kontekstach.
• Pominięcie kolumn. Upewnij się, że wszystkie kolumny wskazane w SET istnieją w tabeli. Na przykład, jeśli dodasz SET non_existing_column = 'value', dostaniesz błąd.

Użycie ON CONFLICT sprawia, że masowe ładowanie danych w PostgreSQL jest elastyczne i bezpieczne. Możesz nie tylko uniknąć wywalenia zapytań przez konflikty, ale też kontrolować, jak dokładnie obsługujesz swoje dane. Twoi użytkownicy (i serwery!) będą Ci wdzięczni.