Date: Tue, 05 Dec 2006 16:45:25 +0100
To: "Sven Geisler" <sgeisler@aeccom.com>, pgsql-performance@postgresql.org
Subject: Re: single transaction vs multiple transactions
From: "Jens Schipkowski" <jens.schipkowski@apus.co.at>
Organization: APUS Software GmbH
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <45758FBF.2030404@aeccom.com>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.tj3fdzu081rjf6@xjens.apus.local>
In-Reply-To: <45758FBF.2030404@aeccom.com>
User-Agent: Opera Mail/9.02 (Linux)

Hi,

PostgreSQL offers some proprietary SQL parameters and commands which  =

easily solves such problems.
If you are sure PostgreSQL is the DB for your app forever ;) , you could=
  =

use this parameters and commands.

Here a possible resolution for your problem.
DELETE FROM x USING tmp WHERE x.y=3Dtmp.z;

Please read the extensive documentation of PostgreSQL first, before  =

posting.

CU,
Jens

On Tue, 05 Dec 2006 16:26:55 +0100, Sven Geisler <sgeisler@aeccom.com>  =

wrote:

> Hi,
>
> I have to refactoring a 'DELETE FROM x WHERE y IN (...)' because IN go=
t
> to much parameters. =3D> '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 increa=
se
> in the future.
>
> My approach is to do multiple 'DELETE FROM x WHERE y=3D...'.
>
> 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
>
> ---------------------------(end of broadcast)-------------------------=
--
> TIP 2: Don't 'kill -9' the postmaster


-- =

**
Dipl.-Designer Jens Schipkowski
APUS Software GmbH