Thread: Trigger disactivation and SELECT WAITING
Hi, I have a database with views that can take up to 2 hours to be calculated. During that time, it's not possible to run a function that inserts data into the database, apparently because this function disactivates a trigger while it runs, by deleting and creating the trigger again at the end. (At least in 7.4.X database, this is the only solution, right?) Running "ps -afxu" show that the process that tries to insert data gets stuck in the "SELECT WAITING" state. Is there a solution to this? Thanks! ---------------------------------- Philippe Lang Attik System
"Philippe Lang" <philippe.lang@attiksystem.ch> writes:
> I have a database with views that can take up to 2 hours to be
> calculated.
> During that time, it's not possible to run a function that inserts data
> into the database, apparently because this function disactivates a
> trigger while it runs, by deleting and creating the trigger again at the
> end. (At least in 7.4.X database, this is the only solution, right?)
"Only solution" to what? Why in the world would a view fool around with
removing triggers?
regards, tom lane
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.
Soinsertions are impossible in the database when views are being calculated.
Regards,
-----Message d'origine-----
De : Tom Lane [mailto:tgl@sss.pgh.pa.us]
Envoyé : mardi, 26. juillet 2005 15:14
À : Philippe Lang
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] Trigger disactivation and SELECT WAITING
"Philippe Lang" <philippe.lang@attiksystem.ch> writes:
> I have a database with views that can take up to 2 hours to be
> calculated.
> During that time, it's not possible to run a function that inserts
> data into the database, apparently because this function disactivates
> a trigger while it runs, by deleting and creating the trigger again at
> the end. (At least in 7.4.X database, this is the only solution,
> right?)
"Only solution" to what? Why in the world would a view fool around with removing triggers?
regards, tom lane
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
"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 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.
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 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
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
Am Mittwoch, 27. Juli 2005 09:47 schrieb Philippe Lang: > 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. Right. There are some things that can't be done with rules. > 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. As far as i understand your example it can be done with rules, too. > 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. just a thought: maybe you can insert a column "copyof_id" in your tabel and mark it if you copy orders. The trigger can check NEW.copyof and quit his work if it is marked. With rules there is a nice advantage when copying: rules are not invoked by COPY command (but sometimes it is a disadvantage if you need the rule invocation) kind regards janning