Обсуждение: out of memory with large queries

Hi,

I have a problem with large queries: I have a table with 300000 rows and
when I try the following query the backends runs out of memory:
 select upper(name) from my_table;

The following queries without funcs or with funcs of int4 work fine:
 select name from my_table; select max(id,0) from my_table;

so I suspect that the trouble is with memory allocated by functions
returning data by address, which is not released until the end of the
transaction. With more then 300000 rows you eat a lot of memory.

This means that postgres is currently unable to execute large queries
that involve functions on text fields. A pretty bad limitation IMHO.

I tried to look at the code but haven't found a way to release the
storage allocated for each tuple and the context allocation code is
not very documented.

Any suggestion?

-- 
Massimo Dal Zotto

+----------------------------------------------------------------------+
|  Massimo Dal Zotto               email: dz@cs.unitn.it               |
|  Via Marconi, 141                phone: ++39-0461534251              |
|  38057 Pergine Valsugana (TN)      www: http://www.cs.unitn.it/~dz/  |
|  Italy                             pgp: finger dz@tango.cs.unitn.it  |
+----------------------------------------------------------------------+

Massimo Dal Zotto <dz@cs.unitn.it> writes:
> This means that postgres is currently unable to execute large queries
> that involve functions on text fields. A pretty bad limitation IMHO.

Yup.  This is one of the major projects that I was proposing for 6.6.
I do not think the fix will be easy, but we need to do it.
        regards, tom lane

At 17:14 9/06/99 -0400, you wrote:
>Massimo Dal Zotto <dz@cs.unitn.it> writes:
>> This means that postgres is currently unable to execute large queries
>> that involve functions on text fields. A pretty bad limitation IMHO.
>
>Yup.  This is one of the major projects that I was proposing for 6.6.
>I do not think the fix will be easy, but we need to do it.
>

In yet another attempt to go over my head, wouldn't it be possible to pass three parameters to the aggregate state
functions,so their signatures become:
 

int (or void) = transfn1 (transtype1, basetype, *transtype1)
int (or void) = transfn2 (transtype2, *transtype2)
int (or void) = finalfn (transtype1, transtype2, *finaltype)

where the int return value *may* give status information, or may just be ignored.

This clearly breaks the ability to use the existing float8pl etc functions, but the cost is reasonably controlled: the
creationof xxxxx3 wrappers (eg. float8pl3) which take three parameters and call the original function, then release the
memory.

A better solution would be to have float8pl accept 2 or 3 parameters, and modify how it returns information based on
theparameter count, but I *presume* that this would break on many platforms/compilers (is this right?), in any case it
seemspretty nasty returning an int OR a float8*, depending on your parameters. Perhaps if the functions were modified
as:

transtype1 = transfn1 (transtype1, basetype [, *transtype1])
transtype2 = transfn2 (transtype2 [, *transtype2])
finaltype = finalfn (transtype1, transtype2 [, *finaltype])

Then they are completely source-compatible with existing functions, but will accept an optional pointer to the storage
fortheir return value (probably a local variable in the caller).
 

If it worked, then each of the aggregates could avoid memory allocation completely, which has to be a performance gain
aswell. Unfortunately, even if this worked on all platforms, I have no idea what it would do to the internals of PG.
 

Am I missing something (again?).


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: +61-03-5367 7422            |                 _________  \
Fax: +61-03-5367 7430            |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/