Backend crash with user defined aggregate

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=#

Responses

Browse pgsql-general by date

  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?