Blog

PostgreSQL, który jest podstawą EuroDB, jest znany z niemal fanatycznego dążenia do implementacji standardów bazodanowych. Wraz z wydaniem wersji 12 został zrobiony kolejny krok w kierunku ich jak najpełniejszego udostępnienia w modelu Open Source. W tym tekście skupimy się na kolumnach generowanych, zwanych też kolumnami wirtualnymi, z których pozwala skorzystać najświeższe wydanie jednego z najpopularniejszych wśród deweloperów silnika bazodanowego – PostgreSQL 12.

Na wstępie warto wytłumaczyć, czym są kolumny generowane (ang. generated columns). Parafrazując standard SQL:2003, kolumna generowana w tabeli to taka, której wartości będą wynikiem wyrażenia, którego składowe to inne kolumny. Brzmi to zawile, dlatego warto pokazać to w praktyce, na przykładzie z wykorzystaniem pseudojęzyka (do SQL przejdziemy później).

ID	netto_price	tax_rate	price
01	10.00	23%	12.30
02	123.00	23%	151.29
03	39.99	8%	43.19

Jak widać, dla takiej tabeli wartości pola price wprost wynikają z wartości innych pól. W tym wypadku wyrażenie w pseudojęzyku mogłoby wyglądać następująco:

price := netto_price + netto_price * tax_rate

Koncepcyjnie widać, że kolumna generowana w stosunku do innych kolumn jest tym samym co widok dla tabel.

Przechowując w bazie danych jedynie informacje o sposobie generowania jednej z kolumn, zamiast wyników tego działania, można zaoszczędzić całkiem pokaźne ilości przestrzeni (dla odpowiednio dużych zbiorów danych). Oczywiście, jak większość kompromisów w informatyce pociąga to za sobą zwiększone zapotrzebowanie na zasoby obliczeniowe. W ten sposób widać już pewnie, dlaczego niektórzy producenci zdecydowali się na zmianę standardowej nazwy i wykorzystują pojęcie kolumny wirtualnej, ponieważ jej dane niekoniecznie znajdują się na dysku.

Kolumny generowane w PostgreSQL 12

Przejdźmy jednak do konkretów i zobaczymy, w jaki sposób najnowszy Postgres pozwala na skorzystanie z tej funkcji. Zgodnie z dokumentacją za tworzenie tego rodzaju kolumn odpowiada klauzula GENERATED ALWAYS AS, z której należy skorzystać przy tworzeniu tabeli. Weźmy na warsztat poprzedni przykład:

CREATE TABLE product_prices (
    ...
    netto_price NUMERIC,
    tax_rate NUMERIC,
    price NUMERIC GENERATED ALWAYS AS (netto_price + netto_price * tax_rate) STORED,
);

W tym miejscu warto wspomnieć o słowie kluczowym STORED. Oznacza to, że wygenerowana wartość będzie przechowywana na dysku. Na ten moment jest to też jedyna dostępna forma tworzenia kolumn generowanych, co nie oznacza, że następne wersje Postgresa nie będą umożliwiać operacji na całkiem „wirtualnych” kolumnach. Już teraz jest to jednak bardzo wygodny sposób na bezpośrednie wskazanie na powiązania między danymi już na etapie projektowania bazy danych. Jest to też funkcja w pełni wspierana przez moduł modelowania bazy danych EuroDB. Pozwala to zlecić bazie danych „dopilnowanie” wprowadzania aktualizacji w tych miejscach, gdzie nam na tym zależy. Na razie jednak pozostaje nam czekać na pełną implementację, która będzie również pozwalać na skorzystanie z VIRTUAL. Niemniej już teraz możemy wykorzystać tę funkcję np. przy zmianach w danych:

postgres=# SELECT * FROM product_prices;
 id | netto_price | tax_rate |  price   
----+-------------+----------+----------
  1 |       10.00 |     0.23 |  12.3000
  2 |      123.00 |     0.23 | 151.2900
  3 |       39.99 |     0.08 |  43.1892
(3 rows)

postgres=# UPDATE product_prices SET netto_price = 35.99 WHERE id = 3;
UPDATE 1
postgres=# SELECT * FROM product_prices;
 id | netto_price | tax_rate |  price   
----+-------------+----------+----------
  1 |       10.00 |     0.23 |  12.3000
  2 |      123.00 |     0.23 | 151.2900
  3 |       35.99 |     0.08 |  38.8692
(3 rows)

Kolumny generowane i jednostkowe (ang. identity column)

Warto tutaj wspomnieć jeszcze o tzw. identity column, czyli mechanizmie pozwalającym na zdefiniowanie kolumn, których wartości są częścią pewnej sekwencji. Najczęściej jest to wykorzystywane w kolumnach będących numerem identyfikacyjnym rekordów. W starszych wersjach PostgreSQL zalecaną metodą definiowania kolumn ID było skorzystanie ze specjalnego typu SERIAL. Od wydania 10 najlepszą praktyką dla nowych projektów jest korzystanie z kolumn jednostkowych. Wspominam o nich tutaj ze względu na ich składnię, która jest bardzo podobna. Posiłkując się naszym przykładem, pełna definicja tabeli powinna wyglądać tak:

CREATE TABLE product_prices (
    id INT GENERATED ALWAYS AS IDENTITY,
    netto_price NUMERIC,
    tax_rate NUMERIC,
    price NUMERIC GENERATED ALWAYS AS (netto_price + netto_price * tax_rate) STORED,
);

Przykłady wykorzystania generowanych kolumn

Ponieważ kolumny generowane są specjalnym bytem w świecie Postgresa, można je wykorzystać w dość ciekawy sposób. Jednym z takich zastosowań jest kontrola widoczności poszczególnych kolumn, ponieważ generowane kolumny otrzymują osobne uprawnienia od kolumn, na których bazują:

postgres=> REVOKE ALL ON product_prices FROM eurolinux;
REVOKE
postgres=> GRANT SELECT(id, netto_price, tax_rate) ON product_prices TO eurolinux;
GRANT
postgres=> SELECT * FROM product_prices;
ERROR:  permission denied for table product_prices

Jak widać, dostęp do kolumn bazowych nie pozwala na przeczytanie również wygenerowanej kolumny. Podobnie sytuacja ma się na odwrót, dostęp do kolumny generowanej nie pozwala widzieć wartości, z których ona wynika.

Możliwości wykorzystania tego w codziennej praktyce może okazać się bardzo dobrym sposobem na uproszczenie niektórych zapytań lub zrezygnowanie w niektórych wypadkach z triggerów. Zdecydowanie doprowadzi to do uproszczenia zarządzania oraz rozwijania wykorzystywanych baz danych.

Podsumowanie

Jak widać, stosunkowo prosty temat może doprowadzić do dość długich rozważań na ich temat. Dziękuję za czas poświęcony na przeczytanie tego tekstu oraz zachęcam do subskrypcji naszego newslettera, żeby informacja o podobnych tekstach w przyszłości trafiła bezpośrednio na Waszą skrzynkę mailową.