PostgreSQL - dziwne zachowanie triggera
Marek - 14-12-2006 16:09
PostgreSQL - dziwne zachowanie triggera
Witam,
Nie potrafi? zinterpretowa? przedziwnego zachowania triggera. Otó? ma dwie
tabele: document oraz files. Pierwsza z nich zawiera dokumenty a druga m.in.
skojarzone pliki z tymi dokumentami. Tabela document posiada trigger:
CREATE OR REPLACE FUNCTION document_remove()
RETURNS TRIGGER AS
$$
BEGIN
EXECUTE 'DELETE FROM files WHERE document_id=' || OLD.document_id || '
AND document_type_id=2';
EXECUTE 'DELETE FROM shop_order_item WHERE document_id=' ||
OLD.document_id || ' AND was_unavailable=true';
IF OLD.category_id NOTNULL THEN
EXECUTE 'UPDATE category SET doc_counter=doc_counter-1 WHERE
category_id=' || OLD.category_id;
END IF;
RETURN OLD;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER document_remove BEFORE DELETE ON document FOR EACH ROW
EXECUTE PROCEDURE document_remove();
Okazuje si?, ?e wykonanie zapytania SQL w postaci:
DELETE FROM document WHERE document_id=xxx;
za pierwszym razem nie wykonuje si?. Kasowane s? za to stosowne rekordy w
tabeli files. Ponowne wywo?anie usuwa ??dany dokument. Reasumuj?c: je?li
jest odpowiadaj?cy rekord w tabeli files to usuni?cie rekordu w document nie
powiedzie si?, je?li tabela files nie ma rekordów spe?niaj?cych warunek
usuni?cia, wszystko zadzia?a poprawnie.
Zaczynam podejrzewa?, ?e definicja OLD ulega zmianie po wykonaniu SQL. Czy
wiecie co? na ten temat ?
--
Pozdrawiam,
Marek
Marek - 14-12-2006 16:09
Chyba wiem co jest grane ...
W strukturze document mam deklaracj? pola:
thumbnail_id int4 REFERENCES files (files_id) MATCH FULL ON DELETE SET
NULL ON UPDATE CASCADE,
Trigger powoduje wykasowanie tego samego rekordu z tabeli files, który jest
wskazywany przez thumbnail_id... moje przeoczenie.
Tak czy owak mam inne pytanie. Maj?c na wzgl?dzie powy?sz? sytuacj?
chcia?bym dokonywa? sprawdzenia czy w chwili wstawiania/aktualizacji rekordu
pole thumbnail_id zawiera poprawn? warto?? (czyli jest nullem lub wskazuje
na rekord w files). Z drugiej za? strony przy usuwaniu rekordu z tabeli
document chcia?bym usuwa? równie? rekord w files wskazywany przez
thumbnail_id. Po trzecie - usuni?cie pliku w files powinno ustawi? na null
pole thumbnail_id. Jak to pogodzi? ?
=?ISO-8859-2?Q?Pawe=B3_Matejski?= - 14-12-2006 16:09
Marek wrote:
> Witam,
>
> Nie potrafi? zinterpretowa? przedziwnego zachowania triggera. Otó? ma
> dwie tabele: document oraz files. Pierwsza z nich zawiera dokumenty a
> druga m.in. skojarzone pliki z tymi dokumentami. Tabela document posiada
> trigger:
> CREATE OR REPLACE FUNCTION document_remove()
> RETURNS TRIGGER AS
> $$
> BEGIN
> EXECUTE 'DELETE FROM files WHERE document_id=' || OLD.document_id || '
> AND document_type_id=2';
> EXECUTE 'DELETE FROM shop_order_item WHERE document_id=' ||
> OLD.document_id || ' AND was_unavailable=true';
> IF OLD.category_id NOTNULL THEN
> EXECUTE 'UPDATE category SET doc_counter=doc_counter-1 WHERE
> category_id=' || OLD.category_id;
> END IF;
> RETURN OLD;
> END;
> $$
> LANGUAGE plpgsql;
Dlaczego u?ywasz dynamicznego SQL'a, gdy nie musisz?
> CREATE TRIGGER document_remove BEFORE DELETE ON document FOR EACH ROW
> EXECUTE PROCEDURE document_remove();
Takie operacje lepiej wykonywa? w triggerze AFTER. Te operacje trwaj? a mo?e si?
okaza?, ?e rekord jest nieskasowany.
> Okazuje si?, ?e wykonanie zapytania SQL w postaci:
> DELETE FROM document WHERE document_id=xxx;
>
> za pierwszym razem nie wykonuje si?. Kasowane s? za to stosowne rekordy
> w tabeli files.
Tak si? dzieje w tylko w jednym przypadku - gdy funkcja triggera zwraca warto??
NULL.
Albo kod tej funkcji ?le przepisa?e? (a przepisywa?e?, nie jest on wklejany ;)
), albo masz jeszcze innego trigera before na tabeli documenty.
> Ponowne wywo?anie usuwa ??dany dokument. Reasumuj?c:
> je?li jest odpowiadaj?cy rekord w tabeli files to usuni?cie rekordu w
> document nie powiedzie si?, je?li tabela files nie ma rekordów
> spe?niaj?cych warunek usuni?cia, wszystko zadzia?a poprawnie.
P.S. Czyta?em Twój drugi post, ale tamto t?umaczenie mnie ne przekonuje -
je?liby co? postgresowi nie pasowa?o, zg?osi?by b??d i wycofa? CA?? transakcj?.
--
P.M.
Marek - 14-12-2006 16:09
> Dlaczego u?ywasz dynamicznego SQL'a, gdy nie musisz?
Hmmm, tu nast?pi?o zastanowienie ...
Nie mam bladego poj?cia jak inaczej zrealizowa? usuwanie rekordów powi?zanej
tabeli je?li musi ona spe?ni? wi?cej ni? jeden warunek ... tzn. np. tak jak
w tym przypadku document_type_id=2. Masz jak?? lepsz? koncepcj? ?
> Takie operacje lepiej wykonywa? w triggerze AFTER. Te operacje trwaj? a
> mo?e si?
> okaza?, ?e rekord jest nieskasowany.
Tak, masz racj?. Chyba kiepski dzie? mia?em ... To rozwi?zuje problem. Ju?
przetestowa?em.
> P.S. Czyta?em Twój drugi post, ale tamto t?umaczenie mnie ne przekonuje -
> je?liby co? postgresowi nie pasowa?o, zg?osi?by b??d i wycofa? CA??
> transakcj?.
W?a?nie nie - ju? przetestowa?em. Chodzi o to, ?e gdy wy??czy?em wi?zy
integralno?ci na polu thumbnail_id to wszystko cacy dzia?a. Przypuszczam, ?e
w tym przypadku powstawa? kociokwik i autorzy Postgresa nie wzi?li pod uwag?
fantazji programisty :-) Zauwa? co si? dzieje:
1. kasujemy dokument
2. wyzwalany jest trigger "before", który usuwa rekord w files
3. wi?zy integralno?ci update'uj? pole thumbnail_id w rekordzi, który jest
w?a?nie usuway
Czyli próbuj? updateowa? rekord, który jest w trakcie usuwania. Jaka? chora
kombinacja powstaje i pewnie nie przewidywano, ?e mo?na w jednym czasie
realizowa? dwie sprzeczne ze sob? operacje na jednym rekordzie.
=?ISO-8859-2?Q?Pawe=B3_Matejski?= - 14-12-2006 16:09
Marek wrote:
>> Dlaczego u?ywasz dynamicznego SQL'a, gdy nie musisz?
>
> Hmmm, tu nast?pi?o zastanowienie ...
> Nie mam bladego poj?cia jak inaczej zrealizowa? usuwanie rekordów
> powi?zanej tabeli je?li musi ona spe?ni? wi?cej ni? jeden warunek ...
> tzn. np. tak jak w tym przypadku document_type_id=2. Masz jak?? lepsz?
> koncepcj? ?
Chyba si? nie zrozumieli?my.
Zamiast:
EXECUTE 'DELETE FROM files WHERE document_id=' || OLD.document_id || ' AND
document_type_id=2';
Napisz:
DELETE FROM files WHERE document_id=OLD.document_id AND document_type_id=2;
To jest wydajniejsze, bo prepare robione jest raz na sesj?, a nie przy ka?dym
wykonaniu funkcji.
>> P.S. Czyta?em Twój drugi post, ale tamto t?umaczenie mnie ne przekonuje -
>> je?liby co? postgresowi nie pasowa?o, zg?osi?by b??d i wycofa? CA??
>> transakcj?.
>
> W?a?nie nie - ju? przetestowa?em. Chodzi o to, ?e gdy wy??czy?em wi?zy
> integralno?ci na polu thumbnail_id to wszystko cacy dzia?a.
> Przypuszczam, ?e w tym przypadku powstawa? kociokwik i autorzy Postgresa
> nie wzi?li pod uwag? fantazji programisty :-) Zauwa? co si? dzieje:
> 1. kasujemy dokument
> 2. wyzwalany jest trigger "before", który usuwa rekord w files
> 3. wi?zy integralno?ci update'uj? pole thumbnail_id w rekordzi, który
> jest w?a?nie usuway
>
> Czyli próbuj? updateowa? rekord, który jest w trakcie usuwania. Jaka?
> chora kombinacja powstaje i pewnie nie przewidywano, ?e mo?na w jednym
> czasie realizowa? dwie sprzeczne ze sob? operacje na jednym rekordzie.
Hmmm... to niedobrze - bo powinien w takim wypadku zaprotestowa? je?li mu si?
co? nie podoba i wycofa? transakcj?. A w której wersji tak masz?
--
P.M.
Marek - 14-12-2006 16:09
> Chyba si? nie zrozumieli?my.
> Zamiast:
> EXECUTE 'DELETE FROM files WHERE document_id=' || OLD.document_id || '
> AND
> document_type_id=2';
> Napisz:
> DELETE FROM files WHERE document_id=OLD.document_id AND
> document_type_id=2;
> To jest wydajniejsze, bo prepare robione jest raz na sesj?, a nie przy
> ka?dym
> wykonaniu funkcji.
No tak ... nie zrozumielimy si?. Zastosowa?em execute z tego wzgl?du gdy?
nie bardzo rozumiem ró?nic?. Czy poj?cie sesji w Postgesie wyznacza otwarcie
po??czenia a? do jego zamkni?cia (w PHP pg_close() lub zamkni?cie skryptu)?
Je?li tak to je?li w trakcie trwania sesji wywo?am 10x usuwanie ró?nych
dokumentów to czy przypadkiem ta druga metoda (DELETE FROM files) nie
spowoduje, ?e nast?pi 10krotna próba kasowania pliku skojarzonego z
pierwszym usuwanym dokumentem?
> Hmmm... to niedobrze - bo powinien w takim wypadku zaprotestowa? je?li mu
> si?
> co? nie podoba i wycofa? transakcj?. A w której wersji tak masz?
8.1.5
Po zmianie triggera z before na after wszystko jest ok. Ten niepotrzebny
update jest wykonywany gdy? rekord jeszcze w tym przypadku istnieje.
hubert depesz lubaczewski - 14-12-2006 16:09
On 2006-12-06, Marek <marek1967@spam.interia.pl> wrote:
> No tak ... nie zrozumielimy si?. Zastosowa?em execute z tego wzgl?du gdy?
> nie bardzo rozumiem ró?nic?. Czy poj?cie sesji w Postgesie wyznacza otwarcie
> po??czenia a? do jego zamkni?cia (w PHP pg_close() lub zamkni?cie skryptu)?
> Je?li tak to je?li w trakcie trwania sesji wywo?am 10x usuwanie ró?nych
> dokumentów to czy przypadkiem ta druga metoda (DELETE FROM files) nie
> spowoduje, ?e nast?pi 10krotna próba kasowania pliku skojarzonego z
> pierwszym usuwanym dokumentem?
oczywi?cie, ?e nie.
execute u?ywasz tylko do dynamicznych zapyta?. w szczególno?ci - gdy
zmienia ci si? nazwa pola lub tabeli w zale?no?ci od danych.
depesz
--
http://www.depesz.com/ -> nowy, jeszcze lepszy, depesz
Marek - 14-12-2006 16:09
> oczywi?cie, ?e nie.
> execute u?ywasz tylko do dynamicznych zapyta?. w szczególno?ci - gdy
> zmienia ci si? nazwa pola lub tabeli w zale?no?ci od danych.
Rozumiem ... czyli zapytanie dynamiczne nale?y rozumie? w tym przypadku
jako zmiane jego struktury a nie tylko warto?ci parametrów?
A jak wygl?da kwestia sesji? Co ona oznacza w Postgresie ?
hubert depesz lubaczewski - 14-12-2006 16:09
On 2006-12-06, Marek <marek1967@spam.interia.pl> wrote:
>> oczywi?cie, ?e nie.
>> execute u?ywasz tylko do dynamicznych zapyta?. w szczególno?ci - gdy
>> zmienia ci si? nazwa pola lub tabeli w zale?no?ci od danych.
> Rozumiem ... czyli zapytanie dynamiczne nale?y rozumie? w tym przypadku
> jako zmiane jego struktury a nie tylko warto?ci parametrów?
dok?adnie.
> A jak wygl?da kwestia sesji? Co ona oznacza w Postgresie ?
nie rozumiem. jakie? szczegó?y?
depesz
--
http://www.depesz.com/ -> nowy, jeszcze lepszy, depesz
Marek - 14-12-2006 16:09
>> A jak wygl?da kwestia sesji? Co ona oznacza w Postgresie ?
>
> nie rozumiem. jakie? szczegó?y?
Nawiaza?em do mojej dyskusji z Paw?em - s?dzi?em, ?e rozwijasz ten w?tek
w?a?nie. Ok, powtórz?: Pawe? wspomnia? o tym, ?e jaki? tam mechanizm dzia?a
raz na sesj?. Nigdzie natomiast nie znalaz?em (o czym te? napisa?em w
odpowiedzi) definicji sesji Postgresa. Co to takiego w zasadzie? Co wyznacza
jej pocz?tek i koniec?
hubert depesz lubaczewski - 14-12-2006 16:09
On 2006-12-07, Marek <marek1967@spam.interia.pl> wrote:
> Nawiaza?em do mojej dyskusji z Paw?em - s?dzi?em, ?e rozwijasz ten w?tek
> w?a?nie. Ok, powtórz?: Pawe? wspomnia? o tym, ?e jaki? tam mechanizm dzia?a
> raz na sesj?. Nigdzie natomiast nie znalaz?em (o czym te? napisa?em w
> odpowiedzi) definicji sesji Postgresa. Co to takiego w zasadzie? Co wyznacza
> jej pocz?tek i koniec?
po??cznie i roz??czenie.
depesz
--
http://www.depesz.com/ -> nowy, jeszcze lepszy, depesz
Marek - 14-12-2006 16:09
> po??cznie i roz??czenie.
Czyli je?li dobrze rozumiem: w trakcie 1 sesji np. skryptu PHP mo?na
ustanowi? wielokrotnie sesj? bazy gdy otworz? równolegle lub kolejno kilka
po??cze??
hubert depesz lubaczewski - 14-12-2006 16:09
On 2006-12-07, Marek <marek1967@spam.interia.pl> wrote:
> Czyli je?li dobrze rozumiem: w trakcie 1 sesji np. skryptu PHP mo?na
> ustanowi? wielokrotnie sesj? bazy gdy otworz? równolegle lub kolejno kilka
> po??cze??
tak, tylko nie wiem po co ci to.
depesz
--
http://www.depesz.com/ -> nowy, jeszcze lepszy, depesz
Marek - 14-12-2006 16:09
> tak, tylko nie wiem po co ci to.
Po to by mie? jasno?? poj?cia "sesji" czytaj?c dokumentacj? Postgresa :-)
zanotowane.pldoc.pisz.plpdf.pisz.pleffulla.pev.pl
|
[PostgreSQL] - jak =?ISO-8859-2?Q?zabezpieczy=E6_interesy_tw?==?ISO-8859-2?Q?=F3rcy_systemu_=3F=3F=3F?=
postgresql - int/int
postgresql Select count(*) czy raczej Select count(ID)
[PostgreSQL] jak =?ISO-8859-2?Q?pobra=E6_warto=B6=E6_zwracan?==?ISO-8859-2?Q?=B1_przez_funkcj=EA=3F?=
[postgresql] INSERT OR UPDATE - jak =?ISO-8859-2?Q?b=EAdzie_na?==?ISO-8859-2?Q?jlepiej=3F?=
[postgresql] kilka =?ISO-8859-2?Q?rekord=F3w_subquery_jako_?==?ISO-8859-2?Q?string?=
[PostgreSQL] Jak =?ISO-8859-2?Q?po=B3=B1czy=E6_funkcje_z_w?==?ISO-8859-2?Q?idokiem?=
Postgres - replikcja master-master
Dopasowanie do "najlepszego" dopasowania :) [ PostgreSQL]
Problemy z =?ISO-8859-2?Q?instalacj=B1_PostgreSQL_na_syste?==?ISO-8859-2?Q?mach_Windows?=
zanotowane.pldoc.pisz.plpdf.pisz.pldirtyboys.xlx.pl
Cytat
Decede mihi sole - nie zasłaniaj mi słonca.
Gdy kogoś kochasz, jesteś jak stworzyciel świata - na cokolwiek spojrzysz, nabiera to kształtu, wypełnia się barwą, światłem. Powietrze przytula się do ciebie, choćby był mróz, a ty masz w sobie tyle radości, że musisz ją rozdawać wokoło, bo się w tobie nie mieści
Hoc fac - tak czyń.
A tergo - od tyłu; z tyłu.
I czarne włosy posiwieją. Safona |
|
