From: | Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Backend crash with user defined aggregate |
Date: | 2005-09-23 20:38:52 |
Message-ID: | Pine.LNX.4.58.0509231323390.1550@greenie.cheapcomplexdevices.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I tried making an aggregate function that concatenates
any non-null strings it encounters, as shown below.
The function works fine on small tables; but when I run
it on one of my larger tables it seems to me that it
crashes the backend - though I don't see anything obvious
in the log.
According to 'top', the postmaster grows pretty quickly
so perhaps it ran out of memory? The query I ran is
doing a group-by and no individual value in the group
by clause should have that big a result; so hypothetically
it seems it wouldn't need to put everything in memory
if it sorted them first.
Any workarounds or other clever ways of doing this
kind of aggregation? I guess a stored procedure that
reads one group at a time?
Thanks in advance,
Ron
------------------------------------------------------------
--- The definition of my aggregte
------------------------------------------------------------
CREATE OR REPLACE FUNCTION nonull_append_strings (
text, text )
RETURNS text AS '
SELECT CASE WHEN $1 IS NULL THEN $2
WHEN $2 IS NULL THEN $1
ELSE $1 || '' '' || $2
END;
' LANGUAGE sql IMMUTABLE;
CREATE OPERATOR ||+ (
LEFTARG = TEXT,
RIGHTARG = TEXT,
PROCEDURE = nonull_append_strings
);
create aggregate strcat_agg (
sfunc = nonull_append_strings,
basetype = text,
stype = text
);
------------------------------------------------------------
--- Example using the function
------------------------------------------------------------
fli=#
fli=# create table new_keywords as
select ext_doc_id,strcat_agg(nam)||+strcat_agg(val)
from facet_raw group by ext_doc_id;
FATAL: terminating connection due to administrator command
CONTEXT: SQL function "nonull_append_strings" statement 1
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
fli=#
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Treat | 2005-09-23 20:40:32 | Re: How many insert + update should one transaction |
Previous Message | Bruce Momjian | 2005-09-23 20:27:31 | Re: Lines of code in PG 8.0 & 8.1? |