I just noticed that if you execute the same DO command over and over
within a session, it gets slower and slower. And if you keep it up
you'll notice the backend's RAM consumption bloating too. The cause
appears to be that we leak the cached plans created for any SQL
statements or expressions within the DO command --- the next iteration
won't reuse those, but rather create its own set. Probably ought to
look into releasing those when the DO block is over.

regards, tom lane

Search Discussions

  • Robert Haas at Mar 27, 2011 at 3:02 am

    On Tue, Jan 18, 2011 at 1:46 PM, Tom Lane wrote:
    I just noticed that if you execute the same DO command over and over
    within a session, it gets slower and slower.  And if you keep it up
    you'll notice the backend's RAM consumption bloating too.  The cause
    appears to be that we leak the cached plans created for any SQL
    statements or expressions within the DO command --- the next iteration
    won't reuse those, but rather create its own set.  Probably ought to
    look into releasing those when the DO block is over.
    Should we try to do something about this?

    I don't really understand what's going on here. I thought maybe the
    problem was that the inline handler was getting called with
    TopMemoryContext active, but it's not. It's getting called with the
    PortalHeapMemory context active, so anything that is allocated without
    first specifying the context ought to get cleaned up at
    end-of-statement. So somewhere we're deliberately copying something
    into a longer-lived memory context where we shouldn't be. Your
    comment about cached plans got me looking at CreateCachedPlan(), which
    does this; it's called from SPI_saveplan(), which is called from
    exec_prepare_plan(). But that's immediately followed by
    SPI_freeplan(), so I'm all tapped out.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • Tom Lane at Mar 27, 2011 at 3:15 pm

    Robert Haas writes:
    On Tue, Jan 18, 2011 at 1:46 PM, Tom Lane wrote:
    I just noticed that if you execute the same DO command over and over
    within a session, it gets slower and slower.  And if you keep it up
    you'll notice the backend's RAM consumption bloating too.  The cause
    appears to be that we leak the cached plans created for any SQL
    statements or expressions within the DO command --- the next iteration
    won't reuse those, but rather create its own set.  Probably ought to
    look into releasing those when the DO block is over.
    Should we try to do something about this?
    I don't really understand what's going on here. I thought maybe the
    problem was that the inline handler was getting called with
    TopMemoryContext active, but it's not.
    No, the problem is that (a) the internal function representation
    produced by plpgsql_compile_inline is never recycled, and (b) the
    cached plans held by plancache.c are never released. When dealing
    with an ordinary function it's reasonable to hang onto those things
    on the expectation that the function will be called again in the
    session. But for an inline code block it's leakage, plain and simple.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJan 18, '11 at 6:46p
activeMar 27, '11 at 3:15p
posts3
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Tom Lane: 2 posts Robert Haas: 1 post

People

Translate

site design / logo © 2022 Grokbase