Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pUskh-0001st-Sb for pgsql-bugs@arkaria.postgresql.org; Wed, 22 Feb 2023 17:18:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pUskg-0005f4-Pq for pgsql-bugs@arkaria.postgresql.org; Wed, 22 Feb 2023 17:17:58 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pUsdk-0005cT-Ro for pgsql-bugs@lists.postgresql.org; Wed, 22 Feb 2023 17:10:48 +0000 Received: from mail-ed1-x532.google.com ([2a00:1450:4864:20::532]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pUsdd-0004Td-JZ for pgsql-bugs@lists.postgresql.org; Wed, 22 Feb 2023 17:10:47 +0000 Received: by mail-ed1-x532.google.com with SMTP id ee7so18195437edb.2 for ; Wed, 22 Feb 2023 09:10:41 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=deepbluecap-com.20210112.gappssmtp.com; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:cc:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=ci2NDPQuimZ7PPC42h+SE9BGgUv32TZxW0IpwpARNrk=; b=YLYCABEY5WoqyvQna6XsZzDhiFWX0anWWf1FsntbbJjJI3QRnFSlzjh8/xvqT74V9X PgWnnc5HbWNaYOavEbSU4E/6ju2CEHuBOdqioqalaP8lxgelwr7lFIFMbM/77+Sp0rFr ITeOhinAucCaJ8Sf70TAKtdbKvjk5vfTyrrSGMvzM7cMfw2jYkKrNGX7CYKiTiQki52L /tW8XRJYgXPfSBFFgtFGXvz50rGXm2WtMQv9mx6VSTwZOTEI+u47XkniBImN7wUCbRYH Zpd/npVlrjQrlbQv36or/HCe3V2sTWe3MpDV8i/2812eP1TmWYwTAUBDs5oHYBfShJQn Hn7A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:cc:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=ci2NDPQuimZ7PPC42h+SE9BGgUv32TZxW0IpwpARNrk=; b=NbhDeUWtH4i40uY5IJsMFKffAyePtX6g4pEJZwxfI24tg41hMsxP85Wt7iEZFxWcts h4j/LQDUoxpV8FvZdjMOkZzwEVPqULXc4tDChWdq1YCMOhIrzRgCMbKUg05yIfiqhIOc 1vPjZnzSV6deEYkA0C7sE4aU9sWe2Qv07+i9na04TYWB6HRADCrvIF9ZHH4Wi4Ve7/ei QZEQgnOO8VLW0iv4qrB9VucK7wcmFLm9zJk8/vianUFpvo2bxGChhBCvLaCe/lLlr4xn v3JBUEIvGSlR8DkzQLGuTJOeCJ+r5IUMuNntl8nHza2YYcA4tyi1yV7nyubJ7Bl6VQ67 zT+A== X-Gm-Message-State: AO0yUKU9bvbCouhZlJ+0jGdUOCxTCN+gQG0ky28re2cU+qgzP5Yek6oJ QO/50uRZZzMMaLvDY9lKxbuS8Xkzth3eDIzNhZzNzw== X-Google-Smtp-Source: AK7set9YfQ7XGQvRXMxN3ci4mnLq+pWJSuCS1kCYVT/qDz39bTD/MF20jlQrT1Ze9KJdFz5EBayK7A== X-Received: by 2002:a17:907:6da0:b0:8dd:5710:a017 with SMTP id sb32-20020a1709076da000b008dd5710a017mr10686402ejc.4.1677085838594; Wed, 22 Feb 2023 09:10:38 -0800 (PST) Received: from [10.101.3.114] (ip-185-104-137-32.ptr.icomera.net. [185.104.137.32]) by smtp.gmail.com with ESMTPSA id ex18-20020a170907955200b008d53ea69227sm3917464ejc.224.2023.02.22.09.10.33 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 22 Feb 2023 09:10:38 -0800 (PST) Message-ID: <5989ffab-a824-e029-3f09-fa578593023a@deepbluecap.com> Date: Wed, 22 Feb 2023 18:10:25 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.8.0 Subject: Re: Unlimited memory consumption with long-lived connection Content-Language: en-GB To: Justin Pryzby , Heikki Linnakangas Cc: pgsql-bugs@lists.postgresql.org, Andres Freund References: <7484b4bd-a808-ed9e-892c-933f7f24120f@iki.fi> <20230222145341.GT1653@telsasoft.com> From: Duncan Sands In-Reply-To: <20230222145341.GT1653@telsasoft.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi Justin, >>> PS: The testcase was reduced from a script that kept a connection open for a >>> long time in order to LISTEN, and would execute a query using the same >>> connection every time there was a notification on the channel. It consumed ever >>> more memory to the point of crashing the postgresql server. Changing the script >>> to perform the query using a new short-lived connection was an effective workaround. > > It sounds like the same as the issue here: > https://wiki.postgresql.org/wiki/PostgreSQL_13_Open_Items I agree that it is likely the same issue. > There are patches proposed here, which fixed the issue for me. > But I've always been suspicious that there may be a 2nd, undiagnosed > issue lurking behind this one... > https://www.postgresql.org/message-id/20210417021602.7dilihkdc7oblrf7%40alap3.anarazel.de Thanks. Since those patches are just a workaround, and I already worked around it, I don't plan to give them a whirl. On the other hand, if Andres ever gets round to implementing approach (1) from https://www.postgresql.org/message-id/20210417021602.7dilihkdc7oblrf7@alap3.anarazel.de then I'd be happy to give it a whirl. I'm rusty now but I used to be quite expert in LLVM, so I might even be able to do some patch review. Best wishes, Duncan.