[megoldva] PostgreSQL trigger

Van két összetartozó táblám (idegen kulccsal): tömb - cetli

Egy tömb, sok cetli. Mindkettőben van státuszmező.

Hogy lehet olyan triggert létrehozni Postgresben, aminek a hatására "ha az összes cetli státusz ilyen", akkor a tömbben is átállítsa a státuszt adott másikra?

Hozzászólások

Alapban update, insert triggerjeid vannak. Akkor tudsz futtatni psql eljárást is akár, ami kigyűjti neked. Vagy csinálsz egy viewt amire ránéz a trigger és az alapján dönt.

Egy tárolt eljárás ellenőrzi a feltételt és állítja a tömb állapotát. Ezek után a cetlik táblán lévő triggerek meghívják a tároltat. A tárolt futtatható kézzel is ha kell.

A cetli módosításakor/beszúrásakor történjen? akkor cetlin insert trigger, benne select ami megnézi a new.id* alapján releváns cetlik állapotát és opcionálisan(if) update a tömbre.

 

*Te id-d.

-- drop trigger if exists cetli_change_trigger on cetli cascade;
-- drop function if exists cetli_change_trigger_func();
-- drop function if exists update_tomb_status(int);
-- drop table if exists cetli;
-- drop table if exists tomb;
-- drop type if exists cetli_status cascade;
-- drop type if exists tomb_status cascade;

create type tomb_status as enum ('OPEN', 'CLOSED');

create type cetli_status as enum ('OPEN', 'CLOSED');

create table if not exists tomb (
        id serial primary key,
        status tomb_status not null default 'OPEN'::tomb_status
);

create table if not exists cetli (
        id serial primary key,
        tomb_id int not null references tomb(id) on update cascade on delete cascade,
        status cetli_status not null default 'OPEN'::cetli_status
);

create or replace function update_tomb_status(update_tomb_id int) returns void as $$
DECLARE
        all_cetli_closed boolean;
BEGIN
        select
                exists(select 1 from cetli c where c.tomb_id = update_tomb_id)
                and 'CLOSED'::cetli_status = ALL(select status from cetli c where c.tomb_id = update_tomb_id)
        into all_cetli_closed;

        if (all_cetli_closed) then
                update tomb t
                        set status = 'CLOSED'::tomb_status
                        where t.id = update_tomb_id
                                and t.status != 'CLOSED'::tomb_status;
        else
                update tomb t
                        set status = 'OPEN'::tomb_status
                        where t.id = update_tomb_id
                                and t.status != 'OPEN'::tomb_status;
        end if;
END;
$$ language plpgsql;

create or replace function cetli_change_trigger_func() returns trigger as $$
BEGIN
        if (TG_OP = 'INSERT' or TG_OP = 'UPDATE') and NEW.tomb_id is not null then
                perform update_tomb_status(NEW.tomb_id);
        end if;
        if (TG_OP = 'UPDATE' and TG_OP = 'DELETE') and OLD.tomb_id is not null and OLD.tomb_id != NEW.tomb_id then
                perform update_tomb_status(OLD.tomb_id);
        end if;
        return null;
END;
$$ language plpgsql;

create trigger cetli_change_trigger
        after insert or update or delete on cetli
        for each row execute procedure cetli_change_trigger_func();

Gyönyörű. Köszönöm.

A kódrészletet szinte egy az egyben tudtam használni. Annyit változtattam csak, hogy ez az update csak egyirányú lehet (azaz soha nem nyílik meg a cetlitömb, ha már bezártuk), úgyhogy pár sort ejtettem (már csak azért is, mert nem csak két státusz van a tömbnél), illetve az id nálam bigint típusú. Nagyon hálás vagyok ezért, egyrészt tanultam vele, másrészt ez így egyszerűbb és bolondbiztosabb, mint programból megoldani.

Egyébként ez egy csak adatbázisban futó alkalmazás? Vagy egy másik kérdésedben láttam a PHP-t, az hívogatja az adatbázist?

másrészt ez így egyszerűbb és bolondbiztosabb, mint programból megoldani.

Ha valakinek majd meg kell keresni, hogy miért update-elődik valami, ami nincs benne a programba, akkor a triggerekre is kell majd gondolnia, hogy rátaláljon.  És telepítéskor is. stb... Eggyel több hely.

A tranzakciókezelés jól működik egy normális adatbázis kezelőben (amilyen a postgres is), az is bolondbiztos. De ízlések és pofonok. :)

Szerkesztve: 2020. 03. 20., p - 23:46

Nem a kérdésre válasz, de mindenképp triggerrel akarod ezt megoldani? Anélkül, hogy ismerném a feladatot: Én a programban, ahol státuszt állítok a cetlire, ott ugyanabban a tranzakcióban update-elném a tömböt is, ha kell. Vagy ha később a programon kívül "kézzel" update-elném a cetliket, akkor sem nagy dolog a megfelelő státuszúra beupdate-elni a tömböt. 

Elvi szinten: most egy dolog, hogy két táblát akarsz így vezetni, és esetleg megoldható egy triggerrel, de ha később valami olyan logikát kell megoldani, ahol három táblában van négyféle státusz, és még két dolog számolódik, update-elődik (vagy még bonyolultabbat), akkor már nem biztos, hogy annyira jó ötlet egy trigger.

Igen, jó felvetés, nincs benne igazi megoldás:

- Lehet hogy a tömbök lekérdezésekor a cetlik állapotát "hozzákérdezzük" (EXISTS, ANY, ALL...). Nem lesz a leggyorsabb, lassú lehet ha tömb állapotra szűrni kell. De gyorsan átvezethető rajta egy üzleti logiak módosítás: pl: "akkor legyen nyitott ha a cetlik legalább fele nyitott".

- A teljesítényt tesztelni kell. Általában hiszünk abban hogy a RDBMS gyors. Ha ez nem lenne igaz, akkor biztos hiányzik egy index (LOL). De érdemes side-effect-eket is viszgálni, pl: tömbhöz tartozik átlag 5 cetli, egyszerű az utolsó/max/átlag lekérdezés. Aztán "egyszer csak" pár tömbnek lesz 10e cetlije, ami a megöli a lekérdezést, mert a query optimizer elkezd más stratégiát választani. Ez még problémásabb ha multi-tenant adatbázisban (több cég egyben) agyik cég egyik tömb-je öli meg az _összes_ cég ezirányú lekérdezéseit.

Lehet hogy félreértem amit írtál, akkor bocs. Én nem mondtam, hogy ne legyen mindkét táblában státusz, csak azt, hogy mentéskor az (akár az az egy rutin), ami az egyik táblát update-eli, az update-elheti a másikat is. A logika egy helyen lenne, nem kettő helyen: egy programban valahol, ami az update-et egyébként is hívja, és egy triggerben máshol, amire vagy emlékeznek majd, vagy nem. De lehet hogy jó oka van a triggerre, csak kiváncsi vagyok miért választotta, vagy miért kell.