BUG #8573: int4range memory consumption

g.vanluffelen@qipc.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Nov 1, 2013, at 2:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

g.vanluffelen@qipc.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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Jim Nasby <decibel@decibel.org> writes:

On Nov 1, 2013, at 2:08 PM, Tom Lane <tgl@sss.pgh.pa.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andres Freund <andres@2ndquadrant.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Nov 4, 2013 at 8:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Andres Freund <andres@2ndquadrant.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.
/messages/by-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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Amit Kapila <amit.kapila16@gmail.com> writes:

On Mon, Nov 4, 2013 at 8:15 PM, Tom Lane <tgl@sss.pgh.pa.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.
/messages/by-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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Nov 5, 2013 at 12:52 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Amit Kapila <amit.kapila16@gmail.com> writes:

On Mon, Nov 4, 2013 at 8:15 PM, Tom Lane <tgl@sss.pgh.pa.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.
/messages/by-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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Nov 4, 2013 at 10:51 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Tue, Nov 5, 2013 at 12:52 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Amit Kapila <amit.kapila16@gmail.com> writes:

On Mon, Nov 4, 2013 at 8:15 PM, Tom Lane <tgl@sss.pgh.pa.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.
/messages/by-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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers