Re: Postgres Connections Requiring Large Amounts of Memory

Dawn Hollingsworth <dmh(at)airdefense(dot)net> writes:
> 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?

The only theory I can come up with is that the deferred trigger list is
getting out of hand. Since you have foreign keys in all the tables,
each insert or update is going to add a trigger event to the list of
stuff to check at commit. The event entries aren't real large but they
could add up if you insert or update a lot of stuff in a single
transaction. How many rows do you process per transaction?

regards, tom lane