Re: Trigger disactivation and SELECT WAITING - Mailing list pgsql-general

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
GUIto 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,
andthis 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
subtabledata. 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@sss.pgh.pa.us]
Envoyé : mardi, 26. juillet 2005 19:57
À : Philippe Lang
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] Trigger disactivation and SELECT WAITING

"Philippe Lang" <philippe.lang@attiksystem.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
attentionto 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.
Soinsertions 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
havethe trigger at all if you do so many changes to the table with it deleted?  Why not improve the trigger to be smart
enoughto not interfere with what you need the insertion function to do? 

            regards, tom lane


-----Message d'origine-----
De : Janning Vygen [mailto:vygen@gmx.de]
Envoyé : mardi, 26. juillet 2005 17:39
À : pgsql-general@postgresql.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
bulkinserts, 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.
Triggersare "procedural". they fire on every inserted row. A rule is relational instead. If you use a rule you have
onlyone 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
calledmaterialized 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