Re: Postgres Connections Requiring Large Amounts of Memory

The database is used to store information for a network management
application. Almost all the Primary Keys are MACADDR or
MACADDR,TIMSTAMPTZ and the Foreign Keys are almost always on one MACADDR
column with "ON UPDATE CASCADE ON DELETE CASCADE". It's not very
complicated. I have not written any triggers of my own.

The connection I was looking at only does inserts and updates, no
deletes. All database access is made through stored procedures using
plpgsql. The stored procedures all work like:
table1( id MACADDR, ... Primary Key(id) )
table2( id MACADDR, mytime TIMESTAMPTZ, .... Primary Key(id, mytime),
FOREIGN KEY(id) REFERENCES table1 ON UPDATE CASCADE ON DELETE CASCADE)

Update table1
if update row count = 0 then
insert into table1
end if

insert into table 2

I'm not starting any of my own transactions and I'm not calling stored
procedures from withing stored procedures. The stored procedures do have
large parameters lists, up to 100. The tables are from 300 to 500
columns. 90% of the columns are either INT4 or INT8. Some of these
tables are inherited. Could that be causing problems?

- Dawn

> Hmm. This only seems to account for about 5 meg of space, which means
> either that lots of space is being used and released, or that the leak
> is coming from direct malloc calls rather than palloc. I doubt the
> latter though; we don't use too many direct malloc calls.
>
> On the former theory, could it be something like updating a large
> number of tuples in one transaction in a table with foreign keys?
> The pending-triggers list could have swelled up and then gone away
> again.
>
> The large number of SPI Plan contexts seems a tad fishy, and even more
> so the fact that some of them are rather large. They still only account
> for a couple of meg, so they aren't directly the problem, but perhaps
> they are related to the problem. I presume these came from either
> foreign-key triggers or something you've written in PL functions. Can
> you tell us more about what you use in that line?
>
> regards, tom lane