Re: Conditional rule? - Mailing list pgsql-sql

From André Næss
Subject Re: Conditional rule?
Date
Msg-id 020f01bff95f$ac6bd2a0$74b5f8c2@wkst6
Whole thread Raw
In response to Aggregates and Primary Keys  (Itai Zukerman <zukerman@math-hat.com>)
Responses Re: Conditional rule?
List pgsql-sql
----- Original Message -----
> > And this works as intended. There are however a few things that worries
me.
> > First of all, I can't seem to find any way to list the trigger and the
> > function, they seem invisible. This is problematic because my work will
be
> > continued by others, and allthough I will document everything I think it
> > should be possible to see the triggers and functions somehow...
> >
> > Secondly, I miss one final idea, when a delete is performed on the
> > b_news_unpublished table, I would like to set up a rule or procedure
that
> > sets the time value to null in b_news for each row that is affected by
the
> > delete. I understand that the OLD and NEW objects are accessible only
during
> > UPDATE or INSERT operations, so I can't quite see how to do this...
>
>
> OLD.* is available during TG_OP = ''DELETE'' in a trigger.

Indeed it is, I just didn't read the manual good enough :)

> There are sometimes some referential integrity problems on DELETE when
using
> triggers to do things on tables that have a RI relationship.  Like, if you
have
> a table that REFERENCES another table ON DELETE SET NULL, and there is a
DELETE
> proc on the referenced table that also does some other update on those
records,
> then the regular trigger might happen before the contraint trigger.  In
this
> case, an update would have a RI problem when it updates some attribute and
the
> contraint is checked again - it would fail since the referenced primary
key is
> deleted already but foreign key has not been SET NULL yet by the contraint
> trigger. I'm not sure what the rules are on the order of contraint trigger
and
> other trigger execution but sometimes the order isn't what you want and
then
> you get the problem on delete.  If it happens, like it happened to me, you
> might quit using foreign keys and just program your triggers as much as
you can
> to do the same checks that the contraint triggers are doing but by having
your
> own triggers do it, you have control of the order of how things happen on
> delete.

This seems ok to me, but I still don't like the invisibility as I pointed
out above. If someone else was to continue my work how would they be able to
see the triggers and procedures? They don't show up on "\d tablename", so it
is very tempting to simply do this in PHP (i.e. managing the
news_unpublished table with PHP.) I have the same issue with constraints, as
they are invisible too.

So, it comes down to something like this; I would really like to use
constraints and trigger procedures, but in order to do so I need to keep a
very close eye on my database, this means that I might get into trouble if I
need to do some changes to this system after several months -- will I
remember all these hidden behaviours? By doing it in PHP all the behaviour
will be there in code, all visible and (hopefully) understandable to both
myself and others.

Again, I'm a postgre newbie, so there might be something essential I've
simply overlooked, but some clarification concerning this topic would be
nice.

Thanks

André Næss



pgsql-sql by date:

Previous
From: "Robert B. Easter"
Date:
Subject: Re: Conditional rule?
Next
From: Itai Zukerman
Date:
Subject: Just 'sql'?