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

Thanks, I'm assuming that's the same error as I'm getting. (Where do you
get the memory dump logs from exactly? It just took me 45 minutes to find
the main postgres logs...)

On Thu, Oct 5, 2017 at 9:00 PM, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
wrote:

> 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
>