To: Andreas Gaab <A.Gaab@scanlab.de>
cc: pgsql sql <pgsql-sql@postgresql.org>,
	=?iso-8859-1?Q?Hans_Joachim_M=FCnzer?= <H.Muenzer@scanlab.de>
Subject: Re: unnesting of array of different size explodes memory 
In-reply-to: <48DA836F3865C54B8FBF424A3B775AF667239FFDEF@Exchange-Server> 
References: <48DA836F3865C54B8FBF424A3B775AF667239FFDEF@Exchange-Server>
Comments: In-reply-to Andreas Gaab <A.Gaab@scanlab.de>
	message dated "Wed, 13 Apr 2011 11:15:38 +0200"
Date: Wed, 13 Apr 2011 10:52:32 -0400
Message-ID: <4785.1302706352@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Andreas Gaab <A.Gaab@scanlab.de> writes:
> As I now understand, the following query leads to 12 results, not just 4 (or 3...):

> SELECT unnest(ARRAY[1,2,3]), unnest(ARRAY[4,5,6,7]);

> Why could postgres use as much memory till the kernel complained when unnesting 1200 and 1300 elements resulting in 1.6e6 rows. Are there settings to prevent this such as "work_mem"?

Multiple SRFs in a targetlist are a good thing to avoid.  The behavior
is ... um ... peculiar, and the fact that we can't reclaim memory
partway through is really the least of the problems with it.

Try doing it like this instead:

SELECT * from unnest(ARRAY[1,2,3]) a, unnest(ARRAY[4,5,6,7]) b;

This has saner behavior and is less likely to leak memory.  Not to
mention less likely to be deprecated or de-implemented altogether
in the far future.

			regards, tom lane