Re: Pro*C Question: Forking Processes

Frank-Michael Zimmer wrote:
>
> Well, thanks a lot for the comments and suggestions - I think I should
> specify my problem a bit more:
>
> The program is written in Pro*C and runs on Solaris, with Oracle version
> 7.1.4.
>
> The idea is of something like a demon that wait at the lower end of a
> pipe to accept orders. The orders are inserted into the upper pipe end
> by routines from a package that resides in the database. If you have an
> order, just call the package procedure, it will turn it into a message,
> throw that down the pipeline, and the 'demon' will take care of its
> execution.
>
> To execute the order, the 'demon' has to spawn a child, for the orders
> might take long to execute (fetch (large!) datasets from the database
> and write them to a directory - the data may well be migrated into a
> unitree system), and doing the work himself will block him for too long.
>
> Also, the 'demon' is expected to deliver some kind of receipt upon
> completion of his task. The method implemented calls another package
> function. The important point is just that whoever writes the receipt
> has to be connected to Oracle.
>
> The _real_ problems ;) now are:
>
> 1) Who writes the receipt, and when?
> Forking while connected means that Oracle sees the last of the two
> processes that used the database connection as the legitimate owner.
> Usually, the son writes his receipt and terminates, thereby killing the
> father's connection, too.
> On the other hand, if the son does not use his connection but hands his
> data to the father to write the receipt and simply terminates
> afterwards, the connection is ok, but the father has to keep track of an
> undetermined number of children. I did this by having the father use a
> list of his sons' data, with the effect that a very quickly terminating
> son will cause the father to remove his son from the list before he has
> entered him into it! So this does not work, either.
>
> 2) To disconnect, or not to disconnect?
> Disconnecting, forking and then connecting again so each of the two
> processes had his own session is a good idea for a solution, but there
> have been problems in the praxis: After the sons terminate, the father
> receives a SIGCHLD signal to inform him that there may be a data
> heirloom for him, to be collected by the system call wait(). The first
> dead child is removed all right, but any further children that die
> suffer the grisly fate of a unix zombie... ;) This happens
> independently of the actual actions the children take with Oracle. I
> suspect that there is a problem with the father's waiting at the pipe
> for a message to pop out (a blocking procedure call) being interrupted
> by the SIGCHLD signal and entering the signal handler.
> As this mechanism works well when Oracle is not involved, I doubt that
> it's simply a unix problem.
>
> Ah, well. If you have read this far, I thank you for your patience and
> would appreciate your thoughts on the subject, just like any ideas or
> suggestions.
>
> Have a nice weekend,
>
> Frank-Michael :D

Would somethig like this work:

                 +-----------------------------------------------------------+
                 |              USER CONNECTIONS TO THE DB                   |
                 +--------------+-----+-----+-----+-----+--------------------+
                                ^     ^     ^     ^     ^
                                |     |     |     |     |
                                |     |     |     |     |
                                V     V     V     V     V
                       +--------+-----+-----+-----+-----+-------------+
                       |              ORACLE KERNEL                   +-<-+
                       +---------------------+------------------------+   |
                                             |                            |
                                          DB |                            |
                                        PIPE |                            |
                                             V                            |
                          +------------------+---------------------+      |
           +------------->+    ORDER SERVER PARENT/LISTENER        |      |
           |              +--+---------------+-------------------+-+      |
      +----+----+            |               |                   |        |
      | UNIX    |            |          UNIX |                   |        |
      | MESSAGE |            |         PIPES |                   |        |
      | QUEUE   |            |   1 PER CHILD |                   |        |
      +----+----+            V               V                   V        |
           ^              +--+---------------+-------------------+-+      |
           +--------------+     1 to N ORDER SERVER CHILDREN       +-<----+
                          +----------------------------------------+

  .The ORDER SERVER PARENT/LISTENER process is started.  It creates a UNIX MESSAGE QUEUE
       forks 1 to N ORDER SERVER CHILDREN each with its own UNIX PIPE, connects to the
       ORACLE KERNEL, creates a DB PIPE, then listens and waits for an order on same.
  .Each ORDER SERVER CHILD, connects to the ORACLE KERNEL, connects to the UNIX MESSAGE QUEUE
       and writes a message to it identifying who they are and that they are ready to service 
       an order.
  .A USER CONNECTION TO THE DB creates an order and the PL/SQL block writes said to the
       DB PIPE.
  .The ORDER SERVER PARENT/LISTENER reads the message from the DB PIPE and reads the first message
       from the UNIX MESSAGE QUEUE.  He now has an order and knows who is available to service 
       this order.  He writes the order to the UNIX PIPE of the available ORDER SERVER CHILD
       and goes back to listening on the DB PIPE.  If by chance when he reads an order from the
       DB PIPE and finds no available message/child on the UNIX MESSAGE QUEUE, he can be programmed
       to sleep/loop/read on the UNIX MESSAGE QUEUE or.........
  .Each ORDER SERVER CHILD does a blocking read on it's UNIX PIPE.  When an order is read, it
       completes it as necessary, then writes a message identifying who they are and that
       they are ready to service another order on the UNIX MESSAGE QUEUE.

There are a lot of things I left out here but the general concept to solid. I hope you find that it's what you need.

                       \\|//
                       (0-0)
           +-----oOO----(_)-----------+
           | Brian P. Mac Lean        |
           | Database Analyst         |
           | brian.maclean_at_teldta.com |
           | http://www.teldta.com    |
           +-------------------oOO----+
                      |__|__|
                       || ||
                      ooO Ooo