PostgreSQL, tabele tymczasowe w funkcjach plpgsql
k.racz...@logifact.pl
A teraz do sedna :)
Napisałem sobie funkcję w plpgsql, która do celów optymalizacji
działania używa wewnętrznie tabeli tymczasowej.
Samo tworzenie tabeli wygląda tak:
IF NOT EXISTS(
SELECT 1 FROM
pg_catalog.pg_class
WHERE
relkind = 'r'
AND relname = 'my_temp_tab'
AND pg_table_is_visible(oid))
THEN
CREATE LOCAL TEMP TABLE my_temp_tab(id int) ON COMMIT DELETE ROWS;
END IF;
Mam nadzieje, że nie poszadkowane za bardzo ... :)
Czy ktoś widzi jakieś zagrożenia ?? testowałem sobie na dwóch różnych
sesjach i nie zauważyłem specjalnie problemów .., ale może są jakieś
zależności czasowe, których nie przewidziałem :)
A może jest prostszy sposób sprawdzenia czy mam już tabelkę tymczasową
czy jeszcze jej nie ma ... ? :)
--
Pozdrawiam
K. Raczkowski
Cezary Statkiewicz
> Czy ktoś widzi jakieś zagrożenia ?? testowałem sobie na dwóch różnych
> sesjach i nie zauważyłem specjalnie problemów .., ale może są jakieś
> zależności czasowe, których nie przewidziałem :)
>
> A może jest prostszy sposób sprawdzenia czy mam już tabelkę tymczasową
> czy jeszcze jej nie ma ... ? :)
tak, utwórz na początku sesji. tabele tymczasowe są widoczne tylko dla
obecnej sesji i znikają po zakończeniu tejże.
http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html
CS
--
Cezary Statkiewicz - http://thelirium.net
rlu#280280 gg#5223219
jabber://koo...@jabber.wp.pl
hubert depesz lubaczewski
> Czy ktoś widzi jakieś zagrożenia ?? testowałem sobie na dwóch różnych
> sesjach i nie zauważyłem specjalnie problemów .., ale może są jakieś
> zależności czasowe, których nie przewidziałem :)
odpal to 2 razy w tej samej sesji.
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
k.racz...@logifact.pl
> Dnia 28.12.2007 k.raczkow...@logifact.pl <k.raczkow...@logifact.pl> napisał/a:
>
> > Czy ktoś widzi jakieś zagrożenia ?? testowałem sobie na dwóch różnych
> > sesjach i nie zauważyłem specjalnie problemów .., ale może są jakieś
> > zależności czasowe, których nie przewidziałem :)
>
> odpal to 2 razy w tej samej sesji.
>
No odpaliłem ... zakładam że okienko SQL w pgAdminIII cały czas trzyma
sesję (na to przynajmnie wygląda podglądając tabelę pg_class) ...
Na razie nic złowrogiego nie widzę.
Jak dobrze rozumiem to:
1) Każda sesja ma własne tabele tymczasowe
2) Moja tabela będzie czyszczona po zakończeniu funkcji
Więc zastanawiam się czy gdzieś nie ma jakiejś pułapki zanim uruchomię
to bardziej produkcyjnie :D
--
Pozdr
K.R.
k.racz...@logifact.pl
> k.raczkow...@logifact.pl wrote:
> > Czy ktoś widzi jakieś zagrożenia ?? testowałem sobie na dwóch różnych
> > sesjach i nie zauważyłem specjalnie problemów .., ale może są jakieś
> > zależności czasowe, których nie przewidziałem :)
>
> > A może jest prostszy sposób sprawdzenia czy mam już tabelkę tymczasową
> > czy jeszcze jej nie ma ... ? :)
>
> tak, utwórz na początku sesji. tabele tymczasowe są widoczne tylko dla
> obecnej sesji i znikają po zakończeniu tejże.
>
Ale to nie jest z mojego punktu widzenia wygodne :) bo chciałbym dać
użytkownikowi funkcje zwracającą rowset, i żeby nie interesowało go to
jak ona działa w środku
--
Pozdr.
K.R.
k.racz...@logifact.pl
Ale jednego nie rozumiem ....
funkcja przyjmuje jeden argument, w zależności od niego zwróci inną
ilość wierszy (tak w uproszczeniu...) zrobiłem takie wywołanie z
pgAdmin
begin; --tranzakcja !!
select * from moja_f(10) as r(definijca tabeli ..);
select * from moja_f(20) as r(definijca tabeli ..);
commit;
10, 20 - to argumenty ..., w komunikatach dostałem:
W wyniku zapytania wyrzucono 42 wierszy.
W wyniku zapytania wyrzucono 2556 wierszy.
No i tego nie rozumiem :D bo wydawało mi się że zadziała źle... skoro
mam tabelę tymczasową zdefiniowaną z parametrem ON COMMIT DELETE ROWS.
To w końcu w PG są zagnieżdżone transakcje czy ich nie ma ?? :D Bo
działanie sugeruje że są ...
--
Pozdr
K.R.
k.racz...@logifact.pl
> funkcja przyjmuje jeden argument, w zależności od niego zwróci inną
> ilość wierszy (tak w uproszczeniu...) zrobiłem takie wywołanie z
> pgAdmin
Ech .. trochę brak mi precyzji, w zależności od argumentu czym innym
będzie wypełniona tabela tymczasowa ..
--
Pozdr
K.R.
hubert depesz lubaczewski
> sesję (na to przynajmnie wygląda podglądając tabelę pg_class) ...
> Na razie nic złowrogiego nie widzę.
pokaż całą funkcją a prynajmniej to gdzie/jak piszesz/czytasz z tej
tabeli.
k.racz...@logifact.pl
> Dnia 28.12.2007 k.raczkow...@logifact.pl <k.raczkow...@logifact.pl> napisał/a:
>
> > No odpaliłem ... zakładam że okienko SQL w pgAdminIII cały czas trzyma
> > sesję (na to przynajmnie wygląda podglądając tabelę pg_class) ...
> > Na razie nic złowrogiego nie widzę.
>
> pokaż całą funkcją a prynajmniej to gdzie/jak piszesz/czytasz z tej
> tabeli.
>
Bardzo proszę i z góry dziękuję za zainteresowanie :)
-- Function: show_matrix(integer, integer)
-- DROP FUNCTION show_matrix(integer, integer);
CREATE OR REPLACE FUNCTION show_matrix(a_user_id integer,
a_producttype_id integer)
RETURNS SETOF record AS
$BODY$
DECLARE
row RECORD;
BEGIN
-------------------------------------------
--fabryki których matrix user może widzieć
-------------------------------------------
IF NOT EXISTS( SELECT 1 FROM pg_catalog.pg_class WHERE relkind = 'r'
AND relname = 'temp_user_factories' AND pg_table_is_visible(oid)) THEN
CREATE LOCAL TEMP TABLE temp_user_factories(id int) ON COMMIT DELETE
ROWS;
END IF;
INSERT INTO temp_user_factories(id)
SELECT distinct priv.factory_id
FROM
app_privileges_groups priv
JOIN groups_users gru ON
gru.group_id = priv.group_id
AND gru.user_id = a_user_id
AND priv.app_privilege like 'matrix%';
FOR row IN
SELECT DISTINCT
mx.id AS mx_id,
mx.product_id,
mx.mp_id,
mx.tp_id,
p.sku,
p.product_type_id,
p.description,
mp.name AS mp_name
FROM
matrix AS mx
JOIN products AS p ON mx.product_id = p.id
JOIN factories AS mp ON mx.mp_id = mp.id
WHERE
mx.tp_id in (select id from temp_user_factories)
OR mx.mp_id in (select id from temp_user_factories)
ORDER BY
p.description
LOOP
RETURN NEXT row;
END LOOP;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION show_matrix(integer, integer) OWNER TO postgres;
hubert depesz lubaczewski
> -- Function: show_matrix(integer, integer)
> -- DROP FUNCTION show_matrix(integer, integer);
> ROWS;
> SELECT distinct priv.factory_id
> FROM
> app_privileges_groups priv
> JOIN groups_users gru ON
> gru.group_id = priv.group_id
> AND gru.user_id = a_user_id
> AND priv.app_privilege like 'matrix%';
nie czyścisz tej tabeli przy wyjściu z funkcji.
czyli jak 2 razy wywołasz w jednej transakcji to będziesz miał więcej
rekordów niż przewidziane w temp_user_factories.
k.racz...@logifact.pl
> Dnia 28.12.2007 k.raczkow...@logifact.pl <k.raczkow...@logifact.pl> napisał/a:
>
> > Bardzo proszę i z góry dziękuję za zainteresowanie :)
> > -- Function: show_matrix(integer, integer)
> > -- DROP FUNCTION show_matrix(integer, integer);
> > CREATE LOCAL TEMP TABLE temp_user_factories(id int) ON COMMIT DELETE
> > ROWS;
> > INSERT INTO temp_user_factories(id)
> > SELECT distinct priv.factory_id
> > FROM
> > app_privileges_groups priv
> > JOIN groups_users gru ON
> > gru.group_id = priv.group_id
> > AND gru.user_id = a_user_id
> > AND priv.app_privilege like 'matrix%';
>
> nie czyścisz tej tabeli przy wyjściu z funkcji.
> czyli jak 2 razy wywołasz w jednej transakcji to będziesz miał więcej
> rekordów niż przewidziane w temp_user_factories.
>
OK, masz rację to jest problem, odpalałem z takimi argumentami że
najpierw było tam mniej rekordów a potem więcej ... więc wychodziło
tak jak chciałem :)
No ale akurat na tym przypadku z jawną transakcją mi nie zależało :)
więc drobna poprawka TRUNCATE TABLE na końcu zdecydowanie pomogła -
dziękuję :)
Rozumiem, że tego też dotyczył Twój post o odpaleniu 2x w sesji ??
--
Pozdr.
K.R.
hubert depesz lubaczewski
> więc drobna poprawka TRUNCATE TABLE na końcu zdecydowanie pomogła -
> dziękuję :)
dałbym raczej na początku. bezpieczniej.
> Rozumiem, że tego też dotyczył Twój post o odpaleniu 2x w sesji ??
nie. źle doczytałem jedną rzecz. ogólnie tabele tymczasowe i funkcje się
nie za bardzo lubią, ale tak jak je zastosowałeś - zadziała.
Herakles
Każda tabela ma swój oid.
Przy pierwszym odpaleniu funkcji w danej sesji zostaje ona skąpilowana i
potem używa już tylko oidów. Jeżeli dropniesz tabelę i stworzysz od nowa to
się wyfaka.
Rozwiązaniem jest create or replace function lub execute.
I jeszcze jedno, w pg_class widzisz wszystkie tabele wszytkich userów i
sesji.
Krzysztof Raczkowski
Witaj,
No tak ... ale w moim przykładzie nie ma ON COMMIT DROP - tak jak chyba
sugerujesz. Przykład uwzględnia właśnie to, że funkcje są kompilowane
raz dla sesji.
Co do pg_class, wiem że są tam wszystkie tabele ... dlatego użyłem:
AND pg_table_is_visible(oid)
,która mi gwarantuje że znajdę tabelę tymczasową należącą do mojej sesji.
--
Pozdrawiam
K. Raczkowski
Ronald Kuczek
> A może jest prostszy sposób sprawdzenia czy mam już tabelkę tymczasową
> czy jeszcze jej nie ma ... ? :)
>
A może zamiast tego obejścia zrobić to bez efektów specjalnych, bez
tabelki tymczasowej, po prostu SRF ?
Zapytania trzeba by oczywiście przemyśleć i wydajnościowo
zoptymalizować, ale to chyba da się zrobić ?
Pozdrawiam
Rony
k.racz...@logifact.pl
> k.raczkow...@logifact.pl pisze:> A może jest prostszy sposób sprawdzenia czy mam już tabelkę tymczasową
Witaj,
Hmmm ... :) w sumie ... nie pomyślałem o tym :) Mam trochę nawyków z
MSSQL. Tam użycie tabelki tymczasowej lub zmiennej tabelarycznej do
optymalizacji jest trywialne (szczególnie tab. tymczasowej).
Faktycznie jak dobrze rozumiem SRF działała by analogicznie do
zmiennej tabelarycznej (czyli jak się da to wszystko w RAM lub swap).
Bo tabele tymczasowe w PostgreSQL zapewne zjadają trochę IO .. :)
--
Pozdrawiam
K. Raczkowski