Thread: SIGSEGV on CREATE FUNCTION with plpgsql
Hi, I sent a bug report in a week ago about problems with pg_proc and getting SIGSEGV when trying to create largish functions. [please see my previous posting for details] I'm not sure what to do here, I tried dropping the pg_proc_prosrc_index and it died, and so I have to leave it hanging around. Also, my functions are all quite small, (less than 2k) except for two of them, which are just over 2k, but nowhere near 4k in size which would cause the btree index to die as someone mentioned. So can someone give me some advice? Do you want me to provide a stack dump or something? Right now I'm reluctant to play with my plpgsql functions because I'm scared its going to die and I wont be able to reload them back in. In order to get my pg_dump to reload I had to do a few hacks, like creating some functions before reloading the backup to cause them to get loaded in a different order. pg_proc can also be corrupted with BTP_CHAIN and things as a result of this problem as well. ciao, Wayne ------------------------------------------------------------------------------ Wayne Piekarski Tel: (08) 8221 5221 Research & Development Manager Fax: (08) 8221 5220 SE Network Access Pty Ltd Mob: 0407 395 889 222 Grote Street Email: wayne@senet.com.au Adelaide SA 5000 WWW: http://www.senet.com.au
Wayne Piekarski <wayne@senet.com.au> writes:
> So can someone give me some advice? Do you want me to provide a stack dump
> or something?
A stack trace might help --- I'm not sure why you are seeing this
problem if there are no functions approaching 4k of text.
> Right now I'm reluctant to play with my plpgsql functions because I'm
> scared its going to die and I wont be able to reload them back in.
You can play with them in a playpen installation... I wouldn't do that
sort of testing on a production installation either. A playpen is
always a good thing to have. Note you can put multiple playpens on
one machine --- all you need is a separate data directory and socket
number for each one.
regards, tom lane
>
> Wayne Piekarski <wayne@senet.com.au> writes:
> > So can someone give me some advice? Do you want me to provide a stack dump
> > or something?
>
> A stack trace might help --- I'm not sure why you are seeing this
> problem if there are no functions approaching 4k of text.
Would be interesting if the problem is index related. I still
wonder (while looking at the code) what that
ProcedureSrcIndex is really good for.
I've tracked it down that it is only once used in pg_proc.c
to check if an sql language function that implements a SET
already exists (weired method to do IMHO). The code was
already there in version 1.1 (initial load) of the code, so
it might be an old Postgres 4.2 thing that's obsolete.
Additionally, very doubtful, is the fact that we considered
functions returning SET's as broken, so again I wonder if
there's any code that automatically creates such functions
(if not created automatically like the _RET rules of views
are, identifying by this wouldn't allways work). The
targetlists attached to SET functions don't work, so I assume
removing the index wouldn't break anything.
I'll dig out the 4.2 sources and search for a reason for that
index there. If I find anything, I can check if that's still
in our code.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
> Wayne Piekarski <wayne@senet.com.au> writes: > > So can someone give me some advice? Do you want me to provide a stack dump > > or something? > > A stack trace might help --- I'm not sure why you are seeing this > problem if there are no functions approaching 4k of text. Ok, I've got a test postgres set up which I used for my profiling so I'll have a play and get a stack dump and see if I can work out whats causing this. Been busy lately so haven't had a chance ... more later on this. > > Right now I'm reluctant to play with my plpgsql functions because I'm > > scared its going to die and I wont be able to reload them back in. > > You can play with them in a playpen installation... I wouldn't do that > sort of testing on a production installation either. A playpen is > always a good thing to have. Note you can put multiple playpens on > one machine --- all you need is a separate data directory and socket > number for each one. I've got multiple testing databases, but due to the random nature of the problem, the functions will reload normally, but when the pg_dump output reloads them, it does it in a different order and dies. So it is dependent on order and a bunch of other things, and so even with testing, I still can't be sure it won't break the real production database. I'll have a look through the code and see if I can spot something obvious. My functions are all <= 1k and so I'm miles away from 2k or 4k problems with btree indices. thanks, Wayne ------------------------------------------------------------------------------ Wayne Piekarski Tel: (08) 8221 5221 Research & Development Manager Fax: (08) 8221 5220 SE Network Access Pty Ltd Mob: 0407 395 889 222 Grote Street Email: wayne@senet.com.au Adelaide SA 5000 WWW: http://www.senet.com.au