Subject: Swelling backend.

From: "David J. Hughes" <[email-masked]>
Date: Tue, 27 Jul 93 21:38:49 -0700

One piece of code I've been working on that utilises Postgres is a daemon
process that hangs off a UDP socket and receives "data" from other nodes on
our network.  Upon receipt of a packet, it talks to Postgres for various
things.  Speed is important here so I leave the connection open while
waiting for the next packet to turn up (could be nanoseconds or hours
away).

I've noticed that as time passes, the backend grows in size.  I'm not sure
if it's a leak or if I'm not clearing something via libpq.  I have ensured
that any retrieve is followed by a PQclear of the portal in use.

Is there anything else that requires clearing or closing to stop this
behaviour?  Has anyone else noticed swelling backends if they remain open
and active for a long time (my tests have generated a query rate which
averages out to about one every 3 seconds sustained for half an hour or so
(although you can see growth within a few quick bursts of 25 queries)). 

Any clues?  The backend eventually dies due to lack of memory so I'd like
to resolve this without PQreset'ing and forcing a new backend (and the
resultant performance hit).

   ___                                  David J. Hughes    [email-masked]
  /   \                /  /    /        
 /  __/ __   __   ____/  /    / __            Senior Network Programmer
/    \ /  \ /  \ /   /  /    / /  \  /     Information Technology Services
\____/ \__//   / \__/   \___/ /   / /   Qld. 4229  AUSTRALIA  (+61 75 951450)

---

From: "David J. Hughes" <[email-masked]>
Date: Wed, 28 Jul 1993 16:05:40 +1000 (EST)

On Tue, 27 Jul 1993, Paul M. Aoki wrote:

> the one thing Purify tells me about is the fact that the yacc-generated
> parser doesn't free its memory if an elog(WARN,...) is generated from
> within the parser.
> 
> i wouldn't think this would happen in your environment, though.

Thanks for the quick response Paul.

Is there anything other than PQclear() that I need to call from time to
time to free any internal buffers?  This process is the event manager for
the Network Management System I'm writing so the one proc should run
24hours * 7days.  Waiting for a new backend after a reset just kills the
performance of my stuff.  I guess I could reset it every 100 or so events
(ie. after 500-800 queries) but that'd be a shocking hack.

On another topic, would you guys be interested in an SQL to Postquel
translator?  Several people want to run my code over Oracle and Progress
so I'm moving to SQL internally.  I wrote a grammer for a subset of SQL
that offered the facilities I need and coded a yacc-based parser for it. 
It has backend hooks for generating other QL's and I've written the code
for Postquel.

It's very limited (doesn't allow joins or nested selects or anything other
than stock simple queries) but it may be of use as a starting point.