Re: Replacing Cursors with Temporary Tables

On Thu, Apr 22, 2010 at 10:11 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> The timings are similar, but the array returning case:
> *)  runs in a single statement.  If this is executed from the client
> that means less round trips
> *) can be passed around as a variable between functions.  temp table
> requires re-query
> *) make some things easier/cheap such as counting the array -- you get
> to call the basically free array_upper()
> *) makes some things harder.  specifically dealing with arrays on the
> client is a pain UNLESS you expand the array w/unnest() or use
> libpqtypes
> *) can nest. you can trivially nest complicated sets w/arrays
> *) does not require explicit transaction mgmt


I neglected to mention perhaps the most important point about the array method:
*) does not rely on any temporary resources.

If you write a lot of plpsql, you will start to appreciate the
difference in execution time between planned and unplanned functions.
The first time you run a function in a database session, it has to be
parsed and planned.  The planning time in particular for large-ish
functions that touch a lot of objects can exceed the execution time of
the function.  Depending on _any_ temporary resources causes plan mgmt
issues because the database detects that a table in the old plan is
gone ('on commit drop') and has to re-plan.   If your functions are
complex/long and you are counting milliseconds, then that alone should
be enough to dump any approach that depends on temp tables.

merlin