Re: [SQL] Low level socket locking...

=>From: M Simms <grim(at)argh(dot)demon(dot)co(dot)uk>
=>...
=>I am writing a C program to work as an interface between postgres and a
=>web page. This page will get a LOT of activity.
=>...
=>I was planning on simply fork()ing the handler process. The database would be
=>open on each child, and I could happily process them all easilly. However
=>it occurred to me that I do not know how the socket code between the
=>postgres libraries and the postgres backend is handled? Is there a socket
=>locking process in place, to prevent two children passing data down the same
=>socket at the same time, thus causing garbled data to be received at the other
=>end? If I fork() and send data from one of say, three child processes, am
=>I guarenteed to get the data back at the same child?
=>(this all assumes that I open the database connection and then fork() after)

I've written a similar program for Oracle and Sybase, and (while I
can't speak directly to the question of whether you can do what you
want in PostgreSQL), I'd strongly suggest opening the DB connection
*after* you fork(). You won't have to worry about multiple children
confusing the back end, and it'll work for other databases too, if you
need that capability later. The sequence you want to do is:

* Prepare to become a daemon (lose controlling TTY, chdir("/"), close
all open descriptors, etc)
* Open the socket you're going to receive requests on.
* Fork() as many children as you need
> In each child, connect to the database, then do
accept/read/write/close on the socket to respond to the client's
requests
> In the parent, watch for children dying, and spawn new ones to
replace them.

You can make this even nicer by having the parent & children collude
to do load balancing -- if all the children have clients, the parent
should start some more. If many children are idle for a while, they
should die off. You can even have the parent serve requests on a
"control channel" to change the number of children or to retrieve
statistics about the daemon's activity.

d.