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