Re: Trigger disactivation and SELECT WAITING

Thanks Tom, thanks Janning,

I found triggers very convenient to do different tasks in the database, and these tasks go far beyond what we can do in rules, Janning.

When a line is being inserted in an order, the insert trigger automatically inserts data in a subtable of the order line, for example. In this subtable, there are informations regarding the "planning" of the order. People can use the GUI to populate the order, but things won't break if the user opens the database table directly, which can happen sometimes. Without the trigger, an "insert function click" would have to be used each time an order line is being added, and this is not that effective from a user-experience point of view, I think. Or would require a lot a client-coding.

Now the use of a trigger has a drawback: when you want to duplicate an order, for example. During the duplication function, I would like to disable the trigger, in order to make a copy of the order, order lines, and order lines subtable data. This is much easier than keeping the trigger, and having to delete default data it inserts in the new order.

I'm not sure how I can improve the trigger in this case, and make it smarter, so I don't have to disable it during duplication...

I hope I was clear...

Philippe

-----Message d'origine-----
De : Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Envoyé : mardi, 26. juillet 2005 19:57
À : Philippe Lang
Cc : pgsql-general(at)postgresql(dot)org
Objet : Re: [GENERAL] Trigger disactivation and SELECT WAITING

"Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch> writes:
> I meant: in 7.4.X databases, is there a way of disabling a trigger without deleting it? I guess the answer is no.

Nothing officially supported, anyway. There's a pg_trigger.tgenabled column but I'm not sure which operations pay attention to it.

> 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 I question a database design in which you routinely have to drop triggers in order to get your work done. Why have the trigger at all if you do so many changes to the table with it deleted? Why not improve the trigger to be smart enough to not interfere with what you need the insertion function to do?

regards, tom lane

-----Message d'origine-----
De : Janning Vygen [mailto:vygen(at)gmx(dot)de]
Envoyé : mardi, 26. juillet 2005 17:39
À : pgsql-general(at)postgresql(dot)org
Cc : Philippe Lang
Objet : Re: [GENERAL] 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