Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 2D5EF9F9C9E for ; Tue, 5 Dec 2006 11:27:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 25098-01 for ; Tue, 5 Dec 2006 11:26:54 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.aeccom.com (port-83-236-156-26.static.qsc.de [83.236.156.26]) by postgresql.org (Postfix) with ESMTP id 7ECBD9F938C for ; Tue, 5 Dec 2006 11:27:00 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.aeccom.com (Postfix) with ESMTP id C9A4F1CBE9 for ; Tue, 5 Dec 2006 16:26:58 +0100 (CET) Received: from mail.aeccom.com ([127.0.0.1]) by localhost (gate6.aeccom.com [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 04139-01-6 for ; Tue, 5 Dec 2006 16:26:58 +0100 (CET) Received: from [192.168.2.140] (andes.core.aeccom.com [192.168.2.140]) by mail.aeccom.com (Postfix) with ESMTP id 138E41CBE8 for ; Tue, 5 Dec 2006 16:26:58 +0100 (CET) Message-ID: <45758FBF.2030404@aeccom.com> Date: Tue, 05 Dec 2006 16:26:55 +0100 From: Sven Geisler Organization: AEC/communications GmbH User-Agent: Thunderbird 1.5.0.8 (Windows/20061025) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: single transaction vs multiple transactions X-Enigmail-Version: 0.94.1.2 Content-Type: text/plain; charset=ISO-8859-15 Content-Transfer-Encoding: 7bit X-Virus-Scanned: amavisd-new at aeccom.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.093 tagged_above=0 required=5 tests=AWL, BAYES_40, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200612/77 X-Sequence-Number: 22133 Hi, I have to refactoring a 'DELETE FROM x WHERE y IN (...)' because IN got to much parameters. => 'stack depth limit exceeded' I don't want to increase just the parameter for max_stack_depth. It is better to refactoring because the number of arguments to IN may increase in the future. My approach is to do multiple 'DELETE FROM x WHERE y=...'. My question is now, what is better for PostgreSQL from a performance perspective? 1. all multiple deletes in one transaction 2. each delete in its own transaction The number of arguments is around 10,000. BTW: The arguments are generate in the application tier. I would have to create a temporary table which I can use in 'DELETE FROM x WHERE y IN (SELECT z FROM tmp)'. Cheers Sven