Re: [BUGS] BUG #8573: int4range memory consumption

The following bug has been logged on the website:

Bug reference: 8573
Logged by: Godfried Vanluffelen
Email address: g(dot)vanluffelen(at)qipc(dot)com
PostgreSQL version: 9.3.1
Operating system: Windows 7 64 bit
Description:

int4range ( and any other range function) consumes much memory when used in
a select statement on a big table.

steps to reproduce:
-generate or use a table with 10M+ rows with 2 columns that are usable to
create a range. For example 1 column with negative numbers, and one with
positive numbers.

-invoke a simple select statement where you create a range of 2 columns. For
example select int4range(col1,col2) from table.

-memory builds up until the query is finished, or when out of memory error
occurs.

g(dot)vanluffelen(at)qipc(dot)com writes:
> int4range ( and any other range function) consumes much memory when used in
> a select statement on a big table.

The problem is that range_out leaks memory, as a consequence of creating a
number of intermediate strings that it doesn't bother to free. I don't
believe it's the only output function that leaks memory, but it does
so with particular vim: now that we've increased the initial size of
StringInfo buffers, it's probably wasting upwards of 2K per call.

While we could doubtless hack range_out to release those strings again,
it seems to me that that's just sticking a finger in the dike. I'm
inclined to think that we really ought to solve this class of problems
once and for all by fixing printtup.c to run the output functions in a
temporary memory context, which we could reset once per row to recover all
memory used. That would also let us get rid of the inadequate kluges that
printtup currently uses to try to minimize leakage, namely forced
detoasting of varlena fields and forced pfree'ing of the strings returned
by output functions. (There is no other context in which we imagine that
it's safe to pfree a function's result, and there are a number of
datatypes for which it'd make sense to return constant strings, were it
not for this kluge.)

It's possible that this would result in some net slowdown in tuple output;
but it's also possible that eliminating the retail pfree's in favor of a
single context reset per tuple would make for a net savings. In any case,
we're already using a reset-per-row approach to memory management of
output function calls in COPY OUT, and I know for a fact that we've
squeezed that code path as hard as we could.

Thoughts?

regards, tom lane

On Nov 1, 2013, at 2:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> g(dot)vanluffelen(at)qipc(dot)com writes:
>> int4range ( and any other range function) consumes much memory when used in
>> a select statement on a big table.
>
> The problem is that range_out leaks memory, as a consequence of creating a
> number of intermediate strings that it doesn't bother to free. I don't
> believe it's the only output function that leaks memory, but it does
> so with particular vim: now that we've increased the initial size of
> StringInfo buffers, it's probably wasting upwards of 2K per call.
>
> While we could doubtless hack range_out to release those strings again,
> it seems to me that that's just sticking a finger in the dike. I'm
> inclined to think that we really ought to solve this class of problems
> once and for all by fixing printtup.c to run the output functions in a
> temporary memory context,
...
> we're already using a reset-per-row approach to memory management of
> output function calls in COPY OUT, and I know for a fact that we've
> squeezed that code path as hard as we could.

+1. COPY is actually the case I was worried about… if you're dealing with large amounts of data in other clients ISTM that other things will bottleneck before the extra memory context.

I wrote:
> It's possible that this would result in some net slowdown in tuple output;
> but it's also possible that eliminating the retail pfree's in favor of a
> single context reset per tuple would make for a net savings. In any case,
> we're already using a reset-per-row approach to memory management of
> output function calls in COPY OUT, and I know for a fact that we've
> squeezed that code path as hard as we could.

It appears that indeed, the reset-per-row approach is marginally faster
than the existing code. It's just barely faster with a couple of columns
of output, for instance I get about 660 vs 665 msec for
select x,x from generate_series(1,1000000) x;
but the advantage grows for more columns, which is expected since we're
getting rid of more pfree's. With ten integer columns I see 1650 vs
1710 msec, for example.

So I see no downside to fixing it like this, and will work on a complete
patch.

regards, tom lane

Jim Nasby <decibel(at)decibel(dot)org> writes:
> On Nov 1, 2013, at 2:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> While we could doubtless hack range_out to release those strings again,
>> it seems to me that that's just sticking a finger in the dike. I'm
>> inclined to think that we really ought to solve this class of problems
>> once and for all by fixing printtup.c to run the output functions in a
>> temporary memory context,
>> ...
>> we're already using a reset-per-row approach to memory management of
>> output function calls in COPY OUT, and I know for a fact that we've
>> squeezed that code path as hard as we could.

> +1. COPY is actually the case I was worried about if you're dealing with large amounts of data in other clients ISTM that other things will bottleneck before the extra memory context.

Attached is a proposed patch for this. It fixes most of the functions
in printtup.c to use a per-row memory context. (I did not bother to
fix debugtup(), which is used only in standalone mode. If you're doing
queries large enough for mem leaks to be problematic in standalone mode,
you're nuts.) I also simplified some other places that had copied the
notion of forced detoasting before an output function call, as that seems
dubious at best, and wasn't being done uniformly anyway.

My original thought had been to get rid of all pfree's of output function
results, so as to make the world safe for returning constant strings when
such functions find it appropriate. However, I ran into a showstopper
problem: SPI_getvalue(), which is little more than a wrapper around the
appropriate type output function, is documented as returning a pfree'able
string. Some though not all of the callers in core and contrib take the
hint and pfree the result, and I'm sure there are more in third-party
extensions. So if we want to allow output functions to return
non-palloc'd strings, it seems we have to either change SPI_getvalue()'s
API contract or insert a pstrdup() call in it. Neither of these is
attractive, mainly because the vast majority of output function results
would still be palloc'd even if we made aggressive use of the option not
to. That means that if we did the former, light testing wouldn't
necessarily show a problem if someone forgot to remove a pfree() after a
SPI_getvalue() call, and it also means that if we did the latter, the
pstrdup() would usually be a waste of cycles and space.

So I've abandoned that idea for now, and just recommend applying the
attached patch as far back as 9.2, where range_out was added.

Comments?

regards, tom lane

On 2013-11-02 15:29:36 -0400, Tom Lane wrote:
> Attached is a proposed patch for this. It fixes most of the functions
> in printtup.c to use a per-row memory context. (I did not bother to
> fix debugtup(), which is used only in standalone mode. If you're doing
> queries large enough for mem leaks to be problematic in standalone mode,
> you're nuts.)

FWIW, by that definition I have been nuts several time in the past -
without much choice since I was recovering data from a corrupted cluster
and the database couldn't be started normally. This now has gotten worse
(even in the backbranches) since debugtup won't clean up detoasted data
anymore.
But I guess the solution for that is to use COPY in those situations
which shouldn't have that problem and should work. Also, easier to parse ;)

> My original thought had been to get rid of all pfree's of output function
> results, so as to make the world safe for returning constant strings when
> such functions find it appropriate. However, I ran into a showstopper
> problem: SPI_getvalue(), which is little more than a wrapper around the
> appropriate type output function, is documented as returning a pfree'able
> string. Some though not all of the callers in core and contrib take the
> hint and pfree the result, and I'm sure there are more in third-party
> extensions. So if we want to allow output functions to return
> non-palloc'd strings, it seems we have to either change SPI_getvalue()'s
> API contract or insert a pstrdup() call in it. Neither of these is
> attractive, mainly because the vast majority of output function results
> would still be palloc'd even if we made aggressive use of the option not
> to. That means that if we did the former, light testing wouldn't
> necessarily show a problem if someone forgot to remove a pfree() after a
> SPI_getvalue() call, and it also means that if we did the latter, the
> pstrdup() would usually be a waste of cycles and space.

I guess one option for the future is to to pstrdup() in SPI_getvalue()
and adding a new function that tells whether the result can be freed or
not. Then callers that care can move over. Although I'd guess that many
of those caring will already use SPI_getbinval() manually.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2013-11-02 15:29:36 -0400, Tom Lane wrote:
>> Attached is a proposed patch for this. It fixes most of the functions
>> in printtup.c to use a per-row memory context. (I did not bother to
>> fix debugtup(), which is used only in standalone mode. If you're doing
>> queries large enough for mem leaks to be problematic in standalone mode,
>> you're nuts.)

> FWIW, by that definition I have been nuts several time in the past -
> without much choice since I was recovering data from a corrupted cluster
> and the database couldn't be started normally. This now has gotten worse
> (even in the backbranches) since debugtup won't clean up detoasted data
> anymore.
> But I guess the solution for that is to use COPY in those situations
> which shouldn't have that problem and should work. Also, easier to parse ;)

Considering the output from debugtup goes to stdout where it will be
intermixed with prompts etc, I'd have to think that COPY is a way better
solution for dealing with bulk data.

Really I'd like to see standalone mode, in its current form, go away
completely. I had a prototype patch for allowing psql and other clients
to interface to a standalone backend. I think getting that finished would
be a way more productive use of time than improving debugtup.

regards, tom lane

On 2013-11-04 09:45:22 -0500, Tom Lane wrote:
> Really I'd like to see standalone mode, in its current form, go away
> completely. I had a prototype patch for allowing psql and other clients
> to interface to a standalone backend. I think getting that finished would
> be a way more productive use of time than improving debugtup.

+many

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Mon, Nov 4, 2013 at 8:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
>> On 2013-11-02 15:29:36 -0400, Tom Lane wrote:
>>> Attached is a proposed patch for this. It fixes most of the functions
>>> in printtup.c to use a per-row memory context. (I did not bother to
>>> fix debugtup(), which is used only in standalone mode. If you're doing
>>> queries large enough for mem leaks to be problematic in standalone mode,
>>> you're nuts.)
>
>> FWIW, by that definition I have been nuts several time in the past -
>> without much choice since I was recovering data from a corrupted cluster
>> and the database couldn't be started normally. This now has gotten worse
>> (even in the backbranches) since debugtup won't clean up detoasted data
>> anymore.
>> But I guess the solution for that is to use COPY in those situations
>> which shouldn't have that problem and should work. Also, easier to parse ;)
>
> Considering the output from debugtup goes to stdout where it will be
> intermixed with prompts etc, I'd have to think that COPY is a way better
> solution for dealing with bulk data.
>
> Really I'd like to see standalone mode, in its current form, go away
> completely. I had a prototype patch for allowing psql and other clients
> to interface to a standalone backend. I think getting that finished would
> be a way more productive use of time than improving debugtup.

The last patch including Windows implementation was posted at below
link sometime back.
http://www.postgresql.org/message-id/6C0B27F7206C9E4CA54AE035729E9C382853263C@szxeml509-mbs

If this is the right thing, I can rebase the patch and make it ready.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> writes:
> On Mon, Nov 4, 2013 at 8:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Really I'd like to see standalone mode, in its current form, go away
>> completely. I had a prototype patch for allowing psql and other clients
>> to interface to a standalone backend. I think getting that finished would
>> be a way more productive use of time than improving debugtup.

> The last patch including Windows implementation was posted at below
> link sometime back.
> http://www.postgresql.org/message-id/6C0B27F7206C9E4CA54AE035729E9C382853263C@szxeml509-mbs

> If this is the right thing, I can rebase the patch and make it ready.

IIRC, the discussion stalled out because people had security concerns
and/or there wasn't consensus about how it should look at the user level.
There's probably not much point in polishing a patch until we have more
agreement about the high-level design.

regards, tom lane

On Tue, Nov 5, 2013 at 12:52 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> writes:
>> On Mon, Nov 4, 2013 at 8:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Really I'd like to see standalone mode, in its current form, go away
>>> completely. I had a prototype patch for allowing psql and other clients
>>> to interface to a standalone backend. I think getting that finished would
>>> be a way more productive use of time than improving debugtup.
>
>> The last patch including Windows implementation was posted at below
>> link sometime back.
>> http://www.postgresql.org/message-id/6C0B27F7206C9E4CA54AE035729E9C382853263C@szxeml509-mbs
>
>> If this is the right thing, I can rebase the patch and make it ready.
>
> IIRC, the discussion stalled out because people had security concerns
> and/or there wasn't consensus about how it should look at the user level.
> There's probably not much point in polishing a patch until we have more
> agreement about the high-level design.

Okay. However +1 for working on that idea as lot of people have shown
interest in it.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Mon, Nov 4, 2013 at 10:51 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Tue, Nov 5, 2013 at 12:52 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> writes:
>>> On Mon, Nov 4, 2013 at 8:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>> Really I'd like to see standalone mode, in its current form, go away
>>>> completely. I had a prototype patch for allowing psql and other clients
>>>> to interface to a standalone backend. I think getting that finished would
>>>> be a way more productive use of time than improving debugtup.
>>
>>> The last patch including Windows implementation was posted at below
>>> link sometime back.
>>> http://www.postgresql.org/message-id/6C0B27F7206C9E4CA54AE035729E9C382853263C@szxeml509-mbs
>>
>>> If this is the right thing, I can rebase the patch and make it ready.
>>
>> IIRC, the discussion stalled out because people had security concerns
>> and/or there wasn't consensus about how it should look at the user level.
>> There's probably not much point in polishing a patch until we have more
>> agreement about the high-level design.
>
> Okay. However +1 for working on that idea as lot of people have shown
> interest in it.

Agreed. I remember the sticking point as being mostly, like, whether
we should just make it work, or whether we had to do a huge amount of
additional work to turn it into something quite different from what
you had in mind. That question answers itself.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company