December 18th, 2006 by depesz | Tags: | 2 comments »
Did it help? If yes - maybe you can help me?

teoretycznie dodanie kolumny do tabeli nie jest problemem. od daaaaaaaaaaaaaawna istnieje ALTER TABLE ADD COLUMN. czy jednak jest to zawsze bezproblemowe?
niestety nie.
otóż – dodanie pola do tabeli zakłada na nią (w całości) exclusive locka.
dla standardowego:

ALTER TABLE t ADD COLUMN c INT8;

to nie problem – taki ALTER trwa moment.
ale co jeśli robimy:

ALTER TABLE t ADD COLUMN c INT8 NOT NULL DEFAULT 123;

tu pojawia się problem.
po pierwsze – not null – wymusza sprawdzenie zawartości bazy.
po drugie – ważniejsze – wyspecyfikowanie "default" przy dodawaniu pola automatycznie wstawi wartość domyślną do wszystkich rekordów obecnie w tabeli.
zasadniczo – super. o to chodzi. ale jak sobie nałożymy na to fakt iż ALTER TABLE lockuje tabelę – oops. recepta na kłopoty.
w szczególności – mając tabelę typu kilka milionów rekordów, na której non stop ktoś pracuje (np. dosyć aktywny serwis www) – zrobienie na niej takiego ALTERa to realnie sprawę ujmując wyłączenie ta
beli i serwisu.
jak więc zrobić dodanie z wyspecyfikowaniem w sposób słuszny?
no cóż – trzeba robić to krokami:
po pierwsze:

ALTER TABLE t ADD COLUMN c INT8;

to się wykona błyskawicznie.
potem (ale jako oddzielne zapytanie!):

ALTER TABLE t ALTER COLUMN c SET DEFAULT 123;

to też pójdzie szybko bo niczego nam nie zaktualizuje. po prostu – nowo wstawiane rekordy będą miały defaulta.
teraz – trzeba zaktualizować starsze. najprościej:

UPDATE t SET c = 123 WHERE c IS NULL;

w szczególności – jak danych jest więcej niż mało (powiedzmy powyżej 100,000 rekordów), to warto rozbić to na kilka oddzielnych zapytań. np:

UPDATE t SET c = 123 WHERE c IS NULL AND id BETWEEN 1 AND 100000;
UPDATE t SET c = 123 WHERE c IS NULL AND id BETWEEN 100001 AND 200000;

itd.
oczywiście – nie można do tego celu użyć funkcji – cała idea tego rozbijania polega na tym by nie robić tego wszystkiego w jednej, olbrzymiej, transakcji!.
no i na koniec pozostaje:

ALTER TABLE t ALTER COLUMN c SET NOT NULL;

to chwilę potrwa – postgres musi sprawdzić dane, ale i tak jest to krótsze i mniej blokujęce niż robienie wszystkiego za jednym zamachem.
oczywiście w tej chwili ktoś może się odezwać i powiedzieć, że w bazie <xxx> to jest prostsze, nie trzeba nic rozbijać, bla bla bla. fakt. w postgresie też nie trzeba – to co pokazałem to jedynie hint jak obejść blokadę przy dodawaniu pól z wartościami domyślnymi przy sporych tabelkach. można to zrobić jednym zapytaniem. i też zadziała. po prostu czasem nie chcemy/nie możemy mieć locka na taki czas jaki jest potrzebny ALTERowi 🙂

  1. 2 comments

  2. # Dominik
    Jan 11, 2007

    Dobry wieczór,

    Właśnie się bawiłem w dodawanie kolumny tym sposobem, napotkałem jednak pewien problem. W tle chodził proces, który non stop coś do modyfikowanej tabeli wstawiał. Dodałem kolumnę, dodałem defaulta i … wiersze wstawione przez proces miały ciągle wartość null 🙁 autocommit był włączony, Postgres 8.1.

    Pozdrawiam,
    Dominik

  3. Jan 11, 2007

    czy to odbywało się w jednej transakcji? niestety to fakt – tak się dzieje. niestety – nie wydaje mi się by dodanie kolumny “po całości jednym poleceniem” to zmieniła.

Leave a comment