Aktualizacja Postgres… LIMIT 1

Mam bazę danych Postgres, która zawiera szczegółowe informacje o klastrach serwerów, takie jak status serwera („aktywny”, „tryb gotowości” itp.). Aktywne serwery w dowolnym momencie mogą wymagać przełączenia awaryjnego do trybu gotowości i nie dbam o to, który tryb gotowości jest używany w szczególności.

Chcę, aby zapytanie do bazy danych zmieniło stan gotowości - JUST ONE - i zwróci adres IP serwera, który będzie używany. Wybór może być dowolny: ponieważ status serwera zmienia się wraz z zapytaniem, nie ma znaczenia, który tryb gotowości zostanie wybrany.

Czy mogę ograniczyć moje zapytanie do jednej aktualizacji?

Oto co mam do tej pory:

UPDATE server_info SET status = 'active' 
WHERE status = 'standby' [[LIMIT 1???]] 
RETURNING server_ip;

Postgres tego nie lubi. Co mogę zrobić inaczej?

Bez równoczesnego dostępu do zapisu

Zmaterializuj zaznaczenie w CTE i dołącz do niego w FROMklauzuli UPDATE.

WITH cte AS (
   SELECT server_ip          -- pk column or any (set of) unique column(s)
   FROM   server_info
   WHERE  status = 'standby'
   LIMIT  1                  -- arbitrary pick (cheapest)
   )
UPDATE server_info s
SET    status = 'active' 
FROM   cte
WHERE  s.server_ip = cte.server_ip
RETURNING server_ip;

Pierwotnie miałem tutaj proste podzapytanie, ale może to pomijaćLIMIT niektóre plany zapytań, jak zauważył Feike :

Planista może wybrać wygenerowanie planu, który wykonuje zagnieżdżoną pętlę nad LIMITingpodzapytaniem, powodując więcej UPDATEsniż LIMITnp .:

 Update on buganalysis [...] rows=5
   ->  Nested Loop
         ->  Seq Scan on buganalysis
         ->  Subquery Scan on sub [...] loops=11
               ->  Limit [...] rows=2
                     ->  LockRows
                           ->  Sort
                                 ->  Seq Scan on buganalysis

Odtwarzanie przypadku testowego

Sposobem na naprawienie powyższego było zawinięcie LIMITpodzapytania we własne CTE, ponieważ CTE jest zmaterializowane, nie zwraca różnych wyników dla różnych iteracji zagnieżdżonej pętli.

Lub użyj słabo skorelowanego podzapytania dla prostego przypadku zLIMIT 1. Prostsze, szybsze:

UPDATE server_info
SET    status = 'active' 
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         LIMIT  1
         )
RETURNING server_ip;

Z jednoczesnym dostępem do zapisu

Zakładając domyślny poziom izolacjiREAD COMMITTED dla tego wszystkiego. Zaostrzone poziomy izolacji ( REPEATABLE READi SERIALIZABLE) mogą nadal powodować błędy serializacji. Widzieć:

• WYBIERZ ... DO AKTUALIZACJI POMOC ZABLOKOWANY W POWTARZALNYCH transakcjach CZYTAJ

Przy jednoczesnym obciążeniu zapisu dodaj, FOR UPDATE SKIP LOCKEDaby zablokować wiersz, aby uniknąć warunków wyścigu. SKIP LOCKEDzostał dodany w Postgres 9.5 , dla starszych wersji patrz poniżej. Instrukcja:

Za pomocą SKIP LOCKED, wszystkie wybrane wiersze, których nie można natychmiast zablokować, są pomijane. Pomijanie zablokowanych wierszy zapewnia niespójny widok danych, więc nie jest to odpowiednie do prac ogólnego przeznaczenia, ale można go użyć, aby uniknąć rywalizacji o blokadę, gdy wielu konsumentów uzyskuje dostęp do tabeli podobnej do kolejki.

UPDATE server_info
SET    status = 'active' 
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         LIMIT  1
         FOR    UPDATE SKIP LOCKED
         )
RETURNING server_ip;

Jeśli nie ma kwalifikującego się, odblokowanego wiersza, w tym zapytaniu nic się nie dzieje (żaden wiersz nie jest aktualizowany), a wynik jest pusty. W przypadku bezkrytycznych operacji oznacza to, że skończyłeś.

Jednak współbieżne transakcje mogą mieć zablokowane wiersze, ale nie kończą aktualizacji ( ROLLBACKlub z innych powodów). Aby mieć pewność, wykonaj ostatnią kontrolę:

SELECT NOT EXISTS (
   SELECT 1
   FROM   server_info
   WHERE  status = 'standby'
   );

SELECTwidzi również zablokowane rzędy. Wile, które nie zwraca true, jeden lub więcej wierszy jest nadal przetwarzanych, a transakcje można nadal wycofywać. (W międzyczasie dodano nowe wiersze.) Poczekaj chwilę, a następnie zapętl dwa kroki: ( UPDATEdopóki nie odzyskasz żadnego wiersza; SELECT...), aż dostaniesz true.

Związane z:

• AKTUALIZACJA Atomowa .. WYBIERZ w Postgres

Bez SKIP LOCKEDw PostgreSQL 9.4 lub starszym

UPDATE server_info
SET    status = 'active' 
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         LIMIT  1
         FOR    UPDATE
         )
RETURNING server_ip;

Równoczesne transakcje próbujące zablokować ten sam wiersz są blokowane, dopóki pierwszy nie zwolni blokady.

Jeśli pierwszy został wycofany, następna transakcja bierze blokadę i przebiega normalnie; inni w kolejce czekają.

Jeśli pierwszy zatwierdzony, WHEREwarunek jest ponownie oceniany, a jeśli już nie TRUEjest ( statuszmienił się), CTE (nieco zaskakująco) nie zwraca wiersza. Nic się nie dzieje. Jest to pożądane zachowanie, gdy wszystkie transakcje chcą zaktualizować ten sam wiersz .
Ale nie przy każdej transakcji chce zaktualizować do następnego wiersza . A ponieważ chcemy po prostu zaktualizować dowolny (lub losowy ) wiersz , nie ma sensu w ogóle czekać.

Możemy odblokować sytuację za pomocą blokad doradczych :

UPDATE server_info
SET    status = 'active' 
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         AND    pg_try_advisory_xact_lock(id)
         LIMIT  1
         FOR    UPDATE
         )
RETURNING server_ip;

W ten sposób następny niezablokowany jeszcze wiersz zostanie zaktualizowany. Każda transakcja otrzymuje nowy wiersz do pracy. Miałem pomoc z Czech Postgres Wiki w tej sztuczce.

idbędący dowolną unikalną bigintkolumną (lub dowolnym typem z niejawną obsadą, taką jak int4lub int2).

_{Jeśli blokady doradcze są używane jednocześnie dla wielu tabel w bazie danych, należy jednoznacznie ustalić, że pg_try_advisory_xact_lock(tableoid::int, id)- idjest integertutaj wyjątkowy .
Ponieważ tableoidjest to bigintilość, teoretycznie może się przepełnić integer. Jeśli jesteś wystarczająco paranoikiem, użyj (tableoid::bigint % 2147483648)::intzamiast tego - pozostawiając teoretyczną „kolizję skrótu” dla prawdziwie paranoicznej ...}

Ponadto Postgres może testować WHEREwarunki w dowolnej kolejności. To mogło testować pg_try_advisory_xact_lock()i uzyskać blokadę przed status = 'standby' , co może wiązać się z dodatkowymi zamkami doradczych na niepowiązanych wierszy, w których status = 'standby'nie jest to prawdą. Powiązane pytanie dotyczące SO:

• Postgres pg_try_advisory_lock blokuje wszystkie rekordy

Zazwyczaj możesz to zignorować. Aby zagwarantować, że tylko kwalifikujące się wiersze są zablokowane, możesz zagnieździć predykat (y) w CTE jak wyżej lub podkwerendę z OFFSET 0hackem (zapobiega wstawianiu) . Przykład:

• Umieścić pg_try_advisory_xact_lock () w zagnieżdżonym podzapytaniu?

Lub (tańsze w przypadku skanowania sekwencyjnego) zagnieżdżają warunki w CASEinstrukcji, takiej jak:

WHERE  CASE WHEN status = 'standby' THEN pg_try_advisory_xact_lock(id) END

JednakCASE Sztuką byłoby również zachować Postgresa z użyciem indeksu status. Jeśli taki indeks jest dostępny, na początku nie potrzebujesz dodatkowego zagnieżdżania: tylko skanowane wiersze zostaną zablokowane.

Ponieważ nie możesz mieć pewności, że indeks jest używany przy każdym połączeniu, możesz po prostu:

WHERE  status = 'standby'
AND    CASE WHEN status = 'standby' THEN pg_try_advisory_xact_lock(id) END

CASEJest logicznie zbędny, ale serwery omawiany cel.

Jeśli polecenie jest częścią długiej transakcji, rozważ blokady na poziomie sesji, które można (i trzeba) zwolnić ręcznie. Możesz więc odblokować, jak tylko skończysz z zablokowanym rzędem: pg_try_advisory_lock()ipg_advisory_unlock() . Instrukcja:

Po uzyskaniu na poziomie sesji blokada doradcza jest utrzymywana do momentu jawnego zwolnienia lub zakończenia sesji.

Związane z:

• Optymalizacja jednoczesnych aktualizacji w Postgres