pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTriggers and transactions
3+ messages / 3 participants
[nested] [flat]
* Triggers and transactions
@ 2013-01-28 18:54 Craig James <cjames@emolecules.com>
2013-01-28 19:10 ` Re: Triggers and transactions Richard Huxton <dev@archonet.com>
2013-01-28 19:25 ` Re: Triggers and transactions Rosser Schwarz <rosser.schwarz@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Craig James @ 2013-01-28 18:54 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Triggers and transactions
2013-01-28 18:54 Triggers and transactions Craig James <cjames@emolecules.com>
@ 2013-01-28 19:10 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2013-01-28 19:10 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: pgsql-performance
On 28/01/13 18:54, Craig James wrote:
> 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?
>
I *think* it blocks, but in any case, read on...
> We have a trigger that detects illegal drugs and dangerous chemicals
> (such as explosives and flammable compounds that can't be shipped by air).
<pedantry mode="full">detects a reference to illegal... (unless you've
hooked your RDBMS up to some sort of x-ray scanner, in which case I
salute you sir)</pedantry>
> 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.
Run the copy as a different user than ordinary applications (a good idea
anyway). Then the function can just check current_user and exit for the
copy.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Triggers and transactions
2013-01-28 18:54 Triggers and transactions Craig James <cjames@emolecules.com>
@ 2013-01-28 19:25 ` Rosser Schwarz <rosser.schwarz@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Rosser Schwarz @ 2013-01-28 19:25 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: pgsql-performance
On Mon, Jan 28, 2013 at 10:54 AM, Craig James <cjames@emolecules.com> wrote:
> But if the drop-and-restore-trigger operation blocks all access to the
> tables, that's a problem.
>
Were the triggers in question created with "CREATE CONSTRAINT TRIGGER"? If
not, "ALTER TABLE foo DISABLE TRIGGER USER" may do what you need here.
rls
--
:wq
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-01-28 19:25 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-01-28 18:54 Triggers and transactions Craig James <cjames@emolecules.com>
2013-01-28 19:10 ` Richard Huxton <dev@archonet.com>
2013-01-28 19:25 ` Rosser Schwarz <rosser.schwarz@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox