[SQL] Memory exhausted
Hello! I used a self written funtion in plpgsql with a database of 2 Gigabyte size. My server has 384 Megabytes of RAM. So I got this error by calling the following function: psql:restructure.sql:139: FATAL 1: Memory exhausted in AllocSetAlloc() pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. psql:restructure.sql:139: connection to server was lost In the memory usage program Its shown that the function needs all the memory. The function fetches all XXX rows of a table and writes a value to another table CREATE FUNCTION series_image () RETURNS integer AS ' DECLARE psr_rec record; i integer := 0; BEGIN FOR psr_rec IN SELECT * FROM relseries_image000 LOOP UPDATE image SET seriesoid = psr_rec.parentoid WHERE chilioid = psr_rec.childoid; i := i + 1; END LOOP; IF NOT FOUND THEN RETURN -1; ELSE RETURN i; END IF; END; ' LANGUAGE 'plpgsql'; What could I optimize in this function above? I tried the Select statement in the psql command and it has taken 20 minutes. I estimate that there are more than 40 rows in the table. Then it breakes , the announcment appears: malloc: Resource temporarily unavailable and psql is crashed. Should I change the postmaster parameters? actually they are : ./postmaster -i -S -D/usr/local/pgsql/data -B 256 -o -e -o -F What can I do? Thanks in advance for any advice David begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum;Division Medizinische und Biologische Informatik version:2.1 email;internet:[EMAIL PROTECTED] adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-15296 fn:David M. Richter end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: select distinct too slow
Did you vacuum analyse your table after creating the index? A solution for your problem would be to turn sequential scans ofd before querying the table. This should work somehow like that: myjava=# SET enable_seqscan TO 'off'; SET VARIABLE myjava=# show enable_seqscan; NOTICE: enable_seqscan is off SHOW VARIABLE check out the docs for further information. Hans ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: seleting all dates between two dates
Jeff Barrett schrieb: > I am interested in a query where I can select all dates between two dates. I > figure I can build a table of all valid dates with a resonable range and > then select from that table, but I would like to use the power of sql to get > the work done without building a date table. Any ideas? > > For example: > I want all dates between 05-29-2001 and 06-02-2001 > The result set would be: > 05-30-2001 > 05-31-2001 > 06-01-2001 > > Thanks for the help. > -Jeff In my opinion there is no reasonable way to solve your problem by using SQL only Why don't you use a temporary table created by a simple PL/pgSQL function? Hans ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Memory exhausted
David Richter <[EMAIL PROTECTED]> writes: > I used a self written funtion in plpgsql with a database of 2 Gigabyte > size. My server has 384 Megabytes of RAM. > So I got this error by calling the following function: > psql:restructure.sql:139: FATAL 1: Memory exhausted in AllocSetAlloc() Try updating to Postgres 7.1. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Table design issue....
Hi all, I've got a situation where I need to be able to query for the same sort of data across multiple tables. Let me give some example tables then explain. create table t1 ( t_attr1 text[], t_attr2 text[] ); create table a1 ( a_attr1 text[], a_attr2 text[] ); create table c1 ( c_attr1 text[], c_attr2 text[], c_attr3 text[] ); In each of the above tables *_attr*[1] contains a flag that determines what type of attribute it is. t1.t_attr1[1] == a1.a_attr2[1] == c1_.c_attr3[1] == FLAG In otherwords, the attribute with the specific flag in quesiton is not known at runtime, unless I keep a table with the column names and table names setup. Also, new *1 tables could be created dynamically with new attr*'s, and the number of columns within the tables isn't going to be the same. What I need to be able to do is say something like: "For ALL *1 tables with *_attr*[1] == FLAG return rows with VALUE" Ideas? Comments? Suggestions? Am I being crazy? Pierre
[SQL] problem with Pl/Pgsql function
Hi I have a problem with this function:
Declare
rec_struttura record;
rec_camp record;
prov int;
estra_capo int;
id_lista int;
estra_non int;
rand int8;
count int;
count_estra int;
count_id int;
rand_doub float8;
row int8;
occ boolean;
note varchar;
pre char(4) /*NOT NULL := '02'*/;
tel char(13) /*NOT NULL := '342522'*/;
com int;
citta char(35);
nome varchar;
num int4;
data_oggi date;
capo int;
cap char(5);
capo_bool boolean;
Begin
For rec_struttura in Select * From struttura_campione Loop
/* estraggo i dati della provincia considerata */
prov := rec_struttura.cod_prov;
estra_capo := rec_struttura.num_capo;
estra_non := rec_struttura.num_non_capo;
/* estrazione dei nominativi nel capoluogo */
capo := 1;
count_estra := 0;
/* modificato andrea*/
<>
while (count_estra < estra_capo) Loop
count_estra := count_estra + 1;
/*modificato andrea*/
/*Select into rand PERFORM random ();*/
/* in ogni caso bisogna rimappre rand da o al ROW_COUNT della
select,
immagino che così vada piu'
veloce il tutto anche perche' rand arriva fino a
2^31-1 ;) */
rand_doub:= random() ;
count := 0;
/* Questa select quante volte viene ripetuta? tante volte
quanti sono i
soggetti da estrarre? */
For rec_camp in Select
note,prefisso,telefono1,cod_com,cod_prov,citta,nome_cogno,capoluo,estrazione,num_estra,cap
>From nominativi inner join comuni on citta = nome_com Where ((cod_prov =
prov) and (capoluo=capo)) Loop
/* bisogna trovare un modo piu' figo per beccare il
record
altrimenti fa troppi giri dentro sti cicli */
GET DIAGNOSTICS row = ROW_COUNT;
rand := round (rand_doub * row);
if (rand > row) then
/*questo l'ho messo perche' altrimenti alla
fine ne estrae meno di
quelli che servono*/
exit caso;
end if;
count := count + 1;
if count = rand then
/* modificato andrea, prima la
condizione "not occ" */
if not rec_camp.occupato then
note := rec_camp.note;
pre := rec_camp.prefisso;
tel := rec_camp.telefono1;
if pre IS NULL or tel IS NULL
then
exit caso;
end if;
com := rec_camp.cod_com;
citta := rec_camp.citta;
cap := rec_camp.cap
nome := rec_camp.nome_cogno;
num := rec_camp.num_estra;
capo := rec_camp.capoluo;
exit;
else count_estra := count_estra -1; exit caso;
end if;
end if;
End Loop; /* end del for*/
/* estraggo i dati dal record prescelto */
/*note := rec_camp.note;
pre := rec_camp.prefisso;
tel := rec_camp.telefono1;
com := rec_camp.cod_com;
citta := rec_camp.citta;
nome := rec_camp.nome_cogno;*/
/* faccio l'update di occupato */
/*num := rec_camp.num_estra;*/
num := num + 1;
data_oggi := now();
update nominativi set occupato = TRUE, num_estra = num,
estrazione =
data_oggi Where ((prefisso = pre) and (telefono1 = tel));
/* faccio l'insert su tab_estrazioni */
id_lista := nextval('tab_estrazioni_id_seq');
[SQL] finding a maximum or minimum sum
I have a table that looks like so: userid | amount --- 1 | $500 2 | $400 2 | $-100 2 | $10 3 | $10 3 | $10 I run a select sum(amount) from payments group by userid userid | sum -- 1| $500 2| $310 3| $20 I need to modify this query so it returns the minimum, maximum and average sums. Is there any way I can do this? -Michael _ http://fastmail.ca/ - Fast Free Web Email for Canadians ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] finding a maximum or minimum sum
"Michael Richards" <[EMAIL PROTECTED]> writes: > I run a select sum(amount) from payments group by userid > I need to modify this query so it returns the minimum, maximum and > average sums. Is there any way I can do this? You need two levels of grouping/aggregating to make that happen. In 7.1 you can do it directly: select min(amtsum), max(amtsum), avg(amtsum) from (select sum(amount) as amtsum from payments group by userid) ss; In prior versions you'd need to do the initial select into a temp table and then select min/max/avg from that. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] finding a maximum or minimum sum
Prfect! This is exactly what I needed. Didn't know postgres supported subselects like that. Thanks. -Michael > select min(amtsum), max(amtsum), avg(amtsum) > from (select sum(amount) as amtsum from payments group by userid) > ss; > > In prior versions you'd need to do the initial select into a temp > table and then select min/max/avg from that. _ http://fastmail.ca/ - Fast Free Web Email for Canadians ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
