Unikaj wyjątkowych naruszeń transakcji atomowych

Czy możliwe jest utworzenie transakcji atomowej w PostgreSQL?

Rozważ, że mam kategorię tabeli z tymi wierszami:

id|name
--|---------
1 |'tablets'
2 |'phones'

A nazwa kolumny ma unikalne ograniczenie.

Jeśli spróbuję:

BEGIN;
update "category" set name = 'phones' where id = 1;
update "category" set name = 'tablets' where id = 2;
COMMIT;

Dostaję:

ERROR:  duplicate key value violates unique constraint "category_name_key"
DETAIL:  Key (name)=(tablets) already exists.

Oprócz tego, co zapewnia @Craig (i poprawia niektóre z nich):

Skuteczne Postgresa 9,4 , UNIQUE, PRIMARY KEYoraz EXCLUDEograniczenia są sprawdzane natychmiast po każdym rzędzie , gdy określona NOT DEFERRABLE. Różni się to od innych NOT DEFERRABLEograniczeń (obecnie tylko REFERENCES(klucz obcy)), które są sprawdzane po każdej instrukcji . Wszystko to wypracowaliśmy pod tym powiązanym pytaniem dotyczącym SO:

• Określone ograniczenie PONIŻSZE NATYCHMIASTOWE NATYCHMIASTOWE POWYŻSZE SĄ PONIESIONE?

Jest to nie tyle dla UNIQUE(lub PRIMARY KEYlub EXCLUDE) ograniczenie się DEFERRABLE, aby Państwa przedstawiony kod z wielu instrukcji pracy.

I można nie używać ALTER TABLE ... ALTER CONSTRAINTdo tego celu. Według dokumentacji:

ALTER CONSTRAINT

Ten formularz zmienia atrybuty wcześniej utworzonego ograniczenia. Obecnie tylko ograniczenia klucza obcego mogą zostać zmienione .

Odważny nacisk moje. Zamiast tego użyj:

ALTER TABLE t
   DROP CONSTRAINT category_name_key
 , ADD  CONSTRAINT category_name_key UNIQUE(name) DEFERRABLE;

Upuść i dodaj ograniczenie z powrotem w pojedynczej instrukcji, aby nikt nie miał okna czasowego na przekradanie się w obrażających się wierszach. W przypadku dużych tabel kuszące byłoby zachowanie jakiegoś unikalnego indeksu, ponieważ jego usunięcie i ponowne utworzenie jest kosztowne. Niestety nie wydaje się to możliwe w przypadku standardowych narzędzi (jeśli masz na to rozwiązanie, daj nam znać!):

• Upuść klucz podstawowy bez upuszczania indeksu

Dla pojedynczej instrukcji wystarczającej do odłożenia ograniczenia wystarczy:

UPDATE category c
SET    name = c_old.name
FROM   category c_old
WHERE  c.id     IN (1,2)
AND    c_old.id IN (1,2)
AND    c.id <> c_old.id;

Zapytanie z CTE również jest pojedynczą instrukcją:

WITH x AS (
    UPDATE category SET name = 'phones' WHERE id = 1
    )
UPDATE category SET name = 'tablets' WHERE id = 2;

Jednak w przypadku kodu zawierającego wiele instrukcji musisz (dodatkowo) odroczyć ograniczenie - lub zdefiniować je, ponieważ INITIALLY DEFERREDjedno z nich jest zwykle droższe niż powyższe. Ale spakowanie wszystkiego w jedno zdanie może nie być łatwo wykonalne.

BEGIN;
SET CONSTRAINTS category_name_key DEFERRED;
UPDATE category SET name = 'phones'  WHERE id = 1;
UPDATE category SET name = 'tablets' WHERE id = 2;
COMMIT;

Należy jednak pamiętać o ograniczeniach związanych z FOREIGN KEYograniczeniami. Według dokumentacji:

Odwołane kolumny muszą być kolumnami nieodroczalnego ograniczenia unikatowego lub klucza podstawowego w tabeli, do której następuje odwołanie.

Więc nie możesz mieć obu jednocześnie.

Jak rozumiem, twoim problemem jest to, że ograniczenie jest sprawdzane po każdej instrukcji, ale chcesz, aby było sprawdzane na końcu transakcji, więc porównuje stan przed do stanu po, ignorując stany pośrednie.

Jeśli tak, jest to możliwe z odroczonym ograniczeniem .

Zobacz SET CONSTRAINTSi DEFERRABLEograniczenia zgodnie z dokumentacją w CREATE TABLE.

Należy pamiętać, że odroczone ograniczenia wiążą się z kosztami - system musi przechowywać ich listę w celu sprawdzenia w czasie zatwierdzania, więc nie są one dobre dla transakcji, które dokonują ogromnych zestawów zmian. Są również wolniejsze do sprawdzenia.

Myślę więc, że prawdopodobnie chcesz:

ALTER TABLE mytable ALTER CONSTRAINT category_name_key DEFERRABLE;

Zauważ, że wydaje się, że istnieją ograniczenia w ALTER TABLEustawianiu ograniczeń dla DEFERRABLE; być może będziesz musiał zamiast tego DROPponownie ADDwprowadzić ograniczenie.