problem with multiple invocations of a deferred constraint trigger - Mailing list pgsql-general

Hello all,

I have two tables (simplified):

create table clin_episode (
    pk serial primary key,
    started timestamp with time zone,
    fk_clin_narrative integer
    unique
    default null
);

create table clin_narrative (
    pk serial primary key,
    fk_episode integer
    not null
    references clin_episode(pk),
    narrative text
    not null
);

The idea behind this is that clin_narrative stores ALL
narrative of a medical record. Clin_episode stores episodes
during which care was received. Such episodes do have a name
(such as "knee pain Nov/04") but such episode names are part
of the medical record narrative and should be stored in
clin_narrative, too.

Now, obviously I got a cyclic referential integrity problem
here, eg. narrative pointing to episodes which point back to
narrative. No problem, I added a deferred constraint trigger
like this (don't worry about the health issue stuff, it isn't
related to the problem):

*-----------------------------------------
-- an episode not linked to a health issue must have a
-- name (at least when the transaction ends ...)
\unset ON_ERROR_STOP
drop trigger tr_standalone_epi_needs_name on clin_episode;
drop function trf_standalone_epi_needs_name();
\set ON_ERROR_STOP 1

create function trf_standalone_epi_needs_name() returns opaque as '
declare
    msg text;
    narr_pk integer;
    narr_fk_episode integer;
begin
    -- debug ...
    raise notice ''%'', TG_OP;
    -- *if* we have a name it must belong to us ...
    -- (eg. check for cyclic referential integrity violations)
    if NEW.fk_clin_narrative is not null then
        select into narr_pk, narr_fk_episode cn.pk, cn.fk_episode
            from clin_narrative cn
            where cn.pk = NEW.fk_clin_narrative
            limit 1;
        if narr_fk_episode <> NEW.pk then
            msg := ''trf_standalone_epi_needs_name: clin_narrative row [''
                || narr_pk || ''] does not belong to episode [''
                || NEW.pk  || ''] and cannot thus name that episode'';
            raise exception ''%'', msg;
        end if;
        return NULL;
    end if;
    -- if linked to a health issue we do not have to have a name of our own ...
    if NEW.fk_health_issue is not null then
        return NULL;
    end if;
    msg := ''trf_standalone_epi_needs_name: episodes not linked to a health issue must point to a clin_narrative row at
theend of the transaction''; 
    raise exception ''%'', msg;
end;
' language 'plpgsql';

-- the trick is to defer the trigger ...
create constraint trigger tr_standalone_epi_needs_name
    after insert or update
    on clin_episode
    initially deferred
    for each row
        execute procedure trf_standalone_epi_needs_name()
;
*-----------------------------------------

This all works. However, to actually insert data I do the
following:

- insert into clin_episode with fk_clin_narrative=NULL
- insert into clin_narrative with fk_encounter = currval('clin_episode_pk_seq')
- update clin_episode with set fk_clin_narrative = currval('clin_narrative_pk_seq')

What I end up with is the trigger being queued up for checking
at the end of transaction TWICE. This is quite logical since I
did an INSERT and then an UPDATE and the trigger fires on
both.

However, the first trigger invocation (on INSERT) correctly
raises an exception since at that time the NEW row did not yet
have a suitable fk_clin_narrative.

What is the proper solution here ?

- Try to deal with INSERT and UPDATE somehow separately ? The
  problem I see with this is that *actually* I want to check the
  state of the row at the *latest* UDPATE in a transaction
  *only* and there might possibly be several UPDATEs in a tx.

- Don't check whether *NEW* has a fk_clin_narrative at trigger
  execution time (eg. at the end of the transaction) but
  rather explicitely SELECT fk_clin_narrative from the table
  where pk = NEW.pk ? IOW check the actual state of the row
  (which may have undergone several UPDATEs) at the end of the
  transaction instead of the NEW row at trigger queue up
  time. Would I then not run into trouble when
  trying to insert more than one episode within the same
  transaction ?

I'd be happy if someone had some help on this.

(Will post full schema details if needed.)


Karsten

GnuMed i18n coordinator
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346