To: pgsql-bugs@lists.postgresql.org
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Subject: Large objects and out-of-memory
Message-ID: <7c57860c-fc05-143e-aa81-f11ca13ec00a@postgrespro.ru>
Date: Mon, 21 Dec 2020 19:33:42 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.2.1
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

Hi,

The following sequence of command cause backend's memory to exceed 10Gb:

CREATE DATABASE testmem;
CREATE ROLE alice WITH SUPERUSER LOGIN;
CREATE ROLE testlo WITH SUPERUSER LOGIN;
\c testmem # Подключаемся к тестовой базе
\c - alice
CREATE TABLE image1 (raster oid);
INSERT INTO image1 SELECT lo_creat(-1) FROM generate_series(1,10000000);
REASSIGN OWNED BY alice TO testlo;

This memory is occupied in top memory context by invalidation messages:

#0  AllocSetAlloc (context=0x19153a0, size=528) at aset.c:919
#1  0x0000000000b451d5 in MemoryContextAlloc (context=0x19153a0, 
size=528) at mcxt.c:809
#2  0x0000000000ae80c6 in AddInvalidationMessage (listHdr=0x19156d8, 
msg=0x7ffdca880600) at inval.c:241
#3  0x0000000000ae8473 in AddSnapshotInvalidationMessage (hdr=0x19156d0, 
dbId=0, relId=1214) at inval.c:437
#4  0x0000000000ae871c in RegisterSnapshotInvalidation (dbId=0, 
relId=1214) at inval.c:547
#5  0x0000000000ae915f in CacheInvalidateHeapTuple 
(relation=0x7f16afc35cb0, tuple=0x7ffdca8807b0, newtuple=0x198dba8) at 
inval.c:1162
#6  0x00000000004f2d41 in heap_update (relation=0x7f16afc35cb0, 
otid=0x198dbac, newtup=0x198dba8, cid=6, crosscheck=0x0, wait=true, 
tmfd=0x7ffdca880830,
     lockmode=0x7ffdca880828) at heapam.c:3729
#7  0x00000000004f3913 in simple_heap_update (relation=0x7f16afc35cb0, 
otid=0x198dbac, tup=0x198dba8) at heapam.c:3891
#8  0x00000000005c281d in CatalogTupleUpdate (heapRel=0x7f16afc35cb0, 
otid=0x198dbac, tup=0x198dba8) at indexing.c:309
#9  0x00000000005e7713 in shdepChangeDep (sdepRel=0x7f16afc35cb0, 
classid=2613, objid=11950311, objsubid=0, refclassid=1260, refobjid=32848,
     deptype=SHARED_DEPENDENCY_OWNER) at pg_shdepend.c:269
#10 0x00000000005e784c in changeDependencyOnOwner (classId=2613, 
objectId=11950311, newOwnerId=32848) at pg_shdepend.c:316
#11 0x0000000000669844 in AlterObjectOwner_internal (rel=0x7f16afc34c90, 
objectId=11950311, new_ownerId=32848) at alter.c:1050
#12 0x00000000005e9c8e in shdepReassignOwned (roleids=0x19aa138, 
newrole=32848) at pg_shdepend.c:1587
#13 0x0000000000718aa2 in ReassignOwnedObjects (stmt=0x18f1830) at 
user.c:1425
#14 0x000000000097bf7d in standard_ProcessUtility (pstmt=0x18f1b80, 
queryString=0x18f0cd0 "REASSIGN OWNED BY alice TO testlo;",
     context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, 
dest=0x18f1c70, qc=0x7ffdca881180) at utility.c:890
#15 0x000000000097b508 in ProcessUtility (pstmt=0x18f1b80, 
queryString=0x18f0cd0 "REASSIGN OWNED BY alice TO testlo;", 
context=PROCESS_UTILITY_TOPLEVEL,

As far as I understand invalidation messages  are kept until the end of 
transaction:

/*
  * CommandEndInvalidationMessages
  *        Process queued-up invalidation messages at end of one command
  *        in a transaction.
  *
  * Here, we send no messages to the shared queue, since we don't know 
yet if
  * we will commit.  We do need to locally process the CurrentCmdInvalidMsgs
  * list, so as to flush our caches of any entries we have outdated in the
  * current command.  We then move the current-cmd list over to become part
  * of the prior-cmds list.
  *
  * Note:
  *        This should be called during CommandCounterIncrement(),
  *        after we have advanced the command ID.
  */

void
CommandEndInvalidationMessages(void)
{
AppendInvalidationMessages(&transInvalInfo->PriorCmdInvalidMsgs,
&transInvalInfo->CurrentCmdInvalidMsgs);
}

So in PriorCmdInvalidMsgs we have 10 millions invalidation message chunks.
Such memory blow happens in this scenario twice: when large objects are 
created and when owner is reassigned.
It can not be considered as memory leak, but I think that it is a real 
problem which has to be fixed.
I am not so familiar with invalidation message processing, I will be 
pleae if somebody can suggest solution of the problem.

Thanks in advance,

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company