Re: single transaction vs multiple transactions

"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
> Sven Geisler wrote:
>> I have to refactoring a 'DELETE FROM x WHERE y IN (...)' because IN got
>> to much parameters. => 'stack depth limit exceeded'
>> 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)'.

> I think that's exactly what you should do.

Also, if you're planning to update to 8.2 soon, the tradeoffs will
change completely.  8.2 should avoid the stack depth problem, and you
can get something closely approximating the plan you'd get for a join
against a temp table using VALUES:

regression=# explain select * from tenk1 where unique2 in (1,2,3,4,6,8);
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=24.01..45.79 rows=6 width=244)
   Recheck Cond: (unique2 = ANY ('{1,2,3,4,6,8}'::integer[]))
   ->  Bitmap Index Scan on tenk1_unique2  (cost=0.00..24.01 rows=6 width=0)
         Index Cond: (unique2 = ANY ('{1,2,3,4,6,8}'::integer[]))
(4 rows)

regression=# explain select * from tenk1 where unique2 in (values(1),(2),(3),(4),(6),(8));
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Nested Loop  (cost=4.10..48.34 rows=6 width=244)
   ->  HashAggregate  (cost=0.09..0.15 rows=6 width=4)
         ->  Values Scan on "*VALUES*"  (cost=0.00..0.08 rows=6 width=4)
   ->  Bitmap Heap Scan on tenk1  (cost=4.01..8.02 rows=1 width=244)
         Recheck Cond: (tenk1.unique2 = "*VALUES*".column1)
         ->  Bitmap Index Scan on tenk1_unique2  (cost=0.00..4.01 rows=1 width=0)
               Index Cond: (tenk1.unique2 = "*VALUES*".column1)
(7 rows)


            regards, tom lane