{"action":"created","issue":{"url":"https://api.github.com/repos/laurenz/oracle_fdw/issues/82","repository_url":"https://api.github.com/repos/laurenz/oracle_fdw","labels_url":"https://api.github.com/repos/laurenz/oracle_fdw/issues/82/labels{/name}","comments_url":"https://api.github.com/repos/laurenz/oracle_fdw/issues/82/comments","events_url":"https://api.github.com/repos/laurenz/oracle_fdw/issues/82/events","html_url":"https://github.com/laurenz/oracle_fdw/issues/82","id":151620884,"number":82,"title":"ANALYZE on large foreign tables fails with out-of-memory condition","user":{"login":"acheccuc","id":6928697,"avatar_url":"https://avatars.githubusercontent.com/u/6928697?v=3","gravatar_id":"","url":"https://api.github.com/users/acheccuc","html_url":"https://github.com/acheccuc","followers_url":"https://api.github.com/users/acheccuc/followers","following_url":"https://api.github.com/users/acheccuc/following{/other_user}","gists_url":"https://api.github.com/users/acheccuc/gists{/gist_id}","starred_url":"https://api.github.com/users/acheccuc/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/acheccuc/subscriptions","organizations_url":"https://api.github.com/users/acheccuc/orgs","repos_url":"https://api.github.com/users/acheccuc/repos","events_url":"https://api.github.com/users/acheccuc/events{/privacy}","received_events_url":"https://api.github.com/users/acheccuc/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/laurenz/oracle_fdw/labels/bug","name":"bug","color":"fc2929"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":8,"created_at":"2016-04-28T11:45:16Z","updated_at":"2016-05-04T11:01:24Z","closed_at":null,"body":"Dear All,\r\nwe are currently experimenting with Oracle_FDW and foreign tables on a system (wa already have some others in production) and experiencing a problem when trying to run ANALYZE against a large Oracle table. Our system specs are:\r\n\r\n- PostgreSQL 9.5.2 Linux x86_64\r\n- Oracle_FDW 1.4.0\r\n\r\nPostgreSQL is set up with the following memory parameters:\r\n\r\n- shared_buffers = 947MB\r\n- effective_cache_size = 2525MB\r\n- work_mem = 4MB\r\n- maintenance_work_mem = 64M\r\n\r\nAlthough ANALYZE works properly with small tables, when we try to run it against a ~800m records one we get an out-of memory condition pretty soon:\r\n\r\n> analyze verbose enauni.dbentry (bioseqid);\r\n> INFO:  analyzing \"enauni.dbentry\"\r\n> ERROR:  out of memory\r\n> DETAIL:  Failed on request of size 5.\r\n> CONTEXT:  converting column \"drop_entry_status\" for foreign table scan of \"dbentry\", row 27940522\r\n\r\nThis is the foreign table mapping on PostgreSQL:\r\n\r\n```\r\n                          Foreign table \"enauni.dbentry\"\r\n        Column         |              Type              | Modifiers | FDW Options \r\n-----------------------+--------------------------------+-----------+-------------\r\n bioseqid              | bigint                         | not null  | \r\n dbentryid             | bigint                         | not null  | \r\n entry_name            | character varying(100)         |           | \r\n drop_entry_status     | character varying(2)           |           | \r\n primaryacc#           | character varying(15)          | not null  | \r\n version#              | integer                        | not null  | \r\n drop_confidential     | character varying(1)           |           | \r\n dbcode                | character varying(5)           | not null  | \r\n ext_date              | timestamp(0) without time zone |           | \r\n ext_ver               | integer                        |           | \r\n first_created         | timestamp(0) without time zone | not null  | \r\n first_public          | timestamp(0) without time zone |           | \r\n hold_date             | timestamp(0) without time zone |           | \r\n project#              | smallint                       | not null  | \r\n ffdate                | timestamp(0) without time zone |           | \r\n userstamp             | character varying(30)          | not null  | \r\n timestamp             | timestamp(0) without time zone | not null  | \r\n entry_type            | smallint                       | not null  | \r\n dataclass             | character varying(4)           | not null  | \r\n qual_division         | character varying(3)           |           | \r\n statusid              | integer                        | not null  | \r\n last_public           | timestamp(0) without time zone |           | \r\n change_date           | timestamp(0) without time zone | not null  | \r\n sequence_length       | bigint                         | not null  | \r\n sequence_version      | integer                        | not null  | \r\n study_id              | character varying(15)          |           | \r\n set_acc               | character varying(15)          |           | \r\n sample_id             | character varying(15)          |           | \r\n analysis_id           | character varying(15)          |           | \r\n submission_account_id | character varying(15)          |           | \r\n assembly_level        | integer                        |           |\r\n```\r\n\r\nTracing the execution on the Linux side, we clearly see that memory is progressively reclaimed from the FS cache until it exhausted by the PostgreSQL backend. \r\n\r\nThe PostgreSQL instance has been run with log_min_messages = 'DEBUG1' and this is what we collected from the log file:\r\n\r\n```\r\n< 2016-04-28 11:57:38.228 BST >INFO:  analyzing \"enauni.dbentry\"\r\n< 2016-04-28 11:57:38.228 BST >DEBUG:  oracle_fdw: analyze foreign table 16464\r\n< 2016-04-28 11:57:38.228 BST >DEBUG:  oracle_fdw: set NLS_LANG=AMERICAN_AMERICA.AL32UTF8\r\n< 2016-04-28 11:57:38.229 BST >DEBUG:  oracle_fdw: remote query is SELECT \"BIOSEQID\", \"DBENTRYID\", \"ENTRY_NAME\", \"DROP_ENTRY_STATUS\", \"PRIMARYACC#\", \"VERSION#\", \"DROP_CONFIDENTIAL\", \"DBCODE\", \"EXT_DATE\", \"EXT_VER\", \"FIRST_CREATED\", \"FIRS\r\nT_PUBLIC\", \"HOLD_DATE\", \"PROJECT#\", \"FFDATE\", \"USERSTAMP\", \"TIMESTAMP\", \"ENTRY_TYPE\", \"DATACLASS\", \"QUAL_DIVISION\", \"STATUSID\", \"LAST_PUBLIC\", \"CHANGE_DATE\", \"SEQUENCE_LENGTH\", \"SEQUENCE_VERSION\", \"STUDY_ID\", \"SET_ACC\", \"SAMPLE_ID\", \"ANA\r\nLYSIS_ID\", \"SUBMISSION_ACCOUNT_ID\", \"ASSEMBLY_LEVEL\" FROM \"DATALIB\".\"DBENTRY\"\r\n\r\n[.....]\r\n\r\n< 2016-04-28 12:08:54.484 BST >DEBUG:  autovacuum: processing database \"pgbench\"\r\nTopMemoryContext: 99184 total in 13 blocks; 14392 free (40 chunks); 84792 used\r\n  TopTransactionContext: 8192 total in 1 blocks; 7968 free (0 chunks); 224 used\r\n  Record information cache: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 used\r\n  TableSpace cache: 8192 total in 1 blocks; 3216 free (0 chunks); 4976 used\r\n  Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used\r\n  Type information cache: 24464 total in 2 blocks; 3744 free (0 chunks); 20720 used\r\n  CFuncHash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used\r\n  MessageContext: 8192 total in 1 blocks; 4472 free (1 chunks); 3720 used\r\n  Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used\r\n  smgr relation table: 24576 total in 2 blocks; 9808 free (4 chunks); 14768 used\r\n  TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used\r\n  Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used\r\n  PortalMemory: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used\r\n    PortalHeapMemory: 1024 total in 1 blocks; 968 free (0 chunks); 56 used\r\n      Analyze: 4605577648 total in 559 blocks; 4824 free (5 chunks); 4605572824 used\r\n      Vacuum: 8192 total in 1 blocks; 7808 free (0 chunks); 384 used\r\n  Relcache by OID: 24576 total in 2 blocks; 13872 free (3 chunks); 10704 used\r\n  CacheMemoryContext: 1051288 total in 8 blocks; 256640 free (0 chunks); 794648 used\r\n    pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used\r\n    pg_inherits_parent_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_policy_polrelid_polname_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used\r\n    pg_policy_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_toast_2618_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used\r\n    pg_roles: 15360 total in 4 blocks; 1392 free (0 chunks); 13968 used\r\n    pg_toast_2619_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used\r\n    pg_attrdef_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used\r\n    pg_class_tblspc_relfilenode_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used\r\n    pg_index_indrelid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_db_role_setting_databaseid_rol_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used\r\n    pg_user_mapping_user_server_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used\r\n    pg_user_mapping_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_type_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_type_typname_nsp_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used\r\n    pg_ts_template_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_ts_template_tmplname_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used\r\n    pg_ts_parser_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_ts_parser_prsname_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used\r\n    pg_ts_dict_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_ts_dict_dictname_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used\r\n    pg_ts_config_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_ts_config_cfgname_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used\r\n    pg_ts_config_map_index: 3072 total in 2 blocks; 1784 free (2 chunks); 1288 used\r\n    pg_transform_type_lang_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used\r\n    pg_transform_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_statistic_relid_att_inh_index: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used\r\n    pg_class_relname_nsp_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used\r\n    pg_range_rngtypid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_proc_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_proc_proname_args_nsp_index: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used\r\n    pg_opfamily_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_opfamily_am_name_nsp_index: 3072 total in 2 blocks; 1784 free (2 chunks); 1288 used\r\n    pg_operator_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_operator_oprname_l_r_n_index: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used\r\n    pg_namespace_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_namespace_nspname_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_language_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_language_name_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_foreign_table_relid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_foreign_server_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_foreign_server_name_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_foreign_data_wrapper_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_foreign_data_wrapper_name_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_event_trigger_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_event_trigger_evtname_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_enum_typid_label_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used\r\n    pg_enum_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_default_acl_role_nsp_obj_index: 3072 total in 2 blocks; 1784 free (2 chunks); 1288 used\r\n    pg_conversion_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_constraint_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_conversion_name_nsp_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used\r\n    pg_conversion_default_index: 3072 total in 2 blocks; 1784 free (2 chunks); 1288 used\r\n    pg_collation_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_collation_name_enc_nsp_index: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used\r\n    pg_opclass_am_name_nsp_index: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used\r\n    pg_cast_source_target_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used\r\n    pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used\r\n    pg_amop_fam_strat_index: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used\r\n    pg_amop_opr_fam_index: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used\r\n    pg_aggregate_fnoid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used\r\n    pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used\r\n    pg_amproc_fam_proc_index: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used\r\n    pg_opclass_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_index_indexrelid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used\r\n    pg_class_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_authid_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_auth_members_member_role_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used\r\n    pg_tablespace_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_replication_origin_roname_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_database_datname_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_replication_origin_roiident_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used\r\n    pg_auth_members_role_member_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used\r\n    pg_database_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n    pg_authid_rolname_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used\r\n  WAL record construction: 49760 total in 2 blocks; 6592 free (0 chunks); 43168 used\r\n  PrivateRefCount: 8192 total in 1 blocks; 3744 free (0 chunks); 4448 used\r\n  MdSmgr: 8192 total in 1 blocks; 5888 free (0 chunks); 2304 used\r\n  ident parser context: 0 total in 0 blocks; 0 free (0 chunks); 0 used\r\n  hba parser context: 7168 total in 3 blocks; 1264 free (1 chunks); 5904 used\r\n  LOCALLOCK hash: 24576 total in 2 blocks; 13920 free (4 chunks); 10656 used\r\n  Timezones: 83472 total in 2 blocks; 3744 free (0 chunks); 79728 used\r\n  ErrorContext: 8192 total in 1 blocks; 8160 free (6 chunks); 32 used\r\n< 2016-04-28 12:09:21.634 BST >ERROR:  out of memory\r\n< 2016-04-28 12:09:21.634 BST >DETAIL:  Failed on request of size 5.\r\n< 2016-04-28 12:09:21.634 BST >CONTEXT:  converting column \"drop_entry_status\" for foreign table scan of \"dbentry\", row 27940522\r\n< 2016-04-28 12:09:21.634 BST >STATEMENT:  analyze verbose enauni.dbentry (bioseqid);\r\n```\r\n\r\nWe thought about manually setting the statistics based on Oracle queries against _all_tab_statistics_ and _all_tab_col_statistics_ using something like:\r\n\r\n`alter table schema.foreign_table alter column column_name set (n_distinct = N)`\r\n\r\nThis could maybe be the approach of the Oracle FDW in case of very large tables.\r\n\r\nWe have a very easily reproducible test case in house, in case you would like us to perform any test.\r\n\r\nThanks\r\nAlessio"},"comment":{"url":"https://api.github.com/repos/laurenz/oracle_fdw/issues/comments/216828742","html_url":"https://github.com/laurenz/oracle_fdw/issues/82#issuecomment-216828742","issue_url":"https://api.github.com/repos/laurenz/oracle_fdw/issues/82","id":216828742,"user":{"login":"acheccuc","id":6928697,"avatar_url":"https://avatars.githubusercontent.com/u/6928697?v=3","gravatar_id":"","url":"https://api.github.com/users/acheccuc","html_url":"https://github.com/acheccuc","followers_url":"https://api.github.com/users/acheccuc/followers","following_url":"https://api.github.com/users/acheccuc/following{/other_user}","gists_url":"https://api.github.com/users/acheccuc/gists{/gist_id}","starred_url":"https://api.github.com/users/acheccuc/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/acheccuc/subscriptions","organizations_url":"https://api.github.com/users/acheccuc/orgs","repos_url":"https://api.github.com/users/acheccuc/repos","events_url":"https://api.github.com/users/acheccuc/events{/privacy}","received_events_url":"https://api.github.com/users/acheccuc/received_events","type":"User","site_admin":false},"created_at":"2016-05-04T11:01:24Z","updated_at":"2016-05-04T11:01:24Z","body":"@laurenz I have just started a test and memory usage seems to be very stable. It will take many hours to complete, I will send you another message when it's complete.\r\n\r\nThanks a lot"}}