C vs plpgsql and child processes

[Jason Godden]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Jan Wieck]

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...efunction.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. #
#============== =============== =============== ====== Ja******@Yahoo. com #
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings