From: | Craig James <cjames(at)emolecules(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Triggers and transactions |
Date: | 2013-01-28 18:54:20 |
Message-ID: | CAFwQ8rcYevFK8tayQ-M7J0ogzdHfuZ4=Jd5qxv4pwexPVcYOww@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
If I drop and then recreate a trigger inside of a single transaction, how
does it affect other processes trying to use the same table? Can they just
merrily go along their way using the table, or will they be blocked by an
exclusive lock?
We have a trigger that detects illegal drugs and dangerous chemicals (such
as explosives and flammable compounds that can't be shipped by air). It's
implemented as a trigger to ensure that even improperly coded application
software can't accidentally let a customer order a prohibited compound.
Unfortunately, the trigger's function is necessarily "heavyweight" and slow.
The drop-and-restore-trigger operation is needed when we're copying data
one server to another. Since the data on the primary source have already
been checked, there's no need to let the trigger re-check every row. When
I drop-and-recreate the trigger for the duration of a COPY operation, it
speeds the operation from (for example) 30 minutes to 15 seconds.
But if the drop-and-restore-trigger operation blocks all access to the
tables, that's a problem.
Thanks,
Craig
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2013-01-28 19:10:44 | Re: Triggers and transactions |
Previous Message | belal hamed | 2013-01-28 11:15:10 | Re: PostgreSQL over internet |