single transaction vs multiple transactions

From: Sven Geisler <sgeisler(at)aeccom(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: single transaction vs multiple transactions
Date: 2006-12-05 15:26:55
Message-ID: 45758FBF.2030404@aeccom.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Heikki Linnakangas 2006-12-05 15:30:43 Re: single transaction vs multiple transactions
Previous Message Michael Stone 2006-12-05 14:49:41 Re: Bad iostat numbers