Thread: C vs plpgsql and child processes
Hi All, I've written a few C functions in the version 1 format as well as some SPI based backend bits and I'm curious as to how PG treats these items when used within plpgsql. The set of functions control external processes. One function is called with various items governed by the db, does a fork and then execl to from the child to start a new process. Whilst it's not posix i ignore the SIGCHLD signal and let the parent process return the pid to postgres. This seems to all be good and postgres itself obviously doesn't wait for the child to finish (which is intentional). When the child finishes it terminates correctly and doesn't zombie out. Problem is that when I call these particular functions from within plpgsql rather than through a single sql command the child never actually starts (or starts and then exits immediately). I presume it's more to do with the fact that what I'm doing is a bit of a hack in some senses and isn't really the best way to go about it. Is there any specific reason why this idea wouldn't work within plpgsql? Or should I look at moving the whole function to C? I can certainly appreciate why it wouldn't work - just some info as to the best way to achieve this concept would be great. Rgds, Jason
> Problem is that when I call these particular functions from within > plpgsql rather than through a single sql command the child never > actually starts (or starts and then exits immediately). Are you sure? I can't think of much that'd prevent a C function from executing other than how you've declared the function (ie, is PgSQL caching the results of the function?). Make sure you've declared it as VOLATILE (or don't declare it anything and it'll default to VOLATILE). http://developer.postgresql.org/docs/postgres/sql-createfunction.html -sc -- Sean Chittenden
Hi Sean, Yeah - It is declared VOLATILE. I think there must be something specific with the way PL/PGSQL handles child processes of a called function. The child process actually spawns mpg123 or ogg123 so it has to live beyond the life of the parent. Not sure. What I might do is rewrite the entire procedure from woe to go in using SPI and see how that goes. Failing that I guess I could always peek at the source! : ) Thanks, Jason On Mon, 18 Aug 2003 04:48 am, Sean Chittenden wrote: > > Problem is that when I call these particular functions from within > > plpgsql rather than through a single sql command the child never > > actually starts (or starts and then exits immediately). > > Are you sure? I can't think of much that'd prevent a C function from > executing other than how you've declared the function (ie, is PgSQL > caching the results of the function?). Make sure you've declared it > as VOLATILE (or don't declare it anything and it'll default to > VOLATILE). > > http://developer.postgresql.org/docs/postgres/sql-createfunction.html > > -sc
Jason Godden wrote: > Hi Sean, > > Yeah - It is declared VOLATILE. I think there must be something specific with > the way PL/PGSQL handles child processes of a called function. The child > process actually spawns mpg123 or ogg123 so it has to live beyond the life of > the parent. Not sure. What I might do is rewrite the entire procedure from > woe to go in using SPI and see how that goes. Failing that I guess I could > always peek at the source! : ) PL/pgSQL does not pay any attention or could affect child processes of a backend to my knowledge. Are you sure that the PL/pgSQL function really calls your C function forking off the child? The best way to check would be to have some NOTICE coming out of your C function before it actually does create the child. Jan > > Thanks, > > Jason > > On Mon, 18 Aug 2003 04:48 am, Sean Chittenden wrote: >> > Problem is that when I call these particular functions from within >> > plpgsql rather than through a single sql command the child never >> > actually starts (or starts and then exits immediately). >> >> Are you sure? I can't think of much that'd prevent a C function from >> executing other than how you've declared the function (ie, is PgSQL >> caching the results of the function?). Make sure you've declared it >> as VOLATILE (or don't declare it anything and it'll default to >> VOLATILE). >> >> http://developer.postgresql.org/docs/postgres/sql-createfunction.html >> >> -sc > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Hi Jan/Sean To the list I bow and apologise for wasting your time! I did not appropriately test the function (as is my want!) and passed command line arguments in the executable image path in the execl function. Now I edit my table containing the configuration vars for the trackformat to decoder info and everything purrs.. sigh. Writing help requests to the list when you've only exhausted half the possibilities are a bit silly. Thanks for your time, Jason On Mon, 18 Aug 2003 11:41 pm, Jan Wieck wrote: > Jason Godden wrote: > > Hi Sean, > > > > Yeah - It is declared VOLATILE. I think there must be something specific > > with the way PL/PGSQL handles child processes of a called function. The > > child process actually spawns mpg123 or ogg123 so it has to live beyond > > the life of the parent. Not sure. What I might do is rewrite the entire > > procedure from woe to go in using SPI and see how that goes. Failing > > that I guess I could always peek at the source! : ) > > PL/pgSQL does not pay any attention or could affect child processes of a > backend to my knowledge. Are you sure that the PL/pgSQL function really > calls your C function forking off the child? The best way to check would > be to have some NOTICE coming out of your C function before it actually > does create the child. > > > Jan > > > Thanks, > > > > Jason > > > > On Mon, 18 Aug 2003 04:48 am, Sean Chittenden wrote: > >> > Problem is that when I call these particular functions from within > >> > plpgsql rather than through a single sql command the child never > >> > actually starts (or starts and then exits immediately). > >> > >> Are you sure? I can't think of much that'd prevent a C function from > >> executing other than how you've declared the function (ie, is PgSQL > >> caching the results of the function?). Make sure you've declared it > >> as VOLATILE (or don't declare it anything and it'll default to > >> VOLATILE). > >> > >> http://developer.postgresql.org/docs/postgres/sql-createfunction.html > >> > >> -sc > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 9: the planner will ignore your desire to choose an index scan if > > your joining column's datatypes do not match