Re: Trigger disactivation and SELECT WAITING

Am Dienstag, 26. Juli 2005 16:07 schrieb Philippe Lang:
> Hi,
>
> I meant: in 7.4.X databases, is there a way of disabling a trigger without
> deleting it? I guess the answer is no.
>
> That's what my plpgsql insert function does, and because of this, if a view
> is running at the same moment on the same tables (some views can take up to
> 2 hours to be calculated), the insert function gets stuck in a SELECT
> WAITING state. So insertions are impossible in the database when views are
> being calculated.

I guess you should rethink your databse design. Disabling triggers is
convinient if your populate a database or you do bulk inserts, but you
shouldn't disable them in a production database.

In my experience rules are much more powerful and faster than triggers but on
the other side much more difficult. Triggers are "procedural". they fire on
every inserted row. A rule is relational instead. If you use a rule you have
only one more statement on insert even if you insert lots of data. On the
other hand rules are not called by COPY Statements. And some things can't be
done with rules.

The waiting state ist ok, because other transaction can just not know if you
commit your changes to the trigger or not.

And i don't know what you mean with "view is running for 2 hours" i guess you
have some functionality to build so called materialized views, right?

if you give me some more information waht you are really doing i can help you.
as your mail is .ch you might prefer german language and can contact via
personal mail.

kind regards,
janning