eulerto
ERROR: could not open relation with OID 149416084
Hello We faced an issue today: ERROR: could not open relation with OID 149416084 From postgres log: 2021-11-03 01:22:19.505 UTC [31907] (user=xmcdcuser) (db=na5) (rhost=10.139.204.7) (app=auditConsumer) [vxid:843/0 txid:0] [idle] ERROR: could not open relation with OID 149416084 2021-11-03 01:22:19.505 UTC [31907] (user=xmcdcuser) (db=na5) (rhost=10.139.204.7) (app=auditConsumer) [vxid:843/0 txid:0] [idle] CONTEXT: slot "cdc_na5", output plugin "wal2json", in the change callback, associated LSN 4756/A5147B48 2021-11-03 01:22:19.505 UTC [31907] (user=xmcdcuser) (db=na5) (rhost=10.139.204.7) (app=auditConsumer) [vxid:843/0 txid:0] [idle] STATEMENT: START_REPLICATION SLOT cdc_na5 LOGICAL 4749/EC77340 ("include-typmod" 'false', "include-types" 'false', "include-schemas" 'false', "include-not-null" 'false', "add-tables" '.event_property_hierarchy_values,.drngss,.persons_func,.num_page_dvc_dtl,.gen_dvc_dtl,.evs_message,.live_ntfn,.cust_attr_cat,.role_perm_grants,.org_dvc_names,.role,.response_choices,.tmp_rpmt,.phone_creds,.person_cust_values,.application,.attrs,.event_property_values,.im_dvc_dtl,.cust_flds,.oncall_reminders_configured_start_times,.voc_dvc_dtl,.org_svc_provs,.email_dvc_dtl,.comm_values,.org_tz,.attr_cat_join,.comm,.date_rng,.tm_rotation,.org_holidays,.dvc,.person_attrs,.release_version,.fax_dvc_dtl,.person_ntfn,.message_panel_recipient,.dvc_ntfn,.response_choices_transl,.grp_ntfn,.date_holiday_cntner,.tz,.ntfn_responses,.cdc_audits,.perms,.runtime_response_choices,.txt_phone_dvc_dtl,.drs_excludes,.gcm_push_dvc_dtl,.tm_ntfn,.attr_cat,.ntfn,.event_property_list_values,.tm,.oncall_reminders,.grp_tf,.event_property_text_values,.oncall_reminders_configured_devices,.voice_ivr_dvc_dtl,.role_func,.txt_page_dvc_dtl,.org_languages,.comm_flds,.addresses,.ev_annotation,.web_creds,.date_rules,.sns,.recipient_ntfn,.tm_mbrships,.sites,.drss,.languages,.ev_metadata,.site_holidays,.grp,.tm_escalation,.persons,.org_func_perms,.message_panel,.date_rpt_patterns,.org_func,.apple_push_dvc_dtl,.evs,.conferences,.recipients,.runtime_response_choices_transl,.drs_includes,*.drngs_rng', "include-timestamp" 'true', "include-lsn" 'true', "include-type-oids" 'false', "pretty-print" 'false', "write-in-chunks" 'true', "include-xids" 'true')
Checked what was select relname from pg_class where oid=149416084;
idx_comm3
What might be wrong: we started reindex index concurrently public.idx_comm3 at 2021-11-03 01:21:59 and reindex process was blocked by another transaction, reindex successfully finished at 2021-11-03 01:23:15 But since 2021-11-03 01:22:19 wal2json tried to read that index ( OID 149416084) but in fact, index wasn't ready till 2021-11-03 01:23:15
How we fixed it: ran pg_replication_slot_advance
It looks like a bug to me...
PS: we are on
wal2json=2.4.1 Postgres 12.8 OS CentOS 7
Bumping this. We recently saw this on PG 16 (actually AlloyDB, but basically the same). We're not sure if it was a CREATE INDEX CONCURRENTLY -- which failed to acquire locks, so died -- or the REINDEX CONCURRENTLY to fix the situation afterwards that caused the problem.
Regardless, the wal2json plugin would crash when trying to emit commits on the related table. We had to drop and recreate the replication slot.
