From: | mlw <markw(at)mohawksoft(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | sig 11 in RC3 |
Date: | 2002-01-04 13:52:25 |
Message-ID: | 3C35B399.D494D3BA@mohawksoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
I have a couple functions which form the basis of an aggregate. The purpose of
the aggregate function is to be able to perform a GROUP BY on a one to many
table and produce a summary able where all the "many" integers will be packed
in a single array. If this were a text search query, rather than searching
hundreds of entries in the table, one fetch and possibly a detoast is used. It
is MUCH faster for my purpose.
It is used like this:
create table array_lookup as select id1, int_array_aggregate(id2) from lookup
group by (id1) ;
I have written a good number of functions in PGSQL, I'm not a newbe. Could
someone take a look at it? I don't think I am doing anything that would kill
the back end, so it may be a bug in RC3, I am just pulling my hair out. (FYI,
the one to many table may have thousands of rows for an entry.) One more thing:
I'm not getting any elog messages, so it should not be a memory issue.
>>>>>>>>>>>>>>>>
-- Internal function for the aggregate
-- Is called for each item in an aggregation
create function int_agg_state (int4, int4)
returns int4
as 'MODULE_FILENAME','int_agg_state'
language 'c';
-- Internal function for the aggregate
-- Is called at the end of the aggregation, and returns an array.
create function int_agg_final_array (int4)
returns int4[]
as 'MODULE_FILENAME','int_agg_final_array'
language 'c';
-- The aggration funcion.
-- uses the above functions to create an array of integers from an aggregation.
create aggregate int_array_aggregate
(
BASETYPE = int4,
SFUNC = int_agg_state,
STYPE = int4,
FINALFUNC = int_agg_final_array,
INITCOND = 0
);
>>>>>>>>>>>>>>>>
/* This is actually a postgres version of a one dimentional array */
typedef struct agg
{
ArrayType a;
int items; /* Number of items in array */
int lower; /* Lower bounds of array, used as max during aggregation
*/
int4 array[1];
}PGARRAY;
#define TOASTED 1
#define START_NUM 8
#define PGARRAY_SIZE(n) (sizeof(PGARRAY) + ((n-1)*sizeof(int4)))
PGARRAY * GetPGArray(int4 state, int fAdd);
Datum int_agg_state(PG_FUNCTION_ARGS);
Datum int_agg_final_array(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(int_agg_state);
PG_FUNCTION_INFO_V1(int_agg_final_array);
/* Manage the aggregation state of the array */
PGARRAY * GetPGArray(int4 state, int fAdd)
{
PGARRAY *p = (PGARRAY *) state;
if(!state)
{
/* New array */
int cb = PGARRAY_SIZE(START_NUM);
p = (PGARRAY *) palloc(cb);
if(!p)
{
elog(ERROR,"Integer aggregator, cant allocate
memory\n");
return 0;
}
p->a.size = cb;
p->a.ndim= 0;
p->a.flags = 0;
p->items = 0;
p->lower= START_NUM;
return p;
}
else if(fAdd)
{
/* Ensure array has space */
if(p->items >= p->lower)
{
PGARRAY *pn;
int n = p->lower + p->lower;
int cbNew = PGARRAY_SIZE(n);
pn = (PGARRAY *) palloc(cbNew);
if(!pn)
{
elog(ERROR,"Integer aggregator, cant allocate
memory\n");
}
else
{
memcpy(pn, p, p->a.size);
pn->a.size = cbNew;
pn->lower = n;
pfree(p);
return pn;
}
}
}
return p;
}
/* Called for each iteration during an aggregate function */
Datum int_agg_state(PG_FUNCTION_ARGS)
{
int4 state = PG_GETARG_INT32(0);
int4 value = PG_GETARG_INT32(1);
PGARRAY *p = GetPGArray(state, 1);
if(!p)
{
elog(ERROR,"No aggregate storage\n");
}
else if(p->items >= p->lower)
{
elog(ERROR,"aggregate storage too small\n");
}
else
{
p->array[p->items++]= value;
}
PG_RETURN_INT32(p);
}
/* This is the final function used for the integer aggregator. It returns all
the integers
* collected as a one dimentional integer array */
Datum int_agg_final_array(PG_FUNCTION_ARGS)
{
PGARRAY *p = GetPGArray(PG_GETARG_INT32(0),0);
if(p)
{
/* Fix up the fields in the structure, so Postgres understands
*/
p->a.size = PGARRAY_SIZE(p->items);
p->a.ndim=1;
p->a.flags = 0;
p->lower = 0;
PG_RETURN_POINTER(p);
}
PG_RETURN_NULL();
}
From | Date | Subject | |
---|---|---|---|
Next Message | Dwayne Miller | 2002-01-04 14:17:00 | Syntax changes in 7.2 |
Previous Message | Palle Girgensohn | 2002-01-04 13:20:44 | Re: Is there any performance penalty using --with-ssl? |