hi all,

I want to build subsubtotals of a column (rechnr, for example). this
field should be incremented, whenever the field "baust" changes.
I'm able to increment a field by a function:

create function add_intsum(varchar,int8) returns int8
  as' update sum_table set intsum=((intsum )+ ($2))where key1 = $1;
        select max(intsum) from sum_table where key1 = $1;'
language 'sql';

sum_table ist delared as: 
create table sum_table(key1 varchar,moneysum money,realsum real,intsum
int8);
insert into sum_table values('baust','0',0,0);
insert into sum_table values('contanz',  '0',0,0);
insert into sum_table values('rechnr',   '0',0,0);

now "select add_intsum('rechnr',1);" will increment "rechnr" in
"sum_table" and return this field. this works - but there are 2 problems:

a) its not very fast
b) I can't do a conditional-increment, depending on the value 
	of another field

so I've done the following:

int4    rechnr=0;
int4    baust=0;

int4    add_rechnr(int4 val)
{rechnr=rechnr+val;return rechnr;}

int4    add_baust(int4 val)
{baustelle=baust+val;return baust;}

int4    init_rechnr(int4 val)
{rechnr=val;return rechnr;}

int4    init_baust(int4 val)
{baust=val;return baust;}


this works and I'm able to include conditions into the c-code. But what I
not fully understand is the followinf behaviour:

CREATE FUNCTION add_rechnr(int4) RETURNS int4
              AS '/usr/local/pgsql/lib/modules/funcs.so' LANGUAGE 'c';

whenever I add a value to rechnr, the correct result is returned. but if I
copy a new file "funcs.so" into the direcotry while the backend is
running and a connection is established, I'll get this error:

pqReadData() -- backend closed the channel unexpectedly.
.....

So I suppose, the backend will load the lib whenever a lib-function is
invoced. But if so, why does he remember the last value of the variables
defined in this lib?

Any hints ?

best regards

wicki ;*)




Sat, 5 Jun 1999 07:01:11 +0000 (GMT)
"Victoria W." <wicki@terror.de>
livetime of a variable defined in a c-procedure (fwd)
"Victoria W." <wicki@terror.de> writes:
> CREATE FUNCTION add_rechnr(int4) RETURNS int4
>               AS '/usr/local/pgsql/lib/modules/funcs.so' LANGUAGE 'c';

> whenever I add a value to rechnr, the correct result is returned. but if I
> copy a new file "funcs.so" into the direcotry while the backend is
> running and a connection is established, I'll get this error:

> pqReadData() -- backend closed the channel unexpectedly.

Overwriting a shared library that's in active use is a no-no on many
flavors of Unix.  (The one I use won't even let you do it --- you get
a "text file busy" error if you try to delete or modify an open SL.)

I don't think there is an "unload shared library" function in Postgres
(it'd be hard or impossible to implement on some Unixes, anyway).  So
the only way to stop referencing a shared library once it's been opened
is to terminate the backend process.

In short, if you've designed a solution that requires you to constantly
modify the shared library, you'd better look for another solution ---
or accept a lot of backend restarts.

			regards, tom lane

Sat, 05 Jun 1999 14:33:03 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] livetime of a variable defined in a c-procedure (fwd) 