Thread: Speed
Hi,
I have a little problem but it's very important for us.
I use a diskless server with RedHat and two stations for now. I use
Flagship to compile the programs in clipper and SQLkit_pg to comunicate
with postgresql.
Everything is ok until I run the application in two stations. They start
fine, but after some minutes they stop for about 30 seconds, I use the
command top for monitoring the process and on that moment the postmaster
disappears. Later they continue with the process and the postmaster
appears again.
It stops after the program do an insert, but not in all of this inserts.
When I run just one station, don't have this problem.
Server
Architecture : Pentium II
Memory : 64Mb
Operating System : RedHat 6.2
PostgreSQL version : 7.0.3-2
Compiler used : FlagShip-4.48-7451
Others : FS2tools-4.48-7451
SQLkit_PG-1.00
Client - Diskless
Memory : 16Mb
Architecture : Pentium
Printer : TM930 Paralell Port
My database is 268 Mb, 60 tables, the largest table has about 181659
rows and 46 columns. I'm using index in each table.
Can you help me, please ???
Regards,
Karla
I want to have a table (a category table in this instance) that will do the following: reference a field within the same table (you'll see why later) have a field automatically changed to another row's field value upon deletion of the referenced field This is what I've come up with: create table category ( catid serial primary key, parentid int references category ( catid ) on delete <?trigger here?>, catname text unique, catdesc text ); I know I need to create a trigger but I'm not sure how to do that. I've read the docs, but an example would be great for this particular need. Also, I know I can do this to update the category with three statements but I'm pretty sure I can do it in one with a subselect but I'm still fuzzy on how to do that. This is a bad mix of pseudo code and sql. A = category.parentid from row being deleted B = select parentid from category where catid=A; update category set parentid=B where parentid=A; finish deleting row. Does any of this make sense? Alan Young Programmer/Analyst IDIGlobal.com
On Thu, 21 Dec 2000, Alan Young wrote: > reference a field within the same table (you'll see why later) > have a field automatically changed to another row's field value upon > deletion of the referenced field > > create table category ( > catid serial primary key, > parentid int references category ( catid ) on delete <?trigger here?>, > catname text unique, > catdesc text > ); > > I know I need to create a trigger but I'm not sure how to do that. I've > read the docs, but an example would be great for this particular need. You can use a trigger, but not w/syntax like this. REFERENCES... ON DELETE... only allows you to to do regular referential integrity stuff (set the field to null, block deletions, etc.), not look up a better value and put it there. You want to create a trigger with something like: CREATE TRIGGER foo BEFORE DELETE ON category FOR EACH ROW EXECUTE PROCEDURE foo(); Your function foo() can dig the new value you want out and update this for all the related records. Can you explain what it is that you want to do? It sounds like you're building a tree here, and there might be a better strategy overall than the one you're starting with. -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
> You can use a trigger, but not w/syntax like this. REFERENCES... ON > DELETE... only allows you to to do regular referential integrity stuff > (set the field to null, block deletions, etc.), not look up a better value > and put it there. > > You want to create a trigger with something like: > > CREATE TRIGGER foo BEFORE DELETE ON category FOR EACH ROW EXECUTE > PROCEDURE foo(); So the parentid would then just be parentid int references category ( catid ), ? > Your function foo() can dig the new value you want out and update this for > all the related records. I'm still not clear on how I would get the appropriate data into the function. CREATE FUNCTION updatecat ( ??? ) RETURNS int AS 'A = select parentid from category where catid=<catid to be deleted--where does this come from?>; update category set parentid=A where parentid=<catid to be deleted>;' LANGUAGE 'sql'; How do I get the appropriate info into the query? Also, I'm fairly certain I can do that select/update as a subselect but I'm not sure how to go about doing that. I'm new to subqueries as well. > Can you explain what it is that you want to do? It sounds like you're > building a tree here, and there might be a better strategy overall than > the one you're starting with. I want to have a category table that supports sub categories. Easy enough, the table I defined in my original post works just fine for that. But what I want to happen is that a subcategory will be reassigned automagically to it's parent category's parent category upon deletion of the parent category. Ummm ... I'm not sure how to say that any better. For example, I have the following categorys catid | parentid | catname | catdesc ======================== 0 | | TOP | Top level category 1 | 0 | cat1 | cat one 2 | 1 | cat1.1 | cat one:one 3 | 2 | cat1.1.1 | cat one:two:three delete from category where catid=2; I want the following to happen automagically. catid | parentid | catname | catdesc ======================== 0 | | TOP | Top level category 1 | 0 | cat1 | cat one 3 | 1 | cat1.1.1 | cat one:two Does that make more sense? Alan Young Programmer/Analyst IDIGlobal.com