The memory usage is positively correlated with the number of partition tables when pg_get_expr is called.

Unreasonable memory usage.

# SUMMARY INFO

OS: Red Hat Enterprise Linux 7.6

PostgreSQL version: 19devel

[yz(at)bogon postgresql]$ psql postgres
psql (19devel)
Type "help" for help.

postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 19devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.3.1 20200408 (Red Hat 9.3.1-2), 64-bit
(1 row)

# TEST CASE

1. Modify postgresql.conf, and set GUCs:

```
max_locks_per_transaction = 2048
shared_buffers = '10GB'
```

2. Restart PostgreSQL

Make the parameter modification take effect.

3. Create database

```
CREATE DATABASE mem;
```

4. Create 500 partition tables (it is recommended to create them in segments), with each partitioned table having 200 sub-partitions.

```
[yz(at)bogon postgresql]$ psql mem
psql (19devel)
Type "help" for help.

mem=#

DO $$
DECLARE
part_start bigint := 1;
part_end bigint := 500;
part_curr bigint := part_start;

sub_start bigint := 1;
sub_end bigint := 200;
sub_curr bigint := sub_start;
BEGIN
WHILE part_curr < part_end LOOP
EXECUTE format('
CREATE TABLE sales%s(
id int,
sale_price bigint,
product_name text
)partition by range (sale_price)', part_curr
);

sub_curr := sub_start;
WHILE sub_curr < sub_end LOOP
EXECUTE format('
CREATE TABLE sales%s_%s PARTITION OF sales%s
FOR VALUES FROM (%s) TO (%s)',
part_curr,
sub_curr,
part_curr,
sub_curr,
sub_curr + 1
);
sub_curr := sub_curr + 1;
END LOOP;
      part_curr := part_curr + 1;
END LOOP;
END $$;
```

After successful creation, exit the psql connection.

5. Call pg_get_expr

```
[yz(at)bogon postgresql]$ psql mem
psql (19devel)
Type "help" for help.

mem=# SELECT pg_get_expr(relpartbound, oid) FROM pg_class WHERE relname like 'sales%';
```

Wait for the query to complete.

6. Check the query memory usage in Step 5

Find the PID corresponding to the query:

```
[yz(at)bogon ~]$ ps ux
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
yz 1739 0.0 1.8 5619732 144672 ? Ss 07:22 0:01 /home/yz/postgresql/pg_bin/bin/postgres -D pg_data
yz 1740 0.0 0.0 5619868 3704 ? Ss 07:22 0:00 postgres: io worker 1
yz 1741 0.0 0.0 5619868 6580 ? Ss 07:22 0:00 postgres: io worker 0
yz 1742 0.0 0.0 5619868 2924 ? Ss 07:22 0:00 postgres: io worker 2
yz 1743 0.8 12.1 5661680 971236 ? Ds 07:22 3:42 postgres: checkpointer
yz 1744 0.0 1.0 5620820 87232 ? Ss 07:22 0:11 postgres: background writer
yz 1746 0.1 0.2 5619868 18212 ? Ss 07:22 0:29 postgres: walwriter
yz 1747 0.0 0.0 5658196 3408 ? Ss 07:22 0:01 postgres: autovacuum launcher
yz 1748 0.0 0.0 5621196 3436 ? Ss 07:22 0:00 postgres: logical replication launcher
yz 30192 1.4 0.0 124644 3636 pts/0 S+ 14:31 0:00 psql mem
yz 30193 21.3 10.5 5942276 841712 ? Ss 14:31 0:10 postgres: yz mem [local] idle
```

Print the memory count of CacheMemoryContext using gdb:

```
[yz(at)bogon ~]$ gdb -p 30193
...
(gdb) p *CacheMemoryContext
$1 = {type = T_AllocSetContext, isReset = false, allowInCritSection = false, mem_allocated = 286261312, methods = 0xecbff0 <mcxt_methods+240>, parent = 0x2b2af50,
firstchild = 0x2b5a3d0, prevchild = 0x2b8f2c0, nextchild = 0x2b8b2a0, name = 0xea853f "CacheMemoryContext", ident = 0x0, reset_cbs = 0x0}
```

It can be seen that the value of mem_allocated in CacheMemoryContext is approximately 280MB.

When the number of partition tables or sub-partitions increases, the memory usage also increases.

Yours Sincerely,
Yang Zhu