Re: Shared Memory: out of memory

(Just getting caught up on all my emails..)

Yes, you might want to, if it's not too late already, rethink your
database design. I'm guessing that these tables are generated by code and
almost all of them are probably identical. Am I correct? If so, you
might be able to do it the way we did, nicknamed STH for Single Table
Hierarchy. This design merged about eight tables all into one and
basically allows us unlimited depth in the hierarchy. It's quite simple
really, just add a ParentID field to your table. This is how we do it:

CREATE TABLE tblStResTree (
TreeID int4 NOT NULL CHECK (TreeID>0),
Name character text NOT NULL CHECK (Name<>''),
Description text,
ParentID int4 NOT NULL,
SurID text,
PRIMARY KEY (TreeID)
);

Simplified of course.. there's actually eight other fields we use. Now,
you may or may not want to have a ID 0 as root. Queries look slightly
strange, but you'll get the hang of it. If you wanted to view the
parent's name instead of it's id, do this:

SELECT child.TreeID, child.Name, child.Description, parent.Name
FROM tblStResTree as child, tblStResTree as parent
WHERE child.ParentID=parent.TreeID;

Because it's a hierarchy, all you have to do to move an entire section of
your tree is change the ParentID. SurID is sort of a numeric PATH
generated by a trigger, so instead of 'var/spool/mail/cedarc' you would
have something like '8.29.41.132', where the numbers are the TreeID's of
all ancestors of the record. You can use this for all sorts of cool
things like finding all descendents:

SELECT ... WHERE SurID LIKE '<my surid>.%'

We use this for doing inheritance where a value in a record higher up the
hierarchy will propagate down to all it's descendents if that field is
null in the descendents. Very useful for us..

Hope this helps,
-Cedar

On Tue, 19 Dec 2000, Joseph Shraibman wrote:

> 200000 tables is rather a lot. As I'm sure someone on hackers will tell
> you, postgres is not designed to handle such bad database design.
>
> Alexaki Sofia wrote:
> >
> > Hello,
> >
> > I'm working with the JDBC driver from the Postgresql 7.0.2 distribution.
> >
> > My application creates transactions consisting of about 3000
> > updates operations ('insert into table' and 'create table').
> > My database consists of a huge (200000) number of tables which
> > form a hierarchy.
> >
> > The postmaster throws the following error massage
> >
> > >NOTICE: ShmemAlloc: out of memory
> > >FATAL 1: LockAcquire: lock table 1 is corrupted
> >
> > What can have caused this error? How can I overcome it?
> > Should I define the parameter -B of the postmaster?
> >
> > Thank you in advance
> > Sofia Alexaki
>
> --
> Joseph Shraibman
> jks(at)selectacast(dot)net
> Increase signal to noise ratio. http://www.targabot.com
>