Dotaz: Postgresql - obmedzenie počtu referencií

[AraxoN]

Zdravím.

Mám Postgres 8.0.13, v ňom databázu s takýmito tabuľkami:

CREATE TABLE uzivatel (
  id SERIAL NOT NULL,
  meno VARCHAR NOT NULL,
  PRIMARY KEY (id)
);
INSERT INTO uzivatel (meno) VALUES ('Jozko');
-- dostane id = 1
INSERT INTO uzivatel (meno) VALUES ('Ferko');
-- dostane id = 2
INSERT INTO uzivatel (meno) VALUES ('Samko');
-- dostane id = 3

CREATE TABLE uloha (
  id SERIAL NOT NULL,
  zadavatel INT REFERENCES uzivatel (id) ON UPDATE CASCADE ON DELETE SET NULL,
  riesitel INT REFERENCES uzivatel (id) ON UPDATE CASCADE ON DELETE SET NULL,
  kontrolor INT REFERENCES uzivatel (id) ON UPDATE CASCADE ON DELETE SET NULL,
-- plus nejake dalsie nepodstatne stlpce
  PRIMARY KEY (id)
);
INSERT INTO uloha (zadavatel, riesitel, kontrolor) VALUES (1,NULL,NULL);
INSERT INTO uloha (zadavatel, riesitel, kontrolor) VALUES (2,2,NULL);
INSERT INTO uloha (zadavatel, riesitel, kontrolor) VALUES (3,3,3);

T.j. sú tam užívatelia a úlohy k nim. Eviduje sa kto úlohu zadal, kto ju rieši (resp. riešil) a kto ju skontroloval. Je to ošetrené referenciami tam, aby zmazanie užívateľa nastavilo v príslušnom stĺpci NULL. Problém nastáva, ak vo všetkých 3 stĺpcoch je tá istá hodnota (ten istý užívateľ). Pri pokuse o vymazanie takého užívateľa sa to zasekne, namiesto toho aby nastavilo všade NULL:

araxon=# DELETE FROM uzivatel WHERE id=1; -- toto je v pohode
DELETE 1
araxon=# DELETE FROM uzivatel WHERE id=2; -- toto je este tiez v pohode
DELETE 1
araxon=# DELETE FROM uzivatel WHERE id=3; -- a toto uz je problem
ERROR:  insert or update on table "uloha" violates foreign key constraint "uloha_kontrolor_fkey"
DETAIL:  Key (kontrolor)=(3) is not present in table "uzivatel".
CONTEXT:  SQL statement "UPDATE ONLY "public"."uloha" SET "riesitel" = NULL WHERE "riesitel" = $1"

T.j. ak sú referencie na užívateľa len v jednom či dvoch stĺpcoch, tak to ide, ale v troch a viac sa to už sekne. Vyzerá to, že pri mazaní toho tretieho užívateľa to opraví prvé dve referencie (zadavatel, riesitel) a potom sa pokúsi užívateľa vymazať nehľadiac na tretiu referenciu (kontrolor). Bude to najskôr nejaký limit, ktorý je v Postgresi zakomponovaný natvrdo. Ak sa na ten istý záznam odkazujú len ľubovoľné 2 stĺpce, tak to ide bez problémov, ak ich je viac, tak to už zlyhá. Nenašiel som k tomuto nič v dokumentácii, ani na fórach, tak sa pýtam: nestretol sa s tým niekto? Viem, že je možné zmeniť štruktúru aplikácie, ale toto je len príklad - v skutočnosti to máme zložitejšie, týka sa to viacerých tabuliek a je to nasadené kade-tade. Tento problém nie je vôbec častý, akurát trochu nahlodáva moju vieru v to, že všetko vždy bude fungovať tak ako má.

Otázka teda znie: dá sa tento limit nastaviť v konfigurácii, alebo pri kompilácii postgresu? Rád by som to vyriešil bez toho aby trebalo meniť aplikáciu - o referenčnú integritu sa má starať databáza, a nie že to bude kontrolovať ešte aj aplikácia.

---

---

Odpovědi

[johniez]

je to divny, na prvni pohled by to melo slapat..

nejhur tam dej misto tech tri references triger na tabulku uzivatel ve kterym provedes update tabulky uloha..

[AraxoN]

Triggery som ešte nerobil... Naštudujem a skúsim. Zatiaľ dík.

[AraxoN]

Nie je to dobré: trigger by sa musel spustiť ešte pred zmazaním užívateľa. Samotné zmazanie sa ale môže nevykonať kvôli niečomu inému (napríklad ON DELETE RESTRICT v nejakej úplne inej tabuľke, ktorá sa na užívateľa odkazuje), takže by sa mohlo stať, že v jeho úlohách už nastavím NULL, ale nakoniec ho vôbec nevymažem...

[johniez]

after delete trigger...

a kdyz bych chtel mit jistotu udelam na mazani proceduru, ktera to muze kontrolovat..

[AraxoN]

keď sa to má spustiť až po vymazaní, tak sa to nespustí vôbec, lebo k vymazaniu ani nedôjde... či nie?

[Pavel Stehule]

tohle by fungovat melo, zkusim se poptat v konferenci. Jinak muzete zkusit deferred pravidla. Pri ni se referencni integrita kontroluje az v okamziku commitu.

[Pavel Stehule]

Je zajimavy, ze tohle projde

CREATE TABLE users (
  id SERIAL NOT NULL,
  name VARCHAR NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO users VALUES (1, 'Jozko');
INSERT INTO users VALUES (2, 'Ferko');
INSERT INTO users VALUES (3, 'Samko');

CREATE TABLE tasks (
  id SERIAL NOT NULL,
  owner INT REFERENCES  users (id) ON UPDATE CASCADE ON DELETE SET NULL,
  worker INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL,
  checked_by INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL,
  PRIMARY KEY (id)
);
INSERT INTO tasks VALUES (1,NULL,NULL);
INSERT INTO tasks VALUES (2,2,NULL);
INSERT INTO tasks VALUES (3,3,3);

DELETE FROM users WHERE id = 1; -- works simple
DELETE FROM users WHERE id = 2; -- works ok
DELETE FROM users WHERE id = 3; -- doesn't work, why

[Pavel Stehule]

Tohle jsem spatne prepsal, sorry. Kdybych to prepsal korektne, tak to nebude fungovat taky.

[Pavel Stehule]

Sice netusim, proc to projde u dvou sloupcu a uz to neprojde u tri, nicmene pomouhou tzv, odlozene podminky (jak uz jsem psal) deferred constraints. Nasledujici test projde:

CREATE TABLE users (
 id integer NOT NULL,
 name VARCHAR NOT NULL,
 PRIMARY KEY (id)
);

INSERT INTO users VALUES (1, 'Jozko');
INSERT INTO users VALUES (2, 'Ferko');
INSERT INTO users VALUES (3, 'Samko');

CREATE TABLE tasks (
 id integer NOT NULL,
 owner INT REFERENCES  users (id) ON UPDATE CASCADE ON DELETE SET NULL INITIALLY DEFERRED,
 worker INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL INITIALLY DEFERRED,
 checked_by INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL INITIALLY DEFERRED,
 PRIMARY KEY (id)
);
INSERT INTO tasks VALUES (1,1,NULL,NULL);
INSERT INTO tasks VALUES (2,2,2,NULL);
INSERT INTO tasks VALUES (3,3,3,3);

DELETE FROM users WHERE id = 1; -- works simple
DELETE FROM users WHERE id = 2; -- works ok
DELETE FROM users WHERE id = 3; -- works too

Docela bych rekl, ze se jedna o bug postgresql (vlastnost), resp. nedomyslenost pri vyhodnocovani podminek.

Pavel

[AraxoN]

Super, ďakujem! S INITIALLY DEFERRED to chodí.

[Pavel Stehule]

tak 100% se jedna o bug. Patch s opravou je k dispozici na http://archives.postgresql.org/pgsql-patches/2007-08/msg00104.php a pokud se nic nestane, tak v dalsich minoritnich verzich tato chyba bude opravena.

Pavel

[AraxoN]

Dobrá práca! Moja obľúbená databáza je tak zase o čosi spoľahlivejšia.