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

Sat, 24 Jul 1999 13:38:58 +0930 (CST)
Wayne Piekarski <wayne@senet.com.au>
SIGSEGV on CREATE FUNCTION with plpgsql
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

Sat, 24 Jul 1999 11:54:24 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] SIGSEGV on CREATE FUNCTION with plpgsql 
>
> 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) #



Mon, 26 Jul 1999 11:03:59 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] SIGSEGV on CREATE FUNCTION with plpgsql
> 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


Sat, 7 Aug 1999 17:46:18 +0930 (CST)
Wayne Piekarski <wayne@senet.com.au>
Re: [HACKERS] SIGSEGV on CREATE FUNCTION with plpgsql