Lateral join w PostgreSQLu

Od wersji 9.3 w zapytaniach typu SELECT pojawiło się nowe słowo kluczowe LATERAL. Główny format zapytania przyjmuję postać:

SELECT * FROM something [INNER/LEFT] JOIN LATERAL subquery;

Dla obrazowania problemu stworzyłam kilka małych tabel: users (imię dziecka z informacją o kraju z którego pochodzi), toys (nazwa zabawi z jego ceną), user_to_toys (połączenie dwóch poprzednich tabel relacją wiele do wielu - które dziecko ma jaką zabawkę), country_rise (procentowa wartość podwyżki cen zabawek dla danego kraju).

Interesują mnie następujące informacje:

• które dziecko jakie dostało zabawki, 
• ile było tych zabawek, 
• ile wyniósł koszt kupionych zabawek,
• ile rodzice by zapłacili po podwyżce.

W głównym podzapytaniu wyciągam informacje o dzieciach, zabawkach i sumie obecnych kosztów tych zabawek:

select 
     u.id, 
     u.name, 
     u.country, 
     string_agg(t.name, ',') as toys_names, 
     sum(t.price) as price_sum, 
     count(t.id) as count_toys 
   from users u 
   LEFT JOIN user_to_toy ut ON u.id = ut.userid 
   LEFT JOIN toys t ON t.id = ut.toyid 
   group by u.id, u.name, u.country

Podwyżkę kosztów chce obliczyć JOINując odpowiednią tabelę, która także jest podzapytaniem:

 select 
     cr.rise, 
     round( CAST(float8 (foo1.price_sum+(foo1.price_sum*cr.rise)/100.0) as numeric),2) as new_price 
   from country_rise cr 
   where cr.country = foo1.country

Oto całe moje zapytanie:

test=# select 
   foo1.name, 
   foo1.toys_names, 
   foo1.price_sum, 
   foo1.count_toys, 
   foo2.rise || '%' as rise, 
   foo2.new_price 
from (
   select 
     u.id, 
     u.name, 
     u.country, 
     string_agg(t.name, ',') as toys_names, 
     sum(t.price) as price_sum, 
     count(t.id) as count_toys 
   from users u 
   LEFT JOIN user_to_toy ut ON u.id = ut.userid 
   LEFT JOIN toys t ON t.id = ut.toyid 
   group by u.id, u.name, u.country
) foo1 
JOIN (
   select 
     cr.rise, 
     round( CAST(float8 (foo1.price_sum+(foo1.price_sum*cr.rise)/100.0) as numeric),2) as new_price 
   from country_rise cr 
   where cr.country = foo1.country
) foo2 ON country;
ERROR:  invalid reference to FROM-clause entry for table "foo1"
LINE 1: ...y) foo1 JOIN (select cr.rise, round( CAST(float8 (foo1.price...
                                                             ^
HINT:  There is an entry for table "foo1", but it cannot be referenced from this part of the query.

Wyświetlany błąd, wynika z tego że nie możemy odwoływać się do tabel zewnętrznych, które są podzapytaniami w łączeniach. W takim przypadku musimy użyć właśnie LATERAL. Bez tego słowa kluczowego, każde zapytanie jest oceniane niezależnie i nie możemy się odwoływać do żadnego elementu w FROM.

A to już poprawione nieco powyższe zapytanie:

test=# select 
  foo1.name,
  foo1.toys_names,
  foo1.price_sum,
  foo1.count_toys,
  foo2.rise || '%' as rise,
  foo2.new_price 
from (
   select 
      u.id, 
      u.name, 
      u.country, 
      string_agg(t.name, ',') as toys_names, 
      sum(t.price) as price_sum, 
      count(t.id) as count_toys 
   from users u 
   LEFT JOIN user_to_toy ut ON u.id = ut.userid 
   LEFT JOIN toys t ON t.id = ut.toyid 
   group by u.id, u.name, u.country
) foo1, 
LATERAL (
   select 
      cr.rise, 
      round( CAST(float8 (foo1.price_sum+(foo1.price_sum*cr.rise)/100.0) as numeric),2) as new_price 
   from country_rise cr 
   where cr.country = foo1.country) foo2;
 name  |                      toys_names                      | price_sum | count_toys | rise | new_price 
-------+------------------------------------------------------+-----------+------------+------+-----------
 Kasia | Kolorowanka                                          |         6 |          1 | 10%  |      6.60
 Marta |                                                      |           |          0 | 10%  |          
 Ula   | Pluszowa Żyrafa,Pluszowa Żabka,Samochodzik,Gryzaczek |        69 |          4 | 10%  |     75.90
 Beata | Kolorowanka,Pluszowa Żyrafa                          |        16 |          2 | 10%  |     17.60
 Ela   | Gryzaczek,Pluszowa Żyrafa                            |        15 |          2 | 35%  |     20.25
 Kuba  | Pluszowa Żabka                                       |        20 |          1 | 35%  |     27.00
 Ola   | Samochodzik,Pluszowa Żabka                           |        54 |          2 | 50%  |     81.00
(7 rows)

Według dokumentacji (którą możecie przeczytać pod tym linkiem), teraz możemy się odwoływać w sub-SELECT do elementów kolumn z FROM, które pojawiają się przed nim w tej liście (każdy wiersz z tabeli źródłowej (FROM) jest wykorzystywany do oszacowania wiersza w LATERAL. Utworzony w ten sposób wiersz(e) są joinowane). 

Sprawdźmy jeszcze koszt takiego zapytania:

test=# explain select foo1.name, foo1.toys_names, foo1.price_sum, foo1.count_toys, foo2.rise || '%' as rise, foo2.new_price 
test-# from (
test(#    select 
test(#       u.id, 
test(#       u.name, 
test(#       u.country, 
test(#       string_agg(t.name, ',') as toys_names, 
test(#       sum(t.price) as price_sum, 
test(#       count(t.id) as count_toys 
test(#    from users u 
test(#    LEFT JOIN user_to_toy ut ON u.id = ut.userid 
test(#    LEFT JOIN toys t ON t.id = ut.toyid 
test(#    group by u.id, u.name, u.country
test(# ) foo1, 
test-# LATERAL (
test(#    select 
test(#       cr.rise, 
test(#       round( CAST(float8 (foo1.price_sum+(foo1.price_sum*cr.rise)/100.0) as numeric),2) as new_price 
test(#    from country_rise cr 
test(#    where cr.country = foo1.country) foo2;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Hash Join  (cost=124.18..199.38 rows=1141 width=568)
   Hash Cond: (cr.country = foo1.country)
   ->  Seq Scan on country_rise cr  (cost=0.00..26.30 rows=1630 width=16)
   ->  Hash  (cost=122.43..122.43 rows=140 width=576)
         ->  Subquery Scan on foo1  (cost=119.28..122.43 rows=140 width=576)
               ->  HashAggregate  (cost=119.28..121.03 rows=140 width=1056)
                     Group Key: u.id, u.name, u.country
                     ->  Hash Left Join  (cost=26.30..96.81 rows=1498 width=1056)
                           Hash Cond: (ut.toyid = t.id)
                           ->  Hash Right Join  (cost=13.15..67.56 rows=1498 width=536)
                                 Hash Cond: (ut.userid = u.id)
                                 ->  Seq Scan on user_to_toy ut  (cost=0.00..31.40 rows=2140 width=8)
                                 ->  Hash  (cost=11.40..11.40 rows=140 width=532)
                                       ->  Seq Scan on users u  (cost=0.00..11.40 rows=140 width=532)
                           ->  Hash  (cost=11.40..11.40 rows=140 width=524)
                                 ->  Seq Scan on toys t  (cost=0.00..11.40 rows=140 width=524)
(16 rows) 

Poprawione nieco zapytanie (tak abyśmy mogli zobaczyć jakieś wyniki i ocenić jego koszt) bez użycia LATERAL:

test=# select name, toys_names, price_sum, count_toys, rise || '%' as rise, round( CAST(float8 (price_sum+(price_sum*rise)/100.0) as numeric),2) as new_price 
from (
   select 
      u.id, 
      u.name, 
      u.country, 
      string_agg(t.name, ',') as toys_names, 
      sum(t.price) as price_sum, 
      count(t.id) as count_toys, rise 
   from users u 
   LEFT JOIN user_to_toy ut ON u.id = ut.userid 
   LEFT JOIN toys t ON t.id = ut.toyid 
   LEFT JOIN country_rise cr ON  cr.country = u.country 
   group by u.id, u.name, u.country, cr.rise) foo1;
 name  |                      toys_names                      | price_sum | count_toys | rise | new_price 
-------+------------------------------------------------------+-----------+------------+------+-----------
 Ula   | Samochodzik,Pluszowa Żyrafa,Gryzaczek,Pluszowa Żabka |        69 |          4 | 10%  |     75.90
 Ola   | Pluszowa Żabka,Samochodzik                           |        54 |          2 | 50%  |     81.00
 Ela   | Pluszowa Żyrafa,Gryzaczek                            |        15 |          2 | 35%  |     20.25
 Kuba  | Pluszowa Żabka                                       |        20 |          1 | 35%  |     27.00
 Kasia | Kolorowanka                                          |         6 |          1 | 10%  |      6.60
 Beata | Kolorowanka,Pluszowa Żyrafa                          |        16 |          2 | 10%  |     17.60
 Marta |                                                      |           |          0 | 10%  |          
(7 rows)

test=# explain select name, toys_names, price_sum, count_toys, rise || '%' as rise, round( CAST(float8 (price_sum+(price_sum*rise)/100.0) as numeric),2) as new_price 
from (
   select 
      u.id, 
      u.name, 
      u.country, 
      string_agg(t.name, ',') as toys_names, 
      sum(t.price) as price_sum, 
      count(t.id) as count_toys, rise 
   from users u 
   LEFT JOIN user_to_toy ut ON u.id = ut.userid 
   LEFT JOIN toys t ON t.id = ut.toyid 
   LEFT JOIN country_rise cr ON  cr.country = u.country 
   group by u.id, u.name, u.country, cr.rise) foo1;
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Subquery Scan on foo1  (cost=6793.36..7647.99 rows=12209 width=568)
   ->  GroupAggregate  (cost=6793.36..7190.15 rows=12209 width=1060)
         Group Key: u.id, u.name, u.country, cr.rise
         ->  Sort  (cost=6793.36..6823.88 rows=12209 width=1060)
               Sort Key: u.id, u.name, u.country, cr.rise
               ->  Hash Right Join  (cost=84.39..287.63 rows=12209 width=1060)
                     Hash Cond: (ut.userid = u.id)
                     ->  Hash Left Join  (cost=13.15..67.56 rows=2140 width=528)
                           Hash Cond: (ut.toyid = t.id)
                           ->  Seq Scan on user_to_toy ut  (cost=0.00..31.40 rows=2140 width=8)
                           ->  Hash  (cost=11.40..11.40 rows=140 width=524)
                                 ->  Seq Scan on toys t  (cost=0.00..11.40 rows=140 width=524)
                     ->  Hash  (cost=56.97..56.97 rows=1141 width=536)
                           ->  Hash Right Join  (cost=13.15..56.97 rows=1141 width=536)
                                 Hash Cond: (cr.country = u.country)
                                 ->  Seq Scan on country_rise cr  (cost=0.00..26.30 rows=1630 width=16)
                                 ->  Hash  (cost=11.40..11.40 rows=140 width=532)
                                       ->  Seq Scan on users u  (cost=0.00..11.40 rows=140 width=532)
(18 rows)

Wyniki są takie same (choć inna kolejność), ale koszt zapytania o wiele większy!

Oczywiście zawsze można by napisać lepsze zapytania, przy którym okazało by się, że koszt nie jest tak wysoki a i nie było by konieczne użycie LATERAL JOINa, ale w tym poście nie o to chodziło :-)

Locki i deadlocki w InnoDB

Witajcie. Dawno już nic nie pisałam, a wracając do tego chciałabym poruszyć temat locków bazodanowych w szczególności w MySQLu (czyli w enginie InnoDB).

Locki są "podtrzymaniem" zasobów, aby zachować integralność danych. Są używane (powstają), gdy wielu (przynajmniej dwóch) użytkowników (sesje) potrzebuje dostępu do danych jednocześnie. Czyli są one zabezpieczeniem przed uszkodzeniem lub zwróceniem niepoprawnych danych.

W MySQL-u tylko enginy InnoDB i NDB (Cluster) wspierają transakcje. Warto jednak zaznaczyć, że dla takich enginów jak MyISAM, Merge czy Memory przy każdej zmianie w tabeli, następuje jej zalockowanie (locki na poziomie całej tabeli). InnoDB wspiera lokowanie na poziomie wierszy, czyli implementuje zbiór wartości ACID:

• Atomicity - Każda transakcje wykonywana jest w całości albo w ogóle (ustawienia autocomitu, polecenia COMMIT i ROLLBACK),
• Consistency - Po wykonaniu transakcji system będzie spójny (wewnętrzne procesy InnoDB chroniące przed crash-recovery: InnoDB doublewrite buffor, InnoDB crash recovery),
• Isolation - Jeśli jakieś dwie transakcje wykonują zadania współbieżnie, to w zależności od poziomu izolacji, nie widzą zmian przez siebie wprowadzonych (ustawienia autocommitu, SET ISOLATION LEVEL),
• Durability - System potrafi zwrócić poprawne i spójne dane po każdej zatwierdzonej transakcji (realizowane przez InnoDB doublewrite buffor, konfiguracja
  innodb_flush_log_at_trx_commit, konfiguracja sync_binlog, konfiguracja innodb_file_per_table, wsparcie dla fsync() w systemie operacyjnym ...).

O transakcjach i poziomach izolacji w MySQLu i PostgreSQLu pisałam już wcześniej, a do posta możecie wrócić tutaj.

InnoDB implementuje standardowy row-level locking z dwoma typami locków:

• Shared (S) = Read
• Przykład:  SELECT ... LOCK IN READ MODE
• nie pozwala na założenie locka do pisania
• pozwala innym użytkownikom na ten sam read lock
• Exclusive (X) = Read/Write
• Przykład: SELECT ... FOR UPDATE
• nie pozwala na jakiekolwiek inne locki
• pozwala na update lub delete

Przyjrzyjmy się z bliska samej implementacji locków w MySQLu. InnoDB rozróżnia następujące typy locków:

• record lock - lock na indexie rekordu (Jeśli tabela nie ma żadnego indexu, InnoDB tworzy ukryty index (tzw. clustered index) i używa go do zalockowania rekordu).
  Przykład zapytania:
  
  SELECT * FROM test WHERE id = 25 FOR UPDATE;
  
  Zapobiega wywołaniu zamian (update, delete, insert), gdzie test.id jest równe 25.

• gap lock - lock na przerwie między indexem rekordów lub lockiem na przerwie przed pierwszym albo ostatnim indexem recordu. Mnie także, trudno było zrozumieć, o co chodzi, więc lepiej popatrzmy na jakiś przykład:
  
  SELECT * FROM test WHERE id BETWEEN 10 AND 20 FOR UPDATE;
  
  Zapytanie zapobiega wykonywania zapytań, które będą chciały wykonać zmiany na tym zakresie wartości id, a także nie pozwoli wstawić nowych wartości np. o id = 15.
  Jeśli na kolumnie id istniałby unikalny index, gap lock by nie powstał tylko rekord-lock zostałby wykorzystany do naszego zapytania.
  Tego typu locki mogą być łatwo wyłączone poprzez zmianę poziomu izolacji na READ COMMITED (przypominam, że dla MySQLa domyślnym poziomem izolacji jest REPEATABLE-READ) lub włączenie zminnej serwerowej innodb_locks_unsafe_for_binlog.
• next-key lock - jest kombinacją dwóch powyższych loków, czyli record lock i gap locku (next-key lock jest to record lock z uwzględnieniem szczeliny przed indexem rekordu). Przykład:
  
  SELECT * FROM test WHERE id > 20 FOR UPDATE;
  Na tabeli test i kolumnie id isnieje index. Zapytanie nakłada record-lock na wiersze spełniające warunek, a także nie pozwala na inserty w 'przerwach', takich jak id równe 23,27,30 i wyżej.

Wiemy, czym są same locki i jak mogą powstawać, ale dla developera bazodanowego największym zagrożeniem są deadlocki.

Co to jest deadlock?

Sytuacja gdzie dwie różne transakcje nie mogą procesować, bo każdy z nich trzyma lock, który inny potrzebuje. Ponieważ, każdy z procesów czeka na zasoby, aż będą dostępne, żadne nie zwolni locku, które trzyma.

Przykład: Proces A lockuje wiersze w tabeli o id 1,2,3,4,5, a proces B lockuje wiersze w tej samej tabeli tylko w innej kolejności 5,4,3,2,1. Proces A otrzymał już część zasobów (np. wiersze o id 1,2 i 3) i czeka na zwolnienie zasobów przez proces B (wiersze o id 5 i 4), a proces B czeka na zwolnienie zasobów, które zajął proces A. I tak mogą sobie czekać ...

Jak bazy danych radzą sobie z taką sytuacją?

W bazach danych istnieje mechanizm automatycznej detekcji deadlocków. Polega on na wykryciu deadlocku i rollbacku jednej z transakcji (victim z ang. ofiara), która bierze w deadlocku udział. InnoDB wybiera najmniejszą transakcję, gdzie rozmiar transakcji jest ustalany przez liczbę wierszy zmienionych przez operację INSERT, UPDATE lub DELETE.

Innym sposobem jest ustawienie zmiennej innodb_lock_wait_timeout (ile sekund ma czekać, aby cofnąć transakcje). Jest ona wykorzystywana, gdy lock nastapił przez polecenie LOCK TABLE lub gdy przy lockowaniu bierze udział engin inny niż InnoDB.

Jak radzić sobie z deadlockami?

Najlepszą sytuacją będzie tworzenie małych transakcji, częste commitowanie ich, aby zapobiegać możliwości wystąpienia konfliktów albo lockowania jak najmniejszej ilości wierszy/tabel. O innych sposobach poczytajcie w tym miejscu: tutaj.

Miłej nauki :-P