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