PostgreSQL - trigger pomija instrukcje

[Marek S]

Witam Was,

Mam kłopot ze zrozumieniem, co się dzieje. Otóż stworzyłem trigger:

CREATE OR REPLACE FUNCTION admin.category_delete()
RETURNS TRIGGER AS
$$
BEGIN
RAISE NOTICE 'Weight %, parent ID %', OLD.weight,
OLD.parent_id;
UPDATE admin.category SET weight = weight - 1 WHERE
parent_id = OLD.parent_id AND weight > OLD.weight;
RAISE NOTICE 'After update';
RETURN OLD;
END;
$$
LANGUAGE plpgsql;


Wywołuję go jako:

CREATE TRIGGER category_delete AFTER DELETE ON admin.category FOR EACH
ROW EXECUTE PROCEDURE admin.category_delete()

No i update z niego nie jest realizowany. Zachowuje się tak jakby był
zakomentowany. W logach bazy są linie to sugerujące:

2019-11-03 23:06:01.946 CET [13200] DZIENNIK: wyraĹĽenie: BEGIN
2019-11-03 23:06:01.947 CET [13200] DZIENNIK: wykonanie <unnamed>:
DELETE FROM admin.category WHERE category_id = $1
2019-11-03 23:06:01.947 CET [13200] SZCZEGĂ“Ĺ Y: parametry: $1 = '1'
2019-11-03 23:06:01.950 CET [13200] UWAGA: Weight 1, parent ID <NULL>
2019-11-03 23:06:01.950 CET [13200] KONTEKST: funkcja PL/pgSQL
admin.category_delete(), wiersz 3 w RAISE
2019-11-03 23:06:01.950 CET [13200] UWAGA: After update
2019-11-03 23:06:01.950 CET [13200] KONTEKST: funkcja PL/pgSQL
admin.category_delete(), wiersz 5 w RAISE
2019-11-03 23:06:01.951 CET [13200] DZIENNIK: wyraĹĽenie: COMMIT

Powstają informacyjne exceptions przed wykonaniem aktualizacji i po.
Natomiast nie ma śladu po tym, co jest między komunikatami. O co chodzi?

--
Pozdrawiam,
Marek

[irq]

W dniu niedziela, 3 listopada 2019 23:18:09 UTC+1 użytkownik Marek S napisał:

>
> No i update z niego nie jest realizowany. Zachowuje się tak jakby był
> zakomentowany. W logach bazy są linie to sugerujące:
>

LOG nie pokazuje statementów wykonywanych z wnętrza funkcji. Nie oznacza to, że "update z niego nie jest realizowany".

Jeżeli rzeczywiście chcesz podglądać w logu, czy dochodzi do updatu, załóż sobie, na przykład, trigger ON UPDATE na tej tabeli i tam umieść stosowny RAISE NOTICE.

[Marek S]

W dniu 2019-11-04 o 16:19, irq pisze:

>
> Jeżeli rzeczywiście chcesz podglądać w logu, czy dochodzi do updatu,
> załóż sobie, na przykład, trigger ON UPDATE na tej tabeli i tam
> umieść stosowny RAISE NOTICE.

Sprytne. Dzięki ;-)

Kiedyś istniał debugger dla funkcji składowych. Znalazłem jedynie źródła
w języku C do niego. Dla mnie bezużyteczne - nie zajmuję się
programowaniem w C. Czy orientujesz się gdzie można znaleźć skompilowany
plugin?

A może masz inne sugestie na temat tego jak debugować w/w? Zakładanie
trigerów debugingowych jest dość kłopotliwe.

--
Pozdrawiam,
Marek

[Marek S]

W dniu 2019-11-04 o 16:19, irq pisze:

> LOG nie pokazuje statementów wykonywanych z wnętrza funkcji. Nie
> oznacza to, że "update z niego nie jest realizowany".
>

Znalazłem powód niedziałania update. Rozwiązałem go tak:

IF OLD.parent_id ISNULL THEN
UPDATE admin.category SET weight = weight - 1 WHERE parent_id ISNULL
AND weight > OLD.weight;

ELSE

UPDATE admin.category SET weight = weight - 1 WHERE parent_id =
OLD.parent_id AND weight > OLD.weight;

END IF;

Nie działa porównanie parent_id = OLD.parent_id jeżeli wartość jest
nullem. Uświadomiłem sobie, że OLD.parent nie jest częścią SQL lecz
zmienną, której wartość wstawiamy, więc parent_id = null nie może
zadziałać. :-)

Czy orientujesz się czy istnieje jakiś trik umożliwiający unikanie
nadmiarowych warunków w funkcjach? Mam na myśli "legalne" triki.


--
Pozdrawiam,
Marek

[irq]

Może w pierwotnej wersji updata:

WHERE parent_id IS NOT DISTINCT FROM OLD.parent_id

?

[Andrzej Stróżyński]

W dniu 2019-11-03 o 23:18, Marek S pisze:

> Witam Was,
>
> Mam kłopot ze zrozumieniem, co się dzieje. Otóż stworzyłem trigger:
>
>         CREATE OR REPLACE FUNCTION admin.category_delete()
>             RETURNS TRIGGER AS
>                 $$
>                 BEGIN
>                     RAISE NOTICE 'Weight %, parent ID %', OLD.weight,
> OLD.parent_id;
>                     UPDATE admin.category SET weight = weight - 1 WHERE
> parent_id = OLD.parent_id AND weight > OLD.weight;
>                     RAISE NOTICE 'After update';
>                     RETURN OLD;
>                 END;
>                 $$
>         LANGUAGE plpgsql;
>
>
> Wywołuję go jako:
>
> CREATE TRIGGER category_delete AFTER DELETE ON admin.category FOR EACH
> ROW EXECUTE PROCEDURE admin.category_delete()
>

[...]

Robisz UPDATE wiersza po jego usunięciu?

--
pozdrawiam
AS

[Marek S]

W dniu 2019-11-05 o 12:46, Andrzej Stróżyński pisze:

>
> Robisz UPDATE wiersza po jego usunięciu?
>

Robię Update wielu wierszy po usunięciu jednego z nich :-D

--
Pozdrawiam,
Marek

[Adam M]

Wkładanie logiki biznesowej na triggery to bardzo niebezpieczna zabawa - takie rozwiązania stosowało się epoce SQLa łupanego ;-) - prosciej i bezpieczniej przenieść logikę na middleware.

[Marek S]

W dniu 2019-11-06 o 21:54, Adam M pisze:

>
> Wkładanie logiki biznesowej na triggery to bardzo niebezpieczna zabawa - takie rozwiązania stosowało się epoce SQLa łupanego ;-) - prosciej i bezpieczniej przenieść logikę na middleware.

Tak, wiem, że niebezpieczna. Dlatego 15x to testuję zanim udostępnię.

Owszem, obecnie zwykle przenosi się logikę tego typu do ORMów, a nawet
widziałem raz sortowanie wykonane w PHP (!!!) w imię przenoszenia
wszystkiego, co możliwe do wykonania na bazie poza nią. Zauważam, że
zapanowała wielka awersja do używania baz danych. Zdarza się nawet, że
obiekty danych trzyma się w formie plików na dysku (patrz Pimcore). Ja
jednak będę trzymał się w/w rozwiązań. Właśnie jestem świadkiem początku
upadku sporego przedsięwzięcia - przed czym, u zarania projektu,
przestrzegałem team. Oni ślepo ufają nowoczesnym rozwiązaniom, jakie
proponujesz. W efekcie, przy ok 25 mln rekordów w bazie, niektóre proste
operacje potrafią trwać po kilka minut (klient tyle musi czekać na
response interfejsu) zamiast kilkadziesiąt milisekund. Przenoszenie
projektu na super wydajne serwery (hosting parę tys. $), stosowanie
loadbalacera - odsunęło problem w czasie. Osobiście wygodny ORM stosuję
tylko tam, gdzie nie może zaszkodzić bardzo: chyba tylko obsługa
formularzy. Zaczynam przenosić rozwiązania nowoczesne, na łupany SQL i
już w tej chwili zyskuję min. trzy rzędy wielkości krótsze czasy
realizowanych operacji.

Jednakże mimo wszystko nie zgodzę się z Tobą, że stosowanie procedur
składowych w bazie to przestarzałe podejście. Wręcz przeciwnie: widuję w
korporacjach działy administratorów baz danych, którym wręcz zleca się
realizowanie procedur składowych, z których korzystają programiści.
Programiści nie muszą być świadomi nawet tego, że jakąś kolumnę
przeniesiono z jednej tabeli do innej.

--
Pozdrawiam,
Marek

[Adam M]

Może trochę zaprzeczę własnemu stwierdzeniu alz zgodzę się z kolegą jeżeli takie rozwiązanie jest stosowane na bazie Oracle , Sybase ASE lub nawet Microsoft SQL gdzie debugowanie kodu SQL po strownie serwera jest łatwe i przyjemne - można wtedy traktować server SQL jak middleware. Niestety PostgrSQL ma fatalny debugger - ostatni jaki używałem był na 9.3 i padał jeśli tylko sie na niego człowiek źle popatrzył nie mówiąc o jakim kolwiek sensownym debugowaniu. Może coś poprawili na 10 - nie wiem tego bo poprostu sobie odpuściłem debugowanie na PostgreSQL is jeśli muszę coś pisac to omijam logikę na triggerach jak zarazę - procedury składowane tak (pomimo ze i tak śą skopane na PostgreSQL - nie mozna mieć transakcji częściowych w środku procedury/funkcji) - logika na triggerach nie - przynajmniej na PostgreSQL.

Pozdrawiam
Adam M

[Marek S]

W dniu 2019-11-05 o 12:12, irq pisze:

>
> Może w pierwotnej wersji updata:
>
> WHERE parent_id IS NOT DISTINCT FROM OLD.parent_id
>
> ?

Też tak uznałem i chyba przy tym pozostanę. Dzięki za sugestię.

--
Pozdrawiam,
Marek

[Marek S]

W dniu 2019-11-07 o 21:19, Adam M pisze:

> Niestety PostgrSQL ma fatalny debugger - ostatni jaki
> używałem był na 9.3 i padał jeśli tylko sie na niego człowiek źle
> popatrzył nie mówiąc o jakim kolwiek sensownym debugowaniu.

No więc właśnie... to też dla mnie udręka. Popełnię drobny błąd (jak w
wątku) i męczę się z nim. Nawet jeśli jest oczywisty, świeci jak
latarnia morska, to autosugestia zakazuje dostrzegania go. Trzeba po
omacku dochodzić przyczyn.

> Może coś
> poprawili na 10 - nie wiem tego bo poprostu sobie odpuściłem
> debugowanie na PostgreSQL

Szczerze - nawet nie mam pojęcia. Stosuję 11 i 12 lecz nie wydaje mi się
by w tym względzie ktokolwiek zadziałał.

> na triggerach jak zarazę - procedury składowane tak (pomimo ze i tak
> śą skopane na PostgreSQL

Sęk w tym, że lepszy Postgres niż śmieciowe MariaDB lub stary MySQL,
który zresztą zdechł. Za inne trzeba czasem srogo płacić. Więc z uwagi
na istniejący rynek, muszę stosować darmowe rozwiązania. A PostgreSQL
sprawuje się świetnie, działa wydajnie nawet przy przyrostach rzędu pół
miliona rekordów dziennie (po paru latach). MySQL (InnoDB) dawno by
odleciał. Stąd wybór i raczej będę przy nim tkwił.

> - nie mozna mieć transakcji częściowych w
> środku procedury/funkcji) - logika na triggerach nie - przynajmniej
> na PostgreSQL.

Można można i to od dawna ;-)

https://www.postgresql.org/docs/current/sql-savepoint.html

Czemu logika na trigerach nie? Bo trudno debugować? To tylko wydłuża
developement (bo narzędzie debugingowe kiepskie) lecz nie obniża
wydajności rozwiązania. Stosuję od lat tego typu podejście i jakoś nigdy
się na nim nie przejechałem. Mało tego, powiem Ci, że pracowałem w
firmie obsługującej wierzytelności (ogromne ilości wrażliwych danych -
drobny błąd danych prowadziłby do kolosalnych strat), która od lat tak
działa i baza sprawuje się u nich świetnie. Szczerze - nie miałbym się
do czego przyczepić.

--
Pozdrawiam,
Marek