livetime of a variable defined in a c-procedure (fwd)

Started by Victoria W.almost 27 years ago2 messageshackers
Jump to latest
#1Victoria W.
wicki@terror.de

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 ;*)

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Victoria W. (#1)
Re: [HACKERS] 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