Re: single transaction vs multiple transactions

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=tmp.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(at)aeccom(dot)com>
wrote:

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

--
**
Dipl.-Designer Jens Schipkowski
APUS Software GmbH