[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Gnumed-devel] (no subject)
|
From: |
Syan Tan |
|
Subject: |
Re: [Gnumed-devel] (no subject) |
|
Date: |
Sun, 19 Jun 2005 06:23:29 +0800 |
On Sat Jun 18 22:02 , Karsten Hilbert sent:
>On Fri, Jun 17, 2005 at 09:03:58PM +0800, Syan Tan wrote:
>
>> >> update clin_root_item set fk_episode = currval('pk_episode_seq')
>>
>> this of course , being contingent on Karsten allowing updates
>> to clin_root_item is special circumstances, ? a special group permission
>>
>
>It's not that I don't feel like allowing that for personal
>reasons. It's rather that doing so is inherently unsafe in
>PostgreSQL, eg child tables' keys WILL NOT GET UPDATED by it.
>
>Generally: If you update rows in a parent table directly those
>updates WILL NOT show up in the corresponding child tables.
>This is a severe limitation in PostgreSQL's inheritance
>support.
>
>Karsten
>--
>GPG key ID E4071346 @ wwwkeys.pgp.net
>E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
>
>
>_______________________________________________
>Gnumed-devel mailing list
>address@hidden
>http://lists.gnu.org/mailman/listinfo/gnumed-devel
may it's an older version of postgres ;
I dropped the update rule on clin_root_item and did the following:
select pk_item, fk_encounter, fk_episode from clin_root_item;
pk_item | fk_encounter | fk_episode
---------+--------------+------------
2 | 5 | 3
3 | 5 | 3
4 | 5 | 3
5 | 5 | 3
6 | 5 | 3
7 | 5 | 3
14 | 6 | 3
15 | 6 | 3
18 | 7 | 4
19 | 7 | 4
20 | 7 | 4
21 | 7 | 4
22 | 7 | 4
1 | 8 | 4
2 | 8 | 4
3 | 8 | 4
4 | 8 | 4
5 | 8 | 4
17 | 6 | 3
8 | 5 | 3
16 | 6 | 3
13 | 5 | 3
10 | 5 | 3
11 | 5 | 3
12 | 5 | 3
9 | 5 | 3
(26 rows)
gnumed=# select * from clin_episode;
pk_audit | row_version | modified_when | modified_by | pk |
fk_health_issue | fk_patient | description | is_open
----------+-------------+-------------------------------+-------------+----+-----------------+------------+--------------------------------------+---------
407 | 0 | 2005-05-07 23:20:53.747333+10 | gm-dbo | 3 |
2 | | postop infected laceration L forearm | f
408 | 0 | 2005-05-07 23:20:53.747333+10 | gm-dbo | 4 |
| 17 | a reinfection | t
(2 rows)
gnumed=# insert into clin_episode ( fk_health_issue ,description) values( 2, 'a
persistent sinus');
INSERT 564075 1
update clin_root_item set fk_episode = ( select max(pk) from clin_episode)
where
fk_encounter = 8;
ERROR: plpgsql functions cannot return type opaque
CONTEXT: compile of PL/pgSQL function "f_protect_clin_root_item" near line 0
gnumed=# \d clin_root_item
Table "public.clin_root_item"
Column | Type |
Modifiers
---------------+--------------------------+---------------------------------------------------------------------
pk_audit | integer | not null default
nextval('public.audit_fields_pk_audit_seq'::text)
row_version | integer | not null default 0
modified_when | timestamp with time zone | not null default
('now'::text)::timestamp(6) with time zone
modified_by | name | not null default "current_user"()
pk_item | integer | not null default
nextval('public.clin_root_item_pk_item_seq'::text)
clin_when | timestamp with time zone | not null default
('now'::text)::timestamp(6) with time zone
fk_encounter | integer | not null
fk_episode | integer | not null
narrative | text |
soap_cat | text | not null
Indexes:
"clin_root_item_pkey" primary key, btree (pk_item)
"idx_cri_encounter" btree (fk_encounter)
"idx_cri_episode" btree (fk_episode)
Check constraints:
"clin_root_item_soap_cat" CHECK (lower(soap_cat) = 's'::text OR
lower(soap_cat)
= 'o'::text OR lower(soap_cat) = 'a'::text OR lower(soap_cat) = 'p'::text)
Foreign-key constraints:
"$1" FOREIGN KEY (fk_encounter) REFERENCES clin_encounter(id)
"$2" FOREIGN KEY (fk_episode) REFERENCES clin_episode(pk)
Rules:
clin_ritem_no_del AS ON DELETE TO clin_root_item DO INSTEAD SELECT
f_protect_clin_root_item() AS f_protect_clin_root_item
clin_ritem_no_ins AS ON INSERT TO clin_root_item DO INSTEAD SELECT
f_protect_clin_root_item() AS f_protect_clin_root_item
clin_ritem_no_upd AS ON UPDATE TO clin_root_item DO INSTEAD SELECT
f_protect_clin_root_item() AS f_protect_clin_root_item
Triggers:
tr_clin_item_mod AFTER INSERT OR DELETE OR UPDATE ON clin_root_item FOR
EACH
ROW EXECUTE PROCEDURE f_announce_clin_item_mod()
Inherits: audit_fields
gnumed=# drop RULE clin_ritem_no_upd on clin_root_item;
DROP RULE
update clin_root_item set fk_episode = ( select max(pk) from clin_episode)
where
fk_encounter = 8;
UPDATE 5
gnumed=# select * from clin_narrative;
pk_audit | row_version | modified_when | modified_by | pk_item
|
clin_when | fk_encounter | fk_episode |
narrative | soap_cat | pk | is_rfe | is_aoe
----------+-------------+-------------------------------+-------------+---------+---------------------------+--------------+------------+------------------------------------------------------------------------------+----------+----+--------+--------
416 | 0 | 2005-05-07 23:20:53.747333+10 | gm-dbo | 2
|
2000-09-17 17:14:32+11 | 5 | 3 | bleeding cut forearm L
| s | 19 | t | f
417 | 0 | 2005-05-07 23:20:53.747333+10 | gm-dbo | 3
|
2000-09-17 17:16:41+11 | 5 | 3 | accid cut himself left
forearm -2/24 w/ dirty
blade rescuing self from being tentacled,
extraterrest.envir. | s | 20 | f | f
418 | 0 | 2005-05-07 23:20:53.747333+10 | gm-dbo | 4
|
2000-09-17 17:20:59+11 | 5 | 3 | left ulnar forearm; 6cm
dirty laceration;
skin/sc fat only; musc/tend not injured; no dist sens loss;
pain/redness++; smelly secretion+; no pus |
o
| 21 | f | f
419 | 0 | 2005-05-07 23:20:53.747333+10 | gm-dbo | 5
|
2000-09-17 17:21:19+11 | 5 | 3 | contam/infected knife
cut
left arm, ?extraterr. vector, needs ABs/surg/blood | a | 22 | f | f
420 | 0 | 2005-05-07 23:20:53.747333+10 | gm-dbo | 6
|
2000-09-17 17:02:00+11 | 5 | 3 | 1) inflamm.screen/std
ET
serology
2) debridement/loose adapt.; 10ml xylocitin sc; 00-Reprolene
3) Pen 1.5 Mega 1-1-1
4) review +2/7; tomorrow if symptoms/blood screen +ve
| p | 23 | f | f
421 | 0 | 2005-05-07 23:20:53.747333+10 | gm-dbo | 7
|
2000-09-17 17:14:32+11 | 5 | 3 | ?contaminated
laceration L
forearm | a | 24 | f | t
422 | 0 | 2005-05-07 23:20:53.747333+10 | gm-dbo | 14
|
2000-09-18 08:14:32+11 | 6 | 3 | knife cut follow-up,
pain/swelling | s | 25 | t
|
f
423 | 0 | 2005-05-07 23:20:53.747333+10 | gm-dbo | 15
|
2000-09-18 08:17:32+11 | 6 | 3 | postop infected
laceration
L forearm | a | 26 | f | t
424 | 0 | 2005-05-07 23:20:53.747333+10 | gm-dbo | 18
|
2005-05-07 11:18:31.62+10 | 7 | 4 | sore too
| s | 27 | f | f
425 | 0 | 2005-05-07 23:20:53.747333+10 | gm-dbo | 19
|
2005-05-07 11:18:32.71+10 | 7 | 4 | red again
| s | 28 | f | f
426 | 0 | 2005-05-07 23:20:53.747333+10 | gm-dbo | 20
|
2005-05-07 11:18:33.78+10 | 7 | 4 | cellulitis again;
| a | 29 | f | f
427 | 0 | 2005-05-07 23:20:53.747333+10 | gm-dbo | 21
|
2005-05-07 11:18:34.85+10 | 7 | 4 | antibiotics;review
| p | 30 | f | f
428 | 0 | 2005-05-07 23:20:53.747333+10 | gm-dbo | 22
|
2005-05-07 11:18:35.92+10 | 7 | 4 | red and swollen
| o | 31 | f | f
430 | 1 | 2005-05-21 20:09:11.155782+10 | postgres | 2
|
2005-05-07 23:17:29.32+10 | 8 | 5 | came back;
| s | 33 | f | f
429 | 1 | 2005-05-21 20:09:11.155782+10 | postgres | 1
|
2005-05-07 23:17:28.30+10 | 8 | 5 | not better;swab shows
vibrio parahaemolyticus; | s | 32 | f
| f
431 | 1 | 2005-05-21 20:09:11.155782+10 | postgres | 3
|
2005-05-07 23:17:30.34+10 | 8 | 5 | possible water related
cellulitis; ; | a | 34 | f
| f
433 | 1 | 2005-05-21 20:09:11.155782+10 | postgres | 5
|
2005-05-07 23:17:32.36+10 | 8 | 5 | still red;
| o | 36 | f | f
432 | 1 | 2005-05-21 20:09:11.155782+10 | postgres | 4
|
2005-05-07 23:17:31.35+10 | 8 | 5 | xray - ? need for
debridement; doxycycline; | p | 35 | f
| f
(18 rows)
gnumed=# select fk
gnumed=# select fk_episode, fk_encounter , pk_item from clin_narrative;
fk_episode | fk_encounter | pk_item
------------+--------------+---------
3 | 5 | 2
3 | 5 | 3
3 | 5 | 4
3 | 5 | 5
3 | 5 | 6
3 | 5 | 7
3 | 6 | 14
3 | 6 | 15
4 | 7 | 18
4 | 7 | 19
4 | 7 | 20
4 | 7 | 21
4 | 7 | 22
5 | 8 | 2
5 | 8 | 1
5 | 8 | 3
5 | 8 | 5
5 | 8 | 4
(18 rows)
Note that clin_narrative , which is a child table of clin_root_item ,
has fk_episode changed from 4 to 5 in pk_items 1,2,3,4,5 , where 5
is the pk of a clin_episode 'persistent sinus' which has just been
inserted pointing to the wound health issue.
may be if you modify the
clin_ritem_no_upd AS ON UPDATE TO clin_root_item DO INSTEAD SELECT
f_protect_clin_root_item() AS f_protect_clin_root_item
rule someway, where in some cases the update proceeds,
but I guess this complicates the idea that clin_root_item should
not be updateable. ( but note, this seems to be an application
constraint, not a postgres constraint).