PL/pgSQL jak =?ISO-8859-2?Q?u=BFy=E6_transakcji=2E?=
Mateo - 27-06-2006 00:07
PL/pgSQL jak =?ISO-8859-2?Q?u=BFy=E6_transakcji=2E?=
Witam wszystkich serdecznie.
Piszę w Javie aplikację w której warstwa DAO jest oparta o Hibernate.
Bazą danych jest PostgreSQL 8.0. Niestety teraz natrafiłem na problem
wydajnościowy i muszę użyć procedury składowanej. Nie znam prawie wcale
języka PL/pgSQL więc korzystam z dostepnej na stronie:
www.postgresql.org dokumentacji. Oto aktualny stan moich prób:
--Funkcja testowa: testuje update dla wszystkich tabel dotyczacych Normy
Materialowej (przy braku przeliczania detali w pliku XLS)
CREATE OR REPLACE FUNCTION test_UpdateMaterialNorm(tablicaKodowPd
varchar[], ileKodowWystepuje integer, newIDSummary integer) RETURNS int
AS $$
DECLARE
--Zadeklaruj potrzebne zmienne
i int;
idPipeDetail int;
status integer := 0; -- staus poprawnosci wykonania funkcji
BEGIN
-- Wstawienie w petli rekordow do tabeli normamaterialowaDane
FOR i IN 1..ileKodowWystepuje LOOP
SELECT id FROM pipedetail WHERE code = tablicaKodowPd[i] into
idPipeDetail;
INSERT INTO normamaterialowadane (id,pipedetail_id, summary_id)
VALUES
(nextval('hibernate_sequence'),idPipeDetail, newIDSummary);
/*IF EXCEPTION THEN
--Wystapil wyjatek
ROLLBACK;
RETURN 1;
END IF;
*/
END LOOP;
--COMMIT;
RETURN 0;
END;
$$ LANGUAGE plpgsql;
--Funkcja testowa: testuje wywolanie funkcji: test_UpdateMaterialNorm
CREATE OR REPLACE FUNCTION test_Invoke_UpdateMaterialNorm() RETURNS int
AS $$
DECLARE
--Zadeklaruj tablice kodow PipeDetail
tablicaKodow varchar[] :=
ARRAY['D.10.900.000.10.*.*.test','D.35.500.500.16.*.*.** *','D.35.500.400.16.*.*.***'];
summaryID integer := 13422;
wynik integer;
BEGIN
SELECT test_UpdateMaterialNorm(tablicaKodow, 3, 13422) into wynik;
RETURN wynik;
END;
$$ LANGUAGE plpgsql;
/* Wywolanie
SELECT test_Invoke_UpdateMaterialNorm();
*/
Pierwsza funkcja wykonuje pewne przetwarzanie na bazie, druga służy do
jej przetestowania (potem w aplikacji będzie wywoływana tylko pierwsza
funkcja). W testowanej funkcji będą docelowo działania na 7-miu tabelach
, a nie tak jak jest teraz na 1-nej. Testowana funkcja działa poprawnie
(robi inserty na tabeli opierając się na tablicy przekazanych kodów).
Mój problem polega na tym, że nie wiem jak zadeklarować transakcję.
Próbowałem: START TRANSACTION ... COMMIT wtedy funkcja poprawnie
zapisywała się do bazy bez komunikatu błedu, ale podczas wykonania był
błąd, że nie można utworzyć kontekstu transakcji. Stąd moje pytanie jak
w PL/pgSQL użyć transakcji (nie chodzi mi o autocommit po każdej
operacji) wykryć, że coś poszło nie tak i w takiej sytuacji zrobić
ROLLBACK, a gdy wszystko będzie OK to COMMIT.
Za wszelkie konstruktywne podpowiedzi szczerze dziękuję.
Pozdrawiam,
Mateo.
hubert depesz lubaczewski - 27-06-2006 00:08
On 2006-06-26, Mateo <mateo@gentoo4you.neostrada.pl> wrote:
> Mój problem polega na tym, że nie wiem jak zadeklarować transakcję.
> Próbowałem: START TRANSACTION ... COMMIT wtedy funkcja poprawnie
nie da się.
musiałbyś mieć subtransactions, a na razie ich nie ma w postgresie.
weź pod uwagę, że co byś nie robił i tak całe wywołanie masz w
transakcji.
tzn. jak odpalasz:
select funkcja();
to tak naprawdę jest to wykonywane w taki sposób:
begin;
select funkcja();
commit;
jeśli coś się wywali w trakcie, to cała ta niejawna transakcja się
zrollbackuje.
jeśli potrzebujesz coś więcej, i uzywasz postgresa 8.1, to mozeliwe, ze
pomoze ci przechwytywanie wyjątków w funkcjach plpgsql. opis w manualu,
w rozdziale: Chapter 36. PL/pgSQL - SQL Procedural Language / 36.7.
Control Structures / 36.7.5. Trapping Errors
depesz
--
http://www.depesz.com/index.php/2006/06/22/choroby/
Robert Osowiecki - 27-06-2006 00:08
hubert depesz lubaczewski napisał(a):
> nie da się.
> musiałbyś mieć subtransactions, a na razie ich nie ma w postgresie.
W najnowszych wersjach są savepointy, one działają podobnie...
Pseudokod:
BEGIN;
Instrukcje...
....
....
SAVEPOINT alamakota;
Instrukcje...
....
....
....
IF (błąd) THEN
ROLLBACK TO alamakota; /* transakcja wraca do stanu przy wykonaniu
savepointa */
END IF;
COMMIT;
Tym niemiej: nie mogą być (podobnie jak transakcje) kontrolowane z
poziomu funkcji wbudowanych, a jedynie z poziomu aplikacji. Mogą się
przydać, jeśli uda się operacje między savepointami zapisać w sensownej
postaci i nie będą one wykonywane zbyt często (jak rozumiem, problem
wydajnościowy był na linii aplikacja-Postgres).
R.
Adam Buraczewski - 27-06-2006 00:08
Mateo <mateo@gentoo4you.neostrada.pl> wrote:
> Piszę w Javie aplikację w której warstwa DAO jest oparta o Hibernate.
> Bazą danych jest PostgreSQL 8.0. Niestety teraz natrafiłem na problem
> wydajnościowy i muszę użyć procedury składowanej.
[...]
> Mój problem polega na tym, że nie wiem jak zadeklarować transakcję.
> Próbowałem: START TRANSACTION ... COMMIT wtedy funkcja poprawnie
> zapisywała się do bazy bez komunikatu błedu, ale podczas wykonania był
> błąd, że nie można utworzyć kontekstu transakcji. Stąd moje pytanie jak
> w PL/pgSQL użyć transakcji (nie chodzi mi o autocommit po każdej
> operacji) wykryć, że coś poszło nie tak i w takiej sytuacji zrobić
> ROLLBACK, a gdy wszystko będzie OK to COMMIT.
Tak jak Ci już depesz odpowiedział, w PL/pgSQL, a właściwie w żadnym
języku proceduralnym w Postgresie nie można jawnie stosować
transakcji, za to cała procedura/funkcja, wraz ze wszystkimi
poleceniami i funkcjami w niej wywołanymi wykonuje się zawsze w jednej
transakcji. Co nie znaczy, że nie możesz wykorzystać istniejących
mechanizmów do realizacji celu.
Począwszy od wersji 8.0 są do dyspozycji savepointy i transakcje
zagnieżdżane. Te pierwsze doskonale współpracują z Javą i JDBC
(obejrzyj sobie funkcje setSavepoint() i rollback(Savepoint) z
interfejsu java.sql.Connection), szkoda tylko że Hibernate tego nie
wykorzystuje. Idea jest bardzo prosta: rozpoczynasz transakcję (START
TRANSACTION albo BEGIN, a w Javie, w JDBC po prostu zaczynasz działać
na bazie ustawiwszy connection.setAutoCommit(false)), potem w jakimś
momencie wykonujesz polecenie SQL:
SAVEPOINT nazwa;
lub w przypadku JDBC robisz coś w stylu:
Savepoint nazwa = connection.setSavepoint();
a następnie wykonujesz różne operacje na bazie i gdy wystąpi błąd lub
nie podoba Ci się to co zrobiłeś, możesz się cofnąć do danej etykiety:
ROLLBACK TO nazwa;
a w JDBC:
connection.rollback(nazwa);
i co najlepsze, nie kończysz wtedy jeszcze transakcji i możesz dalej
działać, tylko to co robiłeś od stworzenia savepointa zostaje
wycofane.
Raz stworzony savepoint możesz wykorzystywać wielokrotnie w ramach
transakcji, za każdym razem wycofując zmiany w bazie. Potem możesz
zwolnić ten savepoint:
RELEASE nazwa;
i odpowiednio dla JDBC:
connection.releaseSavepoint(nazwa);
W Javie zwykle obudowuje się to w odpowiednią struktrurę
try..catch..finally, coś w stylu:
Savepoint sp = connection.setSavepoint();
try {
// Tu robisz coś na bazie
} catch (SQLException e) {
// Coś nie poszło, trzeba cofnąć.
connection.rollback(sp);
} finally {
connection.releaseSavepoint(sp);
}
W innych językach nie widziałem takiego zgrabnego interfejsu do
savepointów, niestety.
Savepointy możesz stosować także wewnątrz funkcji PL/pgSQL i w innych
językach PL/cośtam, o ile te języki na to pozwalają, ale pod kilkoma
obwarowaniami:
1. wewnątrz procedury możesz zrobić RELEASE i ROLLBACK TO tylko dla
savepointów zadeklarowanych lokalnie, wewnątrz danego wywołania tej
procedury (nie można więc np. cofnąć się do savepointu utworzonego
przed wywołaniem tej procedury),
2. jeżeli wewnątrz procedury stosujesz transakcje zagnieżdżone, to
możesz cofnąć się jedynie do savepointów zadeklarowanych w ramach
tej samej transakcji zagnieżdżonej,
3. gdy procedura kończy działanie, to savepointy w niej zadeklarowane
są zwalniane -- nie można się już do nich cofnąć, podobnie gdy
kończysz transakcję zagnieżdżoną, możesz za to wówczas się znowu cofać
do savepointów zadeklarowanych przed rozpoczęciem tej procedury
(transakcji zagnieżdżonej),
4. Jak kończysz całą transakcję za pomocą COMMIT lub ROLLBACK, to
wszystkie savepointy są zwalniane.
Jasne? :)
A teraz druga sprawa, jaką są transakcje zagnieżdżone. Nie można ich
użyć z poziomu SQL, tylko w procedurach pisanych w językach
proceduralnych (w C też). Służą do obsługi błędów, żeby nie trzeba
było kończyć całej transakcji gdy coś nie pójdzie. Skupmy się na
PL/pgSQL. Piszesz po prostu tak:
BEGIN
-- tu coś, co może nie pójść
EXCEPTION
WHEN rodzaj_błędu THEN
-- tutaj obsługa tego błędu
END;
Słowo BEGIN rozpoczyna transakcję zagnieżdżoną. Gdy coś się stanie,
procedura przechodzi do obsługi błędu (ale tylko dla błędów
wymienionych we frazie EXCEPTION, inne nie są obsłużone i powodują
przerzucenie tego błędu do bardziej zewnętrznej transakcji
zagnieżdżonej itp. i w końcu mogą zakończyć całą transakcję). Zwróć
uwagę, że tu nie ma COMMIT czy ROLLBACK -- transakcje zagnieżdżone,
które nie zakończą się błędem są zatwierdzane, a te z błędem --
wycofywane. Możesz to połączyć z savepointami, ale wg zasad, które
podałem wcześniej, pamiętając tylko że to co jest po WHEN wykonuje się
już w transakcji otaczającej. Przykład:
SAVEPOINT aaa;
-- Tu coś robisz...
BEGIN -- początek transakcji zagnieżdżonej
SAVEPOINT bbb;
-- Znowu coś robisz...
IF nie podoba się coś THEN
ROLLBACK TO bbb;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO aaa; -- cofamy się dalej
END;
Mam nadzieję, że wyjaśniłem :) Tak na marginesie dodam, że savepointy
są tak naprawdę zrobione wewnątrz Postgresa na transakcjach
zagnieżdżanych, wskutek czego bardzo dużo savepointów, RELEASE'ów itp
w jednej transakcji może spowodować zauważalny spadek wydajności
Postgresa.
A, jeszcze jedno: jeżeli PL/pgSQL by Ci doskwierał, to jest bardzo
ciekawy projekt -- PL/Java, pozwalający na pisanie procedur (w tym
nawet triggerów) w Javie. Niedawno wyszła wersja 1.3.0:
http://wiki.tada.se/wiki/display/pljava/Home
Pozdrawiam!
--
Adam Buraczewski <adamb (at) nor (dot) pl> * Linux user #165585
GCS/TW d- s-:+>+:- a C+++(++++) UL++++$ P++ L++++ E++ W+ N++ o? K w--
O M- V- PS+ !PE Y PGP+ t+ 5 X+ R tv- b+ DI D G++ e+++>++++ h r+>++ y?
Mateo - 28-06-2006 00:08
Mateo wrote:
> Witam wszystkich serdecznie.
>
> Piszę w Javie aplikację w której warstwa DAO jest oparta o Hibernate.
> Bazą danych jest PostgreSQL 8.0. Niestety teraz natrafiłem na problem
> wydajnościowy i muszę użyć procedury składowanej. Nie znam prawie wcale
> języka PL/pgSQL więc korzystam z dostepnej na stronie:
> www.postgresql.org dokumentacji. Oto aktualny stan moich prób:
> [...]
> Za wszelkie konstruktywne podpowiedzi szczerze dziękuję.
>
> Pozdrawiam,
> Mateo.
Serdecznie wszystkim kolegom dziękuję za udzielenie bardzo pomocnych mi
informacji.
Pozdrawiam,
Mateo.
zanotowane.pldoc.pisz.plpdf.pisz.pleffulla.pev.pl
|
Wydajność baz danych w zależności od poziomu izolacji ANSI/ISO
Czy zna (obsługuje) ktoś program Iso Draw ?
strona plus baza w iso do utf-8
Kodowanie: z iso na utf
Konwesja znaków w dump'ie bazy danych - ISO -> utf-8 -> ISO -> utf-8
=?iso-8859-2?q?Co_oznacza_b=B3=B1d_Warning:_mysql=5Fconnect() _[function.mysql-connect]:_Can't_connect_to_local_MySQL_server_through_sock et_'/var/run/mysqld/mysqld.sock'_(2)_in?=
www.fotosearch.pl & www.fotosearch.com >> ceny
[MS SQL 2005] =?windows-1250?Q?Ilo=9C=E6_wiersz=F3w_w_zbiorze_wynikowym?=
[Forum] www.forum.weeb.pl
Photoshop CS/CS PL - Biblia
zanotowane.pldoc.pisz.plpdf.pisz.plmelooonka.opx.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 |
|
