We use UNION all over the place to expand outer joins that are in ACS
Classic (PG 7.0 doesn't have outer joins). It works fine except that
any ORDER BY clause on orders the first SELECT of the UNION (fixed in
PG 7.1, which will beta soon). I've not used EXCEPT/INTERSECT myself
because ACS Classic doesn't use these constructs AFAIK - it is
possible there are some in there, though, that ported directly without
effort or that others dealt with.
It's not a good idea to kill a postgres backend. It's far better to
stop the postmaster using the script in init.d - it does a kill -TERM
on the POSTMASTER (not individual backend) process. The postmaster
traps the SIGTERM and tries to stop all the individual backends in a
clean fashion before stopping itself. If the kill -TERM fails, then
the script will issue a kill -KILL.
The error in AOLserver simply says that it couldn't allocate roughly
1.5MB to itself (using malloc). I think you most likely did trigger a
cartesian product, causing PG to hog all your RAM and swap space when
it attempted to build the output from the query. In essence, you ran
out of memory. In PG, the value returned from a query must fit in
memory+swap. Not really a problem for realistic queries on modern
computers but it does lead to runaway memory consumption if you have a
cartesian product on a large dataset.