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

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.

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. 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

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@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 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@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 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

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