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

On Fri, Oct 6, 2017 at 8:16 AM, Andres Freund <andres@anarazel.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

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs