From: Richard Huxton <dev@archonet.com>
To: Craig James <cjames@emolecules.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Triggers and transactions
Date: Mon, 28 Jan 2013 19:10:44 +0000
Message-ID: <5106CD34.3080704@archonet.com> (raw)
In-Reply-To: <CAFwQ8rcYevFK8tayQ-M7J0ogzdHfuZ4=Jd5qxv4pwexPVcYOww@mail.gmail.com>
References: <CAFwQ8rcYevFK8tayQ-M7J0ogzdHfuZ4=Jd5qxv4pwexPVcYOww@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, cjames@emolecules.com
Subject: Re: Triggers and transactions
In-Reply-To: <5106CD34.3080704@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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