Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from
	<pgsql-performance-owner+M49153=pgsql-performance-ar@postgresql.org>)
	id 1Tzu6W-0006Lk-8i for pgsql-performance@arkaria.postgresql.org;
	Mon, 28 Jan 2013 19:10:52 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
	<pgsql-performance-owner+M49153=pgsql-performance-ar@postgresql.org>)
	id 1Tzu6V-0005vS-Ee for pgsql-performance@arkaria.postgresql.org;
	Mon, 28 Jan 2013 19:10:51 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <dev@archonet.com>) id 1Tzu6U-0005uj-4d
	for pgsql-performance@postgresql.org; Mon, 28 Jan 2013 19:10:50 +0000
Received: from archon2012.plus.com ([80.229.41.160] helo=mail.archonet.com)
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <dev@archonet.com>) id 1Tzu6R-0002Ss-2b
	for pgsql-performance@postgresql.org; Mon, 28 Jan 2013 19:10:49 +0000
Received: from [192.168.1.13] (ariel.office.archonet.com [192.168.1.13])
	by mail.archonet.com (Postfix) with ESMTP id 962042C0E20;
	Mon, 28 Jan 2013 19:10:44 +0000 (GMT)
Message-ID: <5106CD34.3080704@archonet.com>
Date: Mon, 28 Jan 2013 19:10:44 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:10.0.11) Gecko/20121122 Icedove/10.0.11
MIME-Version: 1.0
To: Craig James <cjames@emolecules.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Triggers and transactions
References: 
 <CAFwQ8rcYevFK8tayQ-M7J0ogzdHfuZ4=Jd5qxv4pwexPVcYOww@mail.gmail.com>
In-Reply-To: 
 <CAFwQ8rcYevFK8tayQ-M7J0ogzdHfuZ4=Jd5qxv4pwexPVcYOww@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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