RE: Creating foreign key on partitioned table is too slow

On Wednesday, August 5, 2020 9:43 AM I wrote:
> I'll report the result before the end of August .

I test v2-0001-build-partdesc-memcxt.patch at 9a9db08ae4 and it is ok.

Firstly, I execute ALTER TABLE ADD CONSTRAINT FOREIGN KEY on the table which has 8k tables.
This query execution completes in about 22 hours without OOM.

Secondary, I confirm the reduction of memory context usage.
Running with 8k partitions takes too long, I confirm with 1k partitions.
I use gdb and call MemoryContextStats(TopMemoryContext) at addFkRecurseReferencing().

CacheMemoryContext size becomes small, so I think it is working as expected.
The Results are as follows.

- before applying patch

TopMemoryContext: 418896 total in 18 blocks; 91488 free (13 chunks); 327408 used
pgstat TabStatusArray lookup hash table: 65536 total in 4 blocks; 16808 free (7 chunks); 48728 used
TopTransactionContext: 4194304 total in 10 blocks; 1045728 free (18 chunks); 3148576 used
TableSpace cache: 8192 total in 1 blocks; 2048 free (0 chunks); 6144 used
Type information cache: 24624 total in 2 blocks; 2584 free (0 chunks); 22040 used
Operator lookup cache: 24576 total in 2 blocks; 10712 free (4 chunks); 13864 used
RowDescriptionContext: 8192 total in 1 blocks; 6880 free (0 chunks); 1312 used
MessageContext: 8192 total in 1 blocks; 3064 free (0 chunks); 5128 used
Operator class cache: 8192 total in 1 blocks; 512 free (0 chunks); 7680 used
smgr relation table: 32768 total in 3 blocks; 16768 free (8 chunks); 16000 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 512 free (0 chunks); 7680 used
TopPortalContext: 8192 total in 1 blocks; 7648 free (0 chunks); 544 used
PortalContext: 9621216 total in 1179 blocks; 13496 free (13 chunks); 9607720 used:
Relcache by OID: 16384 total in 2 blocks; 3424 free (3 chunks); 12960 used
CacheMemoryContext: 4243584 total in 12 blocks; 1349808 free (12 chunks); 2893776 used
index info: 2048 total in 2 blocks; 736 free (0 chunks); 1312 used: pg_trigger_tgconstraint_index
index info: 2048 total in 2 blocks; 736 free (0 chunks); 1312 used: pg_trigger_oid_index
index info: 2048 total in 2 blocks; 352 free (1 chunks); 1696 used: pg_inherits_relid_seqno_index
partition descriptor: 65344 total in 12 blocks; 7336 free (4 chunks); 58008 used: accounts
index info: 2048 total in 2 blocks; 736 free (0 chunks); 1312 used: pg_inherits_parent_index
partition key: 1024 total in 1 blocks; 160 free (0 chunks); 864 used: accounts
...
index info: 2048 total in 2 blocks; 736 free (2 chunks); 1312 used: pg_database_oid_index
index info: 2048 total in 2 blocks; 736 free (2 chunks); 1312 used: pg_authid_rolname_index
WAL record construction: 49776 total in 2 blocks; 6344 free (0 chunks); 43432 used
PrivateRefCount: 8192 total in 1 blocks; 2584 free (0 chunks); 5608 used
MdSmgr: 8192 total in 1 blocks; 5528 free (0 chunks); 2664 used
LOCALLOCK hash: 131072 total in 5 blocks; 26376 free (15 chunks); 104696 used
Timezones: 104128 total in 2 blocks; 2584 free (0 chunks); 101544 used
ErrorContext: 8192 total in 1 blocks; 7928 free (3 chunks); 264 used
Grand total: 19322960 bytes in 1452 blocks; 2743560 free (186 chunks); 16579400 used

- after applying patch

TopMemoryContext: 418896 total in 18 blocks; 91488 free (13 chunks); 327408 used
pgstat TabStatusArray lookup hash table: 65536 total in 4 blocks; 16808 free (7 chunks); 48728 used
TopTransactionContext: 4194304 total in 10 blocks; 1045728 free (18 chunks); 3148576 used
RowDescriptionContext: 8192 total in 1 blocks; 6880 free (0 chunks); 1312 used
MessageContext: 8192 total in 1 blocks; 3064 free (0 chunks); 5128 used
Operator class cache: 8192 total in 1 blocks; 512 free (0 chunks); 7680 used
smgr relation table: 32768 total in 3 blocks; 16768 free (8 chunks); 16000 used
TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
Portal hash: 8192 total in 1 blocks; 512 free (0 chunks); 7680 used
TopPortalContext: 8192 total in 1 blocks; 7648 free (0 chunks); 544 used
PortalContext: 9621216 total in 1179 blocks; 13496 free (13 chunks); 9607720 used:
Relcache by OID: 16384 total in 2 blocks; 3424 free (3 chunks); 12960 used
CacheMemoryContext: 2113600 total in 10 blocks; 556240 free (10 chunks); 1557360 used
index info: 2048 total in 2 blocks; 736 free (0 chunks); 1312 used: pg_trigger_tgconstraint_index
index info: 2048 total in 2 blocks; 736 free (0 chunks); 1312 used: pg_trigger_oid_index
index info: 2048 total in 2 blocks; 352 free (1 chunks); 1696 used: pg_inherits_relid_seqno_index
partition descriptor: 65344 total in 12 blocks; 7336 free (4 chunks); 58008 used: accounts
index info: 2048 total in 2 blocks; 736 free (0 chunks); 1312 used: pg_inherits_parent_index
partition key: 1024 total in 1 blocks; 160 free (0 chunks); 864 used: accounts
...
index info: 2048 total in 2 blocks; 736 free (2 chunks); 1312 used: pg_database_oid_index
index info: 2048 total in 2 blocks; 736 free (2 chunks); 1312 used: pg_authid_rolname_index
WAL record construction: 49776 total in 2 blocks; 6344 free (0 chunks); 43432 used
PrivateRefCount: 8192 total in 1 blocks; 2584 free (0 chunks); 5608 used
MdSmgr: 8192 total in 1 blocks; 6360 free (0 chunks); 1832 used
LOCALLOCK hash: 131072 total in 5 blocks; 26376 free (15 chunks); 104696 used
Timezones: 104128 total in 2 blocks; 2584 free (0 chunks); 101544 used
ErrorContext: 8192 total in 1 blocks; 7928 free (3 chunks); 264 used
Grand total: 17131488 bytes in 1441 blocks; 1936008 free (234 chunks); 15195480 used

Finally, I do make check and all tests are passed.
So, I'll change this patch status to ready for committer.

Regards,
Sho Kato