Re: BUG #14843: CREATE TABLE churns through all memory, crashes db

On Fri, Oct 6, 2017 at 8:16 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> Could you either try to form a reproducible demonstration of the problem
> out of this, or get a memory context dump? If you disable the kernel's
> overcommit heuristics, your computer won't crash on OOM, instead
> postgres will get an error, and log a dump that shows where memory is
> used.

It is not really complicated to reproduce the problem. Just insert the
JSON data attached into a table, multiply it and then run the query
given in the report. I could easily get out an OOM error:
ERROR: 53200: out of memory
DETAIL: Failed on request of size 1085.
LOCATION: palloc, mcxt.c:868
And a dump:
TopMemoryContext: 161440 total in 7 blocks; 66016 free (5 chunks); 95424 used
Record information cache: 8192 total in 1 blocks; 1760 free (0
chunks); 6432 used
TableSpace cache: 8192 total in 1 blocks; 2272 free (0 chunks); 5920 used
Operator lookup cache: 24576 total in 2 blocks; 10936 free (4
chunks); 13640 used
pgstat TabStatusArray lookup hash table: 8192 total in 1 blocks;
1632 free (0 chunks); 6560 used
TopTransactionContext: 8192 total in 1 blocks; 7944 free (1 chunks); 248 used
MessageContext: 65536 total in 4 blocks; 29208 free (3 chunks); 36328 used
Operator class cache: 8192 total in 1 blocks; 736 free (0 chunks); 7456 used
smgr relation table: 24576 total in 2 blocks; 12960 free (3 chunks);
11616 used
TransactionAbortContext: 32768 total in 1 blocks; 32728 free (0
chunks); 40 used
Portal hash: 8192 total in 1 blocks; 736 free (0 chunks); 7456 used
PortalMemory: 8192 total in 1 blocks; 7872 free (0 chunks); 320 used
PortalHeapMemory: 1024 total in 1 blocks; 800 free (0 chunks); 224 used
ExecutorState: 62906368 total in 17 blocks; 13736 free (35
chunks); 62892632 used
printtup: 8192 total in 1 blocks; 8152 free (0 chunks); 40 used
ExprContext: 8192 total in 1 blocks; 7152 free (0 chunks); 1040 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 24576 total in 2 blocks; 11840 free (3 chunks); 12736 used
CacheMemoryContext: 516096 total in 6 blocks; 23264 free (1 chunks);
492832 used
pg_statistic_ext_relid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_db_role_setting_databaseid_rol_index: 1024 total in 1 blocks;
256 free (0 chunks); 768 used
pg_user_mapping_user_server_index: 1024 total in 1 blocks; 256
free (0 chunks); 768 used
pg_user_mapping_oid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_type_oid_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 200 free (0
chunks); 824 used
pg_ts_template_oid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_ts_template_tmplname_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_ts_parser_oid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_ts_parser_prsname_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_ts_dict_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_ts_dict_dictname_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_ts_config_oid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_ts_config_cfgname_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_ts_config_map_index: 3072 total in 2 blocks; 1968 free (0
chunks); 1104 used
pg_transform_type_lang_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_transform_oid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_tablespace_oid_index: 1024 total in 1 blocks; 336 free (0
chunks); 688 used
pg_subscription_rel_srrelid_srsubid_index: 1024 total in 1 blocks;
256 free (0 chunks); 768 used
pg_subscription_oid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_subscription_subname_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_statistic_relid_att_inh_index: 3072 total in 2 blocks; 1912
free (0 chunks); 1160 used
pg_statistic_ext_oid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_statistic_ext_name_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_sequence_seqrelid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_replication_origin_roname_index: 1024 total in 1 blocks; 392
free (0 chunks); 632 used
pg_replication_origin_roiident_index: 1024 total in 1 blocks; 392
free (0 chunks); 632 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 200 free (0
chunks); 824 used
pg_range_rngtypid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_publication_rel_prrelid_prpubid_index: 1024 total in 1 blocks;
256 free (0 chunks); 768 used
pg_publication_rel_oid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_publication_oid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_publication_pubname_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_proc_oid_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
pg_proc_proname_args_nsp_index: 3072 total in 2 blocks; 1912 free
(0 chunks); 1160 used
pg_partitioned_table_partrelid_index: 1024 total in 1 blocks; 392
free (0 chunks); 632 used
pg_opfamily_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_opfamily_am_name_nsp_index: 3072 total in 2 blocks; 1968 free
(0 chunks); 1104 used
pg_operator_oid_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
pg_operator_oprname_l_r_n_index: 3072 total in 2 blocks; 1912 free
(0 chunks); 1160 used
pg_namespace_oid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 336 free (0
chunks); 688 used
pg_language_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_language_name_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_foreign_table_relid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_foreign_server_oid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_foreign_server_name_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_foreign_data_wrapper_oid_index: 1024 total in 1 blocks; 392
free (0 chunks); 632 used
pg_foreign_data_wrapper_name_index: 1024 total in 1 blocks; 392
free (0 chunks); 632 used
pg_event_trigger_oid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_event_trigger_evtname_index: 1024 total in 1 blocks; 392 free
(0 chunks); 632 used
pg_enum_typid_label_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_enum_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_default_acl_role_nsp_obj_index: 3072 total in 2 blocks; 1968
free (0 chunks); 1104 used
pg_conversion_oid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_constraint_oid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_conversion_default_index: 3072 total in 2 blocks; 1968 free (0
chunks); 1104 used
pg_collation_oid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_collation_name_enc_nsp_index: 3072 total in 2 blocks; 1968 free
(0 chunks); 1104 used
pg_opclass_am_name_nsp_index: 3072 total in 2 blocks; 1968 free (0
chunks); 1104 used
pg_cast_source_target_index: 1024 total in 1 blocks; 200 free (0
chunks); 824 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 256
free (0 chunks); 768 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 256 free
(0 chunks); 768 used
pg_amop_fam_strat_index: 3072 total in 2 blocks; 1968 free (0
chunks); 1104 used
pg_amop_opr_fam_index: 3072 total in 2 blocks; 1968 free (0
chunks); 1104 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_shseclabel_object_index: 3072 total in 2 blocks; 1968 free (0
chunks); 1104 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 256
free (0 chunks); 768 used
pg_authid_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_authid_rolname_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_database_oid_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
pg_database_datname_index: 1024 total in 1 blocks; 392 free (0
chunks); 632 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 256 free
(0 chunks); 768 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_amproc_fam_proc_index: 3072 total in 2 blocks; 1912 free (0
chunks); 1160 used
pg_opclass_oid_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 336 free (0
chunks); 688 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 200 free
(0 chunks); 824 used
pg_class_oid_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
WAL record construction: 49776 total in 2 blocks; 6568 free (0
chunks); 43208 used
PrivateRefCount: 8192 total in 1 blocks; 2808 free (0 chunks); 5384 used
MdSmgr: 8192 total in 1 blocks; 7024 free (0 chunks); 1168 used
LOCALLOCK hash: 8192 total in 1 blocks; 736 free (0 chunks); 7456 used
Timezones: 104128 total in 2 blocks; 2808 free (0 chunks); 101320 used
ErrorContext: 8192 total in 1 blocks; 8152 free (3 chunks); 40 used
Grand total: 64125904 bytes in 152 blocks; 315744 free (58 chunks);
63810160 used

This is suspicious:
ExecutorState: 62906368 total in 17 blocks; 13736 free (35
chunks); 62892632 used
--
Michael