[SQL] trigger to maintain relationships
I am maintaining a set of hierarchical data that looks a lot like a tree. (And my SQL is very rusty. And I'm new to postgres.) Questions: - 1.) Is the following a reasonable solution? Is there a postgres-specific way to handle this better? Is there a good generic SQL way to handle this? 2.) Can I write pure "SQL" triggers to handle this? Am I getting close in my first cut (below)? 3.) Any other ideas/suggestions? I have one table with essentially the nodes of a tree: nodes -- node_id integer parent_id integer references nodes(node_id) ...and other descriptive columns... I want an easy way to find all the elements of a subtree. Not being able to think of a good declarative solution, I was thinking about cheating and maintaining an ancestors table: ancestors --- node_idinteger ancestor_id integer references nodes(node_id) I figured I could populate the ancestors table via trigger(s) on the nodes table. Then I should be able to find a whole subtree of node X with something like: select * from nodes where node_id in ( select node_id from ancestors where ancestor_id = X) Here's my best guess so far at the triggers (but, obviously, no luck so far): --insert trigger create function pr_tr_i_nodes() returns opaque as ' insert into ancestors select NEW.node_id, ancestor_id from ancestors where node_id = NEW.parent_id;' language sql; create trigger tr_i_nodes after insert on nodes for each row execute procedure pr_tr_i_nodes(); --delete trigger create function pr_tr_d_nodes() returns opaque as ' delete from ancestors where node_id = OLD.parent_id;' language sql; create trigger tr_d_nodes after insert on nodes for each row execute procedure pr_tr_d_nodes(); --update trigger create function pr_tr_u_nodes() returns opaque as ' delete from ancestors where node_id = OLD.parent_id; insert into ancestors select NEW.node_id, ancestor_id from ancestors where node_id = NEW.parent_id;' language sql; create trigger tr_u_nodes after insert on nodes for each row execute procedure pr_tr_u_nodes(); I realize the update trigger could be handled a multitude of ways and that my first guess may be pretty lousy. But I figured the insert/update triggers would be pretty straightforward. Am I missing something basic? I also tried things like (following the one example in the reference manual): --insert trigger create function pr_tr_i_nodes() returns opaque as ' insert into ancestors select NEW.node_id, ancestor_id from ancestors where node_id = NEW.parent_id; return NEW;' language 'plpgsql'; create trigger tr_i_nodes after insert on nodes for each row execute procedure pr_tr_i_nodes(); ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] trigger to maintain relationships
I think I figured out my join syntax error (sorry for confusing the issue with noise like that). I'd still be interested in general comments on design. FYI, join should've looked like: create function pr_tr_i_nodes() returns opaque as ' insert into ancestors select NEW.node_id, ancestor_id from NEW left outer join ancestors on (NEW.parent_id = ancestors.node_id); return NEW;' language 'plpgsql'; create trigger tr_i_nodes after insert on nodes for each row execute procedure pr_tr_i_nodes(); David M wrote: > I am maintaining a set of hierarchical data that looks a lot like a > tree. (And my SQL is very rusty. And I'm new to postgres.) > > Questions: > - > 1.) Is the following a reasonable solution? Is there a > postgres-specific way to handle this better? Is there a good generic > SQL way to handle this? > 2.) Can I write pure "SQL" triggers to handle this? Am I getting close > in my first cut (below)? > 3.) Any other ideas/suggestions? > > I have one table with essentially the nodes of a tree: > > nodes > -- > node_id integer > parent_id integer references nodes(node_id) > ...and other descriptive columns... > > I want an easy way to find all the elements of a subtree. Not being > able to think of a good declarative solution, I was thinking about > cheating and maintaining an ancestors table: > > ancestors > --- > node_idinteger > ancestor_id integer references nodes(node_id) > > I figured I could populate the ancestors table via trigger(s) on the > nodes table. Then I should be able to find a whole subtree of node X > with something like: > > select * > from nodes > where node_id in ( > select node_id > from ancestors > where ancestor_id = X) > > Here's my best guess so far at the triggers (but, obviously, no luck so > far): > > --insert trigger > create function pr_tr_i_nodes() returns opaque > as ' > insert into ancestors > select NEW.node_id, ancestor_id > from ancestors > where node_id = NEW.parent_id;' > language sql; > create trigger tr_i_nodes after insert > on nodes for each row > execute procedure pr_tr_i_nodes(); > > --delete trigger > create function pr_tr_d_nodes() returns opaque > as ' > delete from ancestors > where node_id = OLD.parent_id;' > language sql; > create trigger tr_d_nodes after insert > on nodes for each row > execute procedure pr_tr_d_nodes(); > > --update trigger > create function pr_tr_u_nodes() returns opaque > as ' > delete from ancestors > where node_id = OLD.parent_id; > > insert into ancestors > select NEW.node_id, ancestor_id > from ancestors > where node_id = NEW.parent_id;' > language sql; > create trigger tr_u_nodes after insert > on nodes for each row > execute procedure pr_tr_u_nodes(); > > I realize the update trigger could be handled a multitude of ways and > that my first guess may be pretty lousy. But I figured the > insert/update triggers would be pretty straightforward. Am I missing > something basic? I also tried things like (following the one example in > the reference manual): > > --insert trigger > create function pr_tr_i_nodes() returns opaque > as ' > insert into ancestors > select NEW.node_id, ancestor_id > from ancestors > where node_id = NEW.parent_id; > > return NEW;' > language 'plpgsql'; > create trigger tr_i_nodes after insert > on nodes for each row > execute procedure pr_tr_i_nodes(); > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Query optimizing - paradox behave
NOTICE: QUERY PLAN: Sort (cost=2194791.19..2194791.19 rows=2555204 width=284) -> Merge Join (cost=8978.44..9453.57 rows=2555204 width=284) -> Sort (cost=990.43..990.43 rows=8725 width=72) -> Seq Scan on patient (cost=0.00..212.25 rows=8725 width=72) -> Sort (cost=7988.00..7988.00 rows=29286 width=212) -> Seq Scan on study (cost=0.00..1236.86 rows=29286 width=212) Restructured tables i.e. PAtient-study relationship is 1:n realized with column patientoid in table study. Table "patient" Attribute | Type | Modifier --++-- chilioid | character varying(80) | name | text | id | character varying(256) | birthdate| date | birthtime| time | sex | character(1) | medicalrecordlocator | character varying(128) | Table "study" Attribute| Type | Modifier ++-- chilioid | character varying(80) | instanceuid| character varying(64) | id | character varying(64) | studydate | date | studytime | time | modality | character varying(2) | manufacturer | character varying(128) | referingphysician | text | description| character varying(128) | manufacturersmodelname | character varying(128) | importtime | double precision | chilisenderid | character varying(80) | accessionnumber| character varying(64) | institutionname| character varying(128) | workflowstate | character varying(8) | flags | character varying(8) | performingphysician| character varying(128) | reportingphysician | character varying(128) | patientoid | character varying(80) | The times of the processes are escape-eliminated by statistical methods. I determined that the "compare" database is 8% times faster than the new restructured "pacs" database. How can I understand this? Whats my mistake? Anybody who can make some sugestions on the above will receive my enthusiastic gratitude David M. Richter begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum;Division Medizinische und Biologische Informatik version:2.1 email;internet:[EMAIL PROTECTED] adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-15296 fn:David M. Richter end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Query optimizing - paradox behave
ient.name using <" > 2tableni NOTICE: QUERY PLAN: Sort (cost=2194791.19..2194791.19 rows=2555204 width=284) -> Merge Join (cost=8978.44..9453.57 rows=2555204 width=284) -> Sort (cost=990.43..990.43 rows=8725 width=72) -> Seq Scan on patient (cost=0.00..212.25 rows=8725 width=72) -> Sort (cost=7988.00..7988.00 rows=29286 width=212) -> Seq Scan on study (cost=0.00..1236.86 rows=29286 width=212) Restructured tables i.e. PAtient-study relationship is 1:n realized with column patientoid in table study. Table "patient" Attribute | Type | Modifier --++-- chilioid | character varying(80) | name | text | id | character varying(256) | birthdate| date | birthtime| time | sex | character(1) | medicalrecordlocator | character varying(128) | Table "study" Attribute| Type | Modifier ++-- chilioid | character varying(80) | instanceuid| character varying(64) | id | character varying(64) | studydate | date | studytime | time | modality | character varying(2) | manufacturer | character varying(128) | referingphysician | text | description| character varying(128) | manufacturersmodelname | character varying(128) | importtime | double precision | chilisenderid | character varying(80) | accessionnumber| character varying(64) | institutionname| character varying(128) | workflowstate | character varying(8) | flags | character varying(8) | performingphysician| character varying(128) | reportingphysician | character varying(128) | patientoid | character varying(80) | The times of the processes are escape-eliminated by statistical methods. I determined that the "compare" database is 8% times faster than the new restructured "pacs" database. How can I understand this? Whats my mistake? Anybody who can make some sugestions on the above will receive my enthusiastic gratitude David M. Richter begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum;Division Medizinische und Biologische Informatik version:2.1 email;internet:[EMAIL PROTECTED] adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-15296 fn:David M. Richter end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: Query optimizing - paradox behave
Hallo! Thanks a lot to You Tom. I stared only at the user and the system time. I didn't found any C-function, with wich I could measure the whole time, so I used getrusage(). I did not recognize, that the whole time is reduced, because the User time is increased. Could that anybody explain to me? Why is the usertime increased and the whole time is decreased? # Anyway .. Thanks all a lot for Your effort. I will now tune my radiology-database further... Thankful Greetings David "David M. Richter" <[EMAIL PROTECTED]> writes: > The query with the 3 tables is faster than the query with 2 tables. How you figure that? > time psql -d compare -c "SELECT patient.*,study.* FROM > patient,study,relpatient_study000 r0 WHERE > (patient.chiliOID=r0.parentOID AND study.chiliOID=r0.childOID) order by > patient.name using <" > 3tableni > 1.671u 0.130s 0:11.14 16.4% 0+0k 0+0io 208pf+0w > time psql -d pacs -c "SELECT patient.*,study.* FROM patient,study WHERE > (patient.chiliOID=study.patientOID ) order by patient.name using <" > > 2tableni > 1.730u 0.120s 0:09.44 19.5% 0+0k 0+0io 208pf+0w 9.44 vs 11.14 seconds looks like a clear advantage for the second query to me... begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum;Division Medizinische und Biologische Informatik version:2.1 email;internet:[EMAIL PROTECTED] adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-15296 fn:David M. Richter end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: Query optimizing - paradox behave
Hallo Stephan! Are there several versions of dbPG95GetIndex existing, or did you mention postgres version 7.1.2? With a little help I have killed the Problem!! Yeah, But now I trying to improve the C-code. Do You have any experience with optimizing C-Code. Are there some new miracle-like function, wich improves the speed dramatically? Do You know some database options wich I could change for better performance? Thanks a lot for Your suggestions!! David >What version are you using? (dbPG95GetIndex?) begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum;Division Medizinische und Biologische Informatik version:2.1 email;internet:[EMAIL PROTECTED] adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-15296 fn:David M. Richter end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] View consistency
Hello! Im using several views for the Usermanagment of a database. My question is: How does postgres keep the views consistent to the according tables( if the original table has been changed)? Is there a Rule? and how is the Rule invoked. With ON DELETE UPDATE INSERT of the original table? I didnt found any Rule in pg_rules. There should be a rule, because I cannot imagine another way wich tells the view that the table has been changed. The problem is: If updating the view takes too much time after every little manipulation, I couldnt use views for usermanagement because of performance problems. Any hints and facts? Thanks in advance David begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum/German Cancer Research Center;Division Medizinische und Biologische Informatik version:2.1 email;internet:[EMAIL PROTECTED] adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-17024 fn:David M. Richter end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] table restruction
Hello! I want to restructure a table called study. this table has the following structure: Table "study" Attribute| Type | Modifier ++-- chilioid | character varying(80) | instanceuid| character varying(64) | id | character varying(64) | studydate | date | studytime | time | modality | character varying(2) | manufacturer | character varying(128) | referingphysician | character varying(128) | description| character varying(128) | manufacturersmodelname | character varying(128) | importtime | double precision | chilisenderid | character varying(80) | accessionnumber| character varying(64) | institutionname| character varying(128) | workflowstate | character varying(8) | flags | character varying(8) | performingphysician| character varying(128) | reportingphysician | character varying(128) | parentoid | character varying(80) | So , what I have to do is to move the column parentoid between chilioid and instanceoid. After that operation parentoid is the second column in the table study. Can I do any restructuring in the running database or should I dump the database and create a new changed schema and then put the data from the dump back? So this would spent a lot of time, otherwise it works sure. Is there any oppertunity to restructure the existing database without using a dump? Thanks in advance David begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum/German Cancer Research Center;Division Medizinische und Biologische Informatik version:2.1 email;internet:[EMAIL PROTECTED] adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-17024 fn:David M. Richter end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] table restruct...
Hi! Thanks, to You! Yes I have to do . Now I solved that problem with rename the original table study to _study then create the new right structured table study , Insert into study (chilioid,...,...) SELECT * FROM _study; Ok not elegant but it works. Another questions: Can I change the physical order of the rows in a database? Is the order of a database under all circumstances the same? (in pg) That is essential for my further restruction of the database... Thanks a lot David begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum/German Cancer Research Center;Division Medizinische und Biologische Informatik version:2.1 email;internet:[EMAIL PROTECTED] adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-17024 fn:David M. Richter end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Cursor names in a self-nested function
Hi, I am trying to write a function that contains a cursor and iteratively calls itself. It is along the lines of, CREATE FUNCTON test(id integer) RETURNS TEXT AS $BODY$ DECLARE mycursor CURSOR FOR SELECT * FROM myfunction(id); newid INTEGER; out = TEXT; BEGIN out := ''; OPEN mycursor; LOOP FETCH my_cursor INTO newid; out := out || test (newid); END LOOP; RETURN out; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE This returns an ERROR stating that "mycursor" is already in use. I understand this occurs because cursor names must be unique across, as well as within, functions. So, my question is whether there is a way I can dynamically declare a cursor name, for example by appending a incremental number or guid to make the name unique? Just trying to concatenate two passed arguments in the DECLARE statement unsurprisingly fails. Any other solutions are of cause welcome. Many thanks, - David David M. Kidd Research Associate Center for Population Biology Silwood Park Campus Imperial College London 0207 594 2470
