Re: Shared Memory: out of memory
От | Cedar Cox |
---|---|
Тема | Re: Shared Memory: out of memory |
Дата | |
Msg-id | Pine.LNX.4.21.0101031500110.31552-100000@nanu.visionforisrael.com обсуждение исходный текст |
Ответ на | Re: Shared Memory: out of memory (Joseph Shraibman <jks@selectacast.net>) |
Список | pgsql-interfaces |
(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.NameFROM tblStResTree as child, tblStResTree as parentWHERE 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@selectacast.net > Increase signal to noise ratio. http://www.targabot.com >
В списке pgsql-interfaces по дате отправления: