Thread: Why would this crash my server
Hi all
I've been playing round for a week or so now trying to master the
elusive secrets of passing sets of records around in postgres. Having
made a little progress, I thought I'd try using cursors - I didn't get
very far, but I did bring my machine to its knees and kill the postgres
server (on the same machine). Here's what I did, I'm wondering what
needs changing:
-- Here is my table
glenn=# select * from zz;
a | b
---+---
1 | 0
2 | 0
3 | 0
4 | 2
5 | 2
6 | 1
7 | 4
8 | 4
9 | 7
(9 rows)
-- Here is my experimental function
create or replace function zz_dump() returns setof zz as '
declare
thiscur refcursor;
zz_tmp zz%rowtype ;
begin
open thiscur for select a, b from zz;
fetch thiscur into zz_tmp ;
return zz_tmp;
end;' language 'plpgsql';
--Heres the result, which comes after 5 min of hdd light thrashing,
capslock light doesn't respond and gui completely freezes. Interesting
thing is that after this executes, the gnome resources panel shows that
the amount of physical memory in use has halved. i.e. first time
95%->47%, 2nd time 47%->21%
thanks for _any_ help
Glenn
glenn <vmstech@tpg.com.au> writes:
> -- Here is my experimental function
> create or replace function zz_dump() returns setof zz as '
> declare
> thiscur refcursor;
> zz_tmp zz%rowtype ;
> begin
> open thiscur for select a, b from zz;
> fetch thiscur into zz_tmp ;
> return zz_tmp;
> end;' language 'plpgsql';
Try 7.3 --- plpgsql functions don't support returning sets in
earlier releases. (You can't do it quite that way in 7.3,
either; you need RETURN NEXT. See the documentation.)
> --Heres the result, which comes after 5 min of hdd light thrashing,
> capslock light doesn't respond and gui completely freezes.
On my machine, 7.2 fails with
ERROR: Memory exhausted in AllocSetContextCreate(8192)
after about 20 seconds. There's no obvious loss of responsiveness of
the rest of the machine meanwhile. I suspect your machine is poorly
configured --- better compare the max-memory-per-process kernel setting
with the actual amount of physical RAM and swap space. You've evidently
got it set up in a way that allows a runaway process to drive it into
swap hell.
regards, tom lane
Thanks again Tom - seems upgrading to 7.3 is the answer to most of my probs - but I'm not sure if I want to open that can of worms - quick clarification - do you think the problem occured at the moment of "fetch into " or when it tried to return the results? glenn > Try 7.3 --- plpgsql functions don't support returning sets in > earlier releases. (You can't do it quite that way in 7.3, > either; you need RETURN NEXT. See the documentation.) > > > --Heres the result, which comes after 5 min of hdd light thrashing, > > capslock light doesn't respond and gui completely freezes. > > On my machine, 7.2 fails with > ERROR: Memory exhausted in AllocSetContextCreate(8192) > after about 20 seconds. There's no obvious loss of responsiveness of > the rest of the machine meanwhile. I suspect your machine is poorly > configured --- better compare the max-memory-per-process kernel setting > with the actual amount of physical RAM and swap space. You've evidently > got it set up in a way that allows a runaway process to drive it into > swap hell. > > regards, tom lane >
glenn <vmstech@tpg.com.au> writes:
> Thanks again Tom - seems upgrading to 7.3 is the answer to most of my
> probs - but I'm not sure if I want to open that can of worms - quick
> clarification - do you think the problem occured at the moment of "fetch
> into " or when it tried to return the results?
I guess I should have quoted my full test result:
regression=# select zz_dump();
NOTICE: Error occurred while executing PL/pgSQL function zz_dump
NOTICE: line 6 at fetch
ERROR: Memory exhausted in AllocSetContextCreate(8192)
The NOTICE makes it look like it was the FETCH that broke. That kinda
surprises me though, as it implies that the problem is not really
related to the attempt to return a set. Could it be that fetching into
a rowtype variable is broken in 7.2? I wouldn't have thought so...
regards, tom lane
PostgreSQL, Please help me. I need to understand how you see the bounderies of the GPL licence. I want to use PostgreSQL as the server of a commercial (non GPL) application. All PostgreSQL components will be clearly marked GPL with URL to sourse code, and readable GPL, to the satisfaction of PostgreSQL. Can you please tell me whether that is acceptable under your understanding of the GPL? Specifically, with respect to MySQL take on the GPL, where the use of API's and lib's are seen as an extension to the main code, and therefore all applications must be GPL, or have a bespoke licence, or be illigal. Or other people who think that the use of API's and lib's form a logical abstracted boundery between the server and client, therefore not extending the product, therefore allowing commercial applications which are not GPL. Can you please let me know whether we can use PostgrSQL or not? Thanks, Ben Clewett.
Andrew McMillan wrote: >>Can you please let me know whether we can use PostgrSQL or not? > > > Undoubtedly :-) No worries, Ben Clewett. > > Regards, > Andrew.
On Mon, 2003-02-24 at 22:48, Ben Clewett wrote:
> PostgreSQL,
>
> Please help me. I need to understand how you see the bounderies of the
> GPL licence.
>
> I want to use PostgreSQL as the server of a commercial (non GPL)
> application. All PostgreSQL components will be clearly marked GPL with
> URL to sourse code, and readable GPL, to the satisfaction of PostgreSQL.
PostgreSQL is licensed under the BSD license, so it is perfectly OK to
do with it pretty much as you please.
> Can you please tell me whether that is acceptable under your
> understanding of the GPL?
>
> Specifically, with respect to MySQL take on the GPL, where the use of
> API's and lib's are seen as an extension to the main code, and therefore
> all applications must be GPL, or have a bespoke licence, or be illigal.
>
> Or other people who think that the use of API's and lib's form a logical
> abstracted boundery between the server and client, therefore not
> extending the product, therefore allowing commercial applications which
> are not GPL.
The BSD license certainly does not impose restrictions on you in this
regard.
> Can you please let me know whether we can use PostgrSQL or not?
Undoubtedly :-)
Regards,
Andrew.
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Survey for nothing with http://survey.net.nz/
---------------------------------------------------------------------
On 24 Feb 2003, Andrew McMillan wrote: > On Mon, 2003-02-24 at 22:48, Ben Clewett wrote: > > Please help me. I need to understand how you see the bounderies of the > > GPL licence. > > > > I want to use PostgreSQL as the server of a commercial (non GPL) > > application. All PostgreSQL components will be clearly marked GPL with > > URL to sourse code, and readable GPL, to the satisfaction of PostgreSQL. > > PostgreSQL is licensed under the BSD license, so it is perfectly OK to > do with it pretty much as you please. One thing I think it doesn't grant is the permission to distribute it under GPL! It also means that you must not mark them as GPL... because those licenses are conflicting. ;-) (Or I'm not sure... but who wants to change it to GPL anyway) -- Antti Haapala