[PGSQL] Funkcje =?ISO-8859-2?Q?zwracaj=B1ce_=27rowset=27_i_?==?ISO-8859-2?Q?tabele_tymczasowe?=
Krzysztof Raczkowski - 11-01-2007 00:08
[PGSQL] Funkcje =?ISO-8859-2?Q?zwracaj=B1ce_=27rowset=27_i_?==?ISO-8859-2?Q?tabele_tymczasowe?=
Cześć,
Temat jest nieśmiertelny ... z góry dziękuję jeżeli ktoś ma gotową odp.
z google .. - ja jeszcze nie znalazłem. Ale do sedna ...
Chciałbym połączyć funkcje zwracające rowset z używaniem w nich tabel
tymczasowych. Jest mi to potrzebne do optymalizacji pewnych
długotrwałych zapytań (praktyka stosowana z MSSQL gdzie rozbija się
skomplikowane zapytania za pomocą tabel tymczasowych).
mam typ:
------------------
CREATE TYPE proc_res AS
(max_dt timestamp,
product_id int4);
------------------
oraz funckję:
------------------
CREATE OR REPLACE FUNCTION proc_test2(firmid int4) RETURNS SETOF proc_res AS
$BODY$
DECLARE
row proc_res%rowtype;
BEGIN
create temp table _fact as select now(), id from factories;
for row in
select now(), id from factories
loop
return next row;
end loop;
drop table _fact;
return;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
------------------
Ta powyższ funkcja działa bez problemu, lecz gdy:
for row in
select now(), id from factories
loop
zamienię na
for row in
select now(), id from _fact
loop
to działa tylko za pierwszym razem (po rekompilacji funkcji) a potem mam:
ERROR: relation with OID 62014 does not exist
CONTEXT: PL/pgSQL function "proc_test2" line 6 at for over select rows
Funkcję odpalam z PGAdmina: select * from proc_test2(1);
Czy ktoś może mi pomóc jak połączyć funkcje zwracające rowset tabelami
tymczasowymi na podobnej zasadzie jak to jest w MSSQL? Czy jest to
możliwe w PGSQL czy mam inaczej podejść do optymalizacji ... Z góry
zaznaczam, że widoki odpadają (chyba że o czymś nie wiem) gdyż do
funkcji muszę przekazywać dodatkowe parametry.
--
Pozdrawiam
K. Raczkowski
hubert depesz lubaczewski - 12-01-2007 00:29
On 2007-01-10, Krzysztof Raczkowski <raczkowk@poczta.wp.pl> wrote:
> to działa tylko za pierwszym razem (po rekompilacji funkcji) a potem mam:
> ERROR: relation with OID 62014 does not exist
> CONTEXT: PL/pgSQL function "proc_test2" line 6 at for over select rows
> Funkcję odpalam z PGAdmina: select * from proc_test2(1);
> Czy ktoś może mi pomóc jak połączyć funkcje zwracające rowset tabelami
> tymczasowymi na podobnej zasadzie jak to jest w MSSQL? Czy jest to
> możliwe w PGSQL czy mam inaczej podejść do optymalizacji ... Z góry
> zaznaczam, że widoki odpadają (chyba że o czymś nie wiem) gdyż do
> funkcji muszę przekazywać dodatkowe parametry.
coś mi tłucze po głowie, że to już było, ale niech tam:
nie możesz użyć "create table"/"drop table" w funkcji którą chcesz
wykonywać wielokrotnie (w połączeniu). treść funkcji jest kompilowana
tylko raz na połączenie i w trakcie kompilacji np. select * from tabelka
jest zmieniane na "select * from <jakis_numerek>" gdzie jakis_numerek to
oid tabelki.
drop tabeli i ponowny create da jej inny numerek.
rozwiazania:
1. zmiana algorytmu
2. uzycie "execute 'select * from tabelka' " zamiast po prostu select * from tabelka
3. użycie tabeli nie tymczasowej
ad. 1 - najlepszy efekt, ale nie znam kodu więc nie wiem na ile realne.
ad. 2 - nie zmieniasz algorytmu, ale execute jest wolniejsze od
normalnych zapytań
ad. 3 - w/g mnie najlepszy kompromis. załóżmy, że w tej tymczasowej
tabelce masz pola "x" i "y". tworzysz tabelę (nie temp!, nie w funkcji!)
ktora zawiera pola x, y i numeryczny identyfikator.
wstawiajac do niej rekordy robisz to tak
insert into tabela (x,y,ident) select costam, costam, pg_backend_pid();
potem odczytujac dane z tabelki dodajesz where ident = pg_backend_pid();
i już.
depesz
--
http://www.depesz.com/ - blog dla ciebie
Herakles - 12-01-2007 00:29
Krzysztof Raczkowski wrote:
> Cześć,
>
> Temat jest nieśmiertelny ... z góry dziękuję jeżeli ktoś ma gotową odp.
> z google .. - ja jeszcze nie znalazłem. Ale do sedna ...
>
> Chciałbym połączyć funkcje zwracające rowset z używaniem w nich tabel
> tymczasowych. Jest mi to potrzebne do optymalizacji pewnych
> długotrwałych zapytań (praktyka stosowana z MSSQL gdzie rozbija się
> skomplikowane zapytania za pomocą tabel tymczasowych).
>
> mam typ:
>
> ------------------
>
> CREATE TYPE proc_res AS
> (max_dt timestamp,
> product_id int4);
>
> ------------------
> oraz funckję:
> ------------------
>
> CREATE OR REPLACE FUNCTION proc_test2(firmid int4) RETURNS SETOF proc_res
> AS $BODY$
> DECLARE
> row proc_res%rowtype;
> BEGIN
> create temp table _fact as select now(), id from factories;
>
> for row in
> select now(), id from factories
> loop
> return next row;
> end loop;
>
> drop table _fact;
> return;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
>
> ------------------
>
> Ta powyższ funkcja działa bez problemu, lecz gdy:
>
> for row in
> select now(), id from factories
> loop
>
> zamienię na
>
> for row in
> select now(), id from _fact
> loop
>
> to działa tylko za pierwszym razem (po rekompilacji funkcji) a potem mam:
>
> ERROR: relation with OID 62014 does not exist
> CONTEXT: PL/pgSQL function "proc_test2" line 6 at for over select rows
>
> Funkcję odpalam z PGAdmina: select * from proc_test2(1);
>
> Czy ktoś może mi pomóc jak połączyć funkcje zwracające rowset tabelami
> tymczasowymi na podobnej zasadzie jak to jest w MSSQL? Czy jest to
> możliwe w PGSQL czy mam inaczej podejść do optymalizacji ... Z góry
> zaznaczam, że widoki odpadają (chyba że o czymś nie wiem) gdyż do
> funkcji muszę przekazywać dodatkowe parametry.
>
> --
> Pozdrawiam
> K. Raczkowski
Wyrzuć create temp table poza ciało funkcji.
Krzysztof Raczkowski - 12-01-2007 00:29
hubert depesz lubaczewski napisał(a):
> On 2007-01-10, Krzysztof Raczkowski <raczkowk@poczta.wp.pl> wrote:
>> to działa tylko za pierwszym razem (po rekompilacji funkcji) a potem mam:
>> ERROR: relation with OID 62014 does not exist
>> CONTEXT: PL/pgSQL function "proc_test2" line 6 at for over select rows
>> Funkcję odpalam z PGAdmina: select * from proc_test2(1);
>> Czy ktoś może mi pomóc jak połączyć funkcje zwracające rowset tabelami
>> tymczasowymi na podobnej zasadzie jak to jest w MSSQL? Czy jest to
>> możliwe w PGSQL czy mam inaczej podejść do optymalizacji ... Z góry
>> zaznaczam, że widoki odpadają (chyba że o czymś nie wiem) gdyż do
>> funkcji muszę przekazywać dodatkowe parametry.
>
> coś mi tłucze po głowie, że to już było, ale niech tam:
:) być może staram się przenieść doświadczenia bezpośrednio z MSSQL ale
to nie do końca dobra droga ..
> nie możesz użyć "create table"/"drop table" w funkcji którą chcesz
> wykonywać wielokrotnie (w połączeniu). treść funkcji jest kompilowana
> tylko raz na połączenie i w trakcie kompilacji np. select * from tabelka
> jest zmieniane na "select * from <jakis_numerek>" gdzie jakis_numerek to
> oid tabelki.
> drop tabeli i ponowny create da jej inny numerek.
>
> rozwiazania:
> 1. zmiana algorytmu
> 2. uzycie "execute 'select * from tabelka' " zamiast po prostu select * from tabelka
> 3. użycie tabeli nie tymczasowej
>
> ad. 1 - najlepszy efekt, ale nie znam kodu więc nie wiem na ile realne.
> ad. 2 - nie zmieniasz algorytmu, ale execute jest wolniejsze od
> normalnych zapytań
> ad. 3 - w/g mnie najlepszy kompromis. załóżmy, że w tej tymczasowej
> tabelce masz pola "x" i "y". tworzysz tabelę (nie temp!, nie w funkcji!)
> ktora zawiera pola x, y i numeryczny identyfikator.
> wstawiajac do niej rekordy robisz to tak
> insert into tabela (x,y,ident) select costam, costam, pg_backend_pid();
> potem odczytujac dane z tabelki dodajesz where ident = pg_backend_pid();
> i już.
>
Mi też 3 wydaje się najlepszym pomysłem i chyba najbardziej niezawodnym.
Trzeba by do tej tabeli tymczasowej dodać jeszcze jakiś timestamp,
żeby w razie wpadki wiedzieć co sprzątać.
Jeszcze jedno, jeżeli w mojej przykładowej funkcji zrobię delete from
tmp_tabela where ident = my_id to nie będzie problemu z rowsetem
zwróconym z funkcji ??
Czy coś takiego:
for row in
select now(), id from tmp_tabela
return next row;
end loop;
Tworzy kopię danych ??
No i jeżeli tak ... to może przy rzadkim uruchamianiu funkcji bardzie
opłaca się zwracać kursor z funkcji zamiast rowset a sprzątać w cronie...
Dziękuję za pomoc :)
--
Pozdrawiam
K. Raczkowski
hubert depesz lubaczewski - 12-01-2007 00:29
On 2007-01-11, Krzysztof Raczkowski <raczkowk@poczta.wp.pl> wrote:
> Mi też 3 wydaje się najlepszym pomysłem i chyba najbardziej niezawodnym.
> Trzeba by do tej tabeli tymczasowej dodać jeszcze jakiś timestamp,
> żeby w razie wpadki wiedzieć co sprzątać.
możesz sprzątać te wpisy których ident nie istnieje. ten ident to nic
innego niż process-id backendu postgresa w systemie. widoczny pod
unixami przy pomocy zwykłego ps'a.
> Jeszcze jedno, jeżeli w mojej przykładowej funkcji zrobię delete from
> tmp_tabela where ident = my_id to nie będzie problemu z rowsetem
> zwróconym z funkcji ??
nie.
> Czy coś takiego:
> for row in
> select now(), id from tmp_tabela
> return next row;
> end loop;
> Tworzy kopię danych ??
tak.
> No i jeżeli tak ... to może przy rzadkim uruchamianiu funkcji bardzie
> opłaca się zwracać kursor z funkcji zamiast rowset a sprzątać w cronie...
jak masz możliwość użycia kursora - to tak. bardziej się opłaca.
ale kursor nie zwraca kopii. tylko faktyczne dane.
depesz
--
http://www.depesz.com/ - blog dla ciebie
zanotowane.pldoc.pisz.plpdf.pisz.pleffulla.pev.pl
|
[sql][pgsql] zapytanie sql
Problem z funkcja mysql_real_escape_string() na bazie mysql
Wydajność baz danych w zależności od poziomu izolacji ANSI/ISO
Która z baz: [PGSQL] czy [MySQL] będzie lepsza w takim zastosowaniu (masowe UPDATE)
[MySQL5] problem z zastosowanie funkcji, procedury w celu unikniecia powtórzeń kodu w zapytaniach
Czy zna (obsługuje) ktoś program Iso Draw ?
[PGSQL] Pole "timestamp" a wyciagniecie tylko danych z danego roku.
[pgsql] Wybieranie z dwoch tabel - z drugiej tylko jeden rekord
[Postgres] jak zastapic brakujaca funkcje regexp_replace w PG wersji 8.0
nie mozna otworzyc PDF w PS - "Plik ma pewne ograniczone funkcje..."
zanotowane.pldoc.pisz.plpdf.pisz.platanvarne633.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 |
|
