Solved: Postgres Rocks! Powered by EDB - Re: Error in PEM worker on PEM server - Postgres Rocks

Polem · ‎11-12-2018

ERROR: insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
Detail: Key (alert_id)=(1814) is not present in table "alert".
Context: SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state", "current_state_since", "last_processed", "display_value") VALUES (alert_rec.id, sql_ret, state, CASE WHEN state IS NOT NULL THEN now() ELSE NULL END, now(),
sql_ret_display)" PL/pgSQL function pem.process_one_alert() line 300 at SQL statement
Statement: SELECT pem.process_one_alert()
Date: 2018-11-11 23:03:50
Database: pem
Application: Postgres Enterprise Manager - Agent (Alert
Thread [#1])
User: agent1
Remote: 127.0.0.1
Code:

kapil · ‎11-22-2018

Hi Polem,

Probably the best recovery strategy is to DELETE that row by ctid,then reinsert the same data. Lather, rinse, repeat until you can reindex successfully. Better check your other tables too.

View solution in original post

dhananjayjejur · ‎11-23-2018

Hi Paul,

It is not recommended to truncate the alerts table.

If you wish to go with it then we recommend to re-setup the PEM again.

Hope this helps.

Regards,
Dhananjay

View solution in original post

Tushar-Takate · ‎11-12-2018

Hi Team ,

Is someone have manually deleted entry from pem.alert table for id - 1814 .

Because pem.alert(id) is referance as a Foreign-key constraints in pem.alert_status(alert_id) table .

Which is casing the below error .

"ERROR: insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey""

Can you please share below details with us to debug this issues .

1) PEM and PostgreSQL Version .
2) pem worker log before and after 1 day when you face this issues .
3) postgresql.log before and after 1 day when you face this issues .

Thanks .

Polem · ‎11-12-2018

0)No delete on pem.alert

1)PEM version 7.4
Enterprisedb 10.5.12
SchemaVersion: 201808231
2) These are all the logs that I have
3) That happen already since 29/10
Postgres log file is 291MB so where I can upload this file?
Regards,
Paul-Emmanuel Devadder

Tushar-Takate · ‎11-12-2018

Hi Paul ,

Thanks for the details for PEM and DB server veriosn .

We will try to reproduce it at our end .

We will update you soon .

Thanks ,

Tushar T .

slonkar · ‎11-12-2018

Hi Paul,

These error occurs when someone tries to update the pem.alert_status table for the id which is not present in its referenced table pem.alert.

For further analysis, could you please upload the worker.log, database logs and definition of the pem.process_one_alert() function from the PEM server to below location :

https://enterprisedb.sharefile.com/r-rb21398581fe44b88

Please let us know in case of any issues/queries.

Regards,

Sudhir

Ranjan · ‎11-13-2018

Hi Paul,

We are reviewing the log files shared by you and see too many ERRORs regarding unable to write datafile due to disk space which results in could not access status of the transactions.

We are analyzing this for reported error and will update you soon. Let us know in case of any issues.

slonkar · ‎11-13-2018

Hi Paul,

Thanks for sharing the log files and these error is occurring due to the fact that PEM agent trying to set the alert status for the id which is not present in the pem.alert table.

For further analysis could you please share the output of the below commands on previous upload link :

1) select * from pem.alert;

2) select * from pem.alert_status;

Also, if it is feasible for you, could you please try restarting the PEMHTTPD service and see if the error still exists.

Please let us know in case of any issues/queries.

Regards,

Sudhir

slonkar · ‎11-14-2018

Hi Paul,

Thanks for sharing the dump and other info, from the info shared by you, you are getting the below error message while performing the pg_dump of the pem.alert_status table.

pg_dump: Dumping the contents of table "alert_status" failed: PQgetResult() failed.

pg_dump: Error message from server: ERROR: missing chunk number 0 for toast value 1460190 in pg_toast_19233

Which indicates that toast table with pid 19233 is corrupted. Could you please verify first that oid 19233 belongs to the alert_status table and then perform the reindex on it.

After reindex try taking the pg_dump of the table, if still gives the missing chunk number error, we need to find the corrupted rows and delete those from table.

Please update us the status after the reindex and let us know in case of any issues/queries.

Regards,

Sudhir

Polem · ‎11-14-2018

Hi,
See bellow I try several things but still the same error

pem=# select 19233::regclass;
regclass
------------------
pem.alert_status
(1 row)

pem=# reindex table pg_toast.pg_toast_19233;
REINDEX
pem=# \q
-bash-4.2$ pg_dump -d pem -t pem.alert_status -f alert_status.sql
pg_dump: Dumping the contents of table "alert_status" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: missing chunk number 0 for toast value 1460192 in pg_toast_19233
pg_dump: The command was: COPY pem.alert_status (alert_id, current_value, current_state, current_state_since, last_processed, state_change_count, info, info_cols, info_vals, display_value) TO stdout;
-bash-4.2$ psql -d pem
psql.bin (10.5.12)
Type "help" for help.

pem=# vacuum full pem.alert_status;
ERROR: uncommitted xmin 109172708 from before xid cutoff 155581532 needs to be frozen
pem=# vacuum analyze pem.alert_status;
ERROR: missing chunk number 0 for toast value 1460191 in pg_toast_19233
pem=#

Kind regards,
Paul-Emmanuel Devadder

dhananjayjejur · ‎11-14-2018

Hi Paul,

Kindly refer to a procedure to detect the corrupted rows in the table.

BEGIN TRANSACTION;

CREATE TABLE edb_corrupted_rows(schemaname TEXT,
                                                                tablename TEXT,
                                                                t_ctid TID,
                                                                sqlstate TEXT,
                                                                sqlerrm TEXT);

CREATE OR REPLACE FUNCTION check_table_row_corruption(schemaname TEXT, tablename TEXT) RETURNS VOID AS $$
DECLARE
    rec RECORD;
    tmp RECORD;
    t_ctid TID;
    tmp_text TEXT;
BEGIN
    FOR rec IN EXECUTE 'SELECT ctid
                        FROM ' || quote_ident(schemaname) || '.' || quote_ident(tablename)
        LOOP
    BEGIN
        t_ctid := rec.ctid;
        BEGIN
            EXECUTE 'SELECT * FROM '
                    || quote_ident(schemaname) || '.' || quote_ident(tablename)
                    || ' WHERE ctid = ''' || t_ctid || '''::tid'
                INTO STRICT tmp;

            tmp_text := tmp::text;
        EXCEPTION WHEN OTHERS THEN
            INSERT INTO edb_corrupted_rows VALUES(schemaname, tablename, t_ctid, SQLSTATE::text, SQLERRM::text);
        END;
    END;
    END LOOP;
END;
$$ LANGUAGE PLPGSQL;

COMMIT TRANSACTION;

Note :

If the above function failed to execute on the corrupted partition then set SET synchronize_seqscans = off; and then again execute the function on affected table partition.
Once the data is gathered for one table in "edb_corrupted_rows" table then we need to delete those rows from the table with the help of ctid.

Hope this solves your problem.

Regards,
Dhananjay

Polem · ‎11-14-2018

Hi,

I'm now able to dump pem.alert_status. I will upload to you share drive.

Kind regards,

Paul-Emmanuel Devadder

NB: I restart pemagent and I have still the same error about alert_id=6 missing

dhananjayjejur · ‎11-14-2018

Hi Paul,

Thank you for your response. However, we kindly request you to reply to the PostgresRocks request via PostgresRocks portal.

As when you reply to the notification email from your inbox that creates a new thread on the PostgresRocks and this might create confusion due to missing previous information of previous thread.

I am currently copying your response on the previous thread for future reference.

Warm Regards,

Regards,
Dhananjay

dhananjayjejur · ‎11-14-2018

Hi Paul,

Copying your response sent via a different thread.

--Quote--

Hi,

I'm now able to dump pem.alert_status. I will upload to you share drive.

Kind regards,

Paul-Emmanuel Devadder

NB: I restart pemagent and I have still the same error about alert_id=6 missing

--UnQuote--

We are waiting for a reply on the dump file upload and the output of previously shared procedure which will tell the corrupted ctid's.

Warm Regards.

Regards,
Dhananjay

Polem · ‎11-14-2018

Hi,

After execution of the function provided, 3 records was in the table:

pem=# select * from edb_corrupted_rows ;
schemaname |  tablename   |  t_ctid  | sqlstate |                             sqlerrm
------------+--------------+----------+----------+------------------------------------------------------------------
pem        | alert_status | (86,10)  | XX000    | missing chunk number 0 for toast value 1460192 in pg_toast_19233
pem        | alert_status | (312,28) | XX000    | missing chunk number 0 for toast value 1460190 in pg_toast_19233
pem        | alert_status | (345,12) | XX000    | missing chunk number 0 for toast value 1460162 in pg_toast_19233
(3 rows)
 
pem=# delete from pem.alert_status where ctid in ( select t_ctid from edb_corrupted_rows );
DELETE 3
pem=# \q

So that's why I was able to dump alert_status

Kind regards,

Paul-Emmanuel Devadder

dhananjayjejur · ‎11-14-2018

Hi Paul,

Thank you for your update. It is good to hear that you are able to successfully delete the corrupted rows from the database and successfully able to dump the table in a flat file.

Warm Regards,

Dhananjay

Regards,
Dhananjay

Polem · ‎11-19-2018

Hi,

Error in Worker log still present "alert_id=6 not present in alert"

"Update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"

Kind regards,

Paul-Emmanuel

dhananjayjejur · ‎11-19-2018

Hi Paul,

Can you please share the output of below commands. This will help us to identify the issue in more details.

\d pem.alert_status

\d pem.alert

Select * from pem.alert_status where alert_id='6';

select * from pem.alert where id='6';

Note: All above commands need to be executed on the pem database.

Regards.

Regards,
Dhananjay

Polem · ‎11-20-2018

Hi,

Find here the output of requested queries:

Table "pem.alert_status"
       Column        |           Type           | Collation | Nullable | Default

---------------------+--------------------------+-----------+----------+---------
alert_id            | integer                  |           | not null |

 current_value       | numeric                  |           |          |

 current_state       | pem.alert_state          |           |          |

 current_state_since | timestamp with time zone |           |          |

 last_processed      | timestamp with time zone |           | not null |

 state_change_count  | integer                  |           | not null | 0
info                | text                     |           |          |

 info_cols           | text[]                   |           |          |

 info_vals           | text[]                   |           |          |

 display_value       | text                     |           |          |

Indexes:
    "alert_status_alert_id_key" UNIQUE CONSTRAINT, btree (alert_id)
Foreign-key constraints:
    "alert_status_alert_id_fkey" FOREIGN KEY (alert_id) REFERENCES pem.alert(id) ON UPDATE RESTRICT ON DELETE CASCADE
Triggers:
    detail_alert_information BEFORE INSERT OR UPDATE ON pem.alert_status FOR EACH ROW EXECUTE PROCEDURE pem.get_detail_alert_info()
    log_alert_history BEFORE INSERT OR UPDATE ON pem.alert_status FOR EACH ROW EXECUTE PROCEDURE pem.log_alert_history()
    send_notifications_trigger AFTER INSERT OR UPDATE ON pem.alert_status FOR EACH ROW EXECUTE PROCEDURE pem.send_notifications()
 
                                                      Table "pem.alert"
              Column               |           Type           | Collation | Nullable |                Default               

-----------------------------------+--------------------------+-----------+----------+---------------------------------------
id                                | integer                  |           | not null | nextval('pem.alert_id_seq'::regclass)
name                              | text                     |           |          |

 enabled                           | boolean                  |           | not null | true
template_id                       | integer                  |           |          |

 agent_id                          | integer                  |           | not null |

 server_id                         | integer                  |           |          |

 database_name                     | text                     |           |          |

 schema_name                       | text                     |           |          |

 package_name                      | text                     |           |          |

 object_name                       | text                     |           |          |

 params                            | text[]                   |           |          |

 operator                          | "char"                   |           | not null |

 thresholds                        | numeric[]                |           | not null |

 check_frequency                   | integer                  |           | not null | 1
history_retention                 | integer                  |           | not null | 30
error_message                     | text                     |           |          |

 acknowledged                      | boolean                  |           | not null | false
email_group_id                    | integer                  |           |          |

 send_email                        | boolean                  |           | not null | false
last_mail_send                    | timestamp with time zone |           |          |

 flapping_detected                 | boolean                  |           | not null | false
last_flapping_detection_processed | timestamp with time zone |           | not null | current_timestamp
send_trap                         | boolean                  |           | not null | false
snmp_trap_version                 | integer                  |           | not null | 2
error_timestamp                   | timestamp with time zone |           |          |

 low_send_trap                     | boolean                  |           | not null | false
low_email_group_id                | integer                  |           |          |

 med_send_trap                     | boolean                  |           | not null | false
med_email_group_id                | integer                  |           |          |

 high_send_trap                    | boolean                  |           | not null | false
high_email_group_id               | integer                  |           |          |

 execute_script                    | boolean                  |           | not null | false
execute_script_on_clear           | boolean                  |           | not null | false
execute_script_on_pem_server      | boolean                  |           | not null | false
script_code                       | text                     |           |          |

 submit_to_nagios                  | boolean                  |           | not null | false
auto_created                      | boolean                  |           | not null | false
Indexes:
    "alert_pkey" PRIMARY KEY, btree (id)
    "alert_name_object_type_uniq" UNIQUE, btree (COALESCE(name, 'dummy_name'::text), agent_id, COALESCE(server_id, 0), COALESCE(database_name, 'dummy_database_name'::text), COALESCE(schema_name,
 'dummy_schema_name'::text), COALESCE(package_name, 'dummy_package_name'::text), COALESCE(object_name, 'dummy_object_name'::text))
Check constraints:
    "check_alert_params_array_size" CHECK (pem.check_alert_params_array_size(template_id, params))
    "check_frequency_gt_zero" CHECK (check_frequency > 0)
    "history_gt_zero" CHECK (history_retention > 0)
    "valid_operators" CHECK (operator = ANY (ARRAY['<'::"char", '>'::"char"]))
    "validate_thresholds_ordering" CHECK (array_upper(thresholds, 1) = 3 AND thresholds[1] IS NOT NULL AND thresholds[2] IS NOT NULL AND thresholds[3] IS NOT NULL AND
CASE
    WHEN operator = '<'::"char" THEN thresholds[1] > thresholds[2] AND thresholds[2] > thresholds[3]
    WHEN operator = '>'::"char" THEN thresholds[1] < thresholds[2] AND thresholds[2] < thresholds[3]
    ELSE false
END)
Foreign-key constraints:
    "alert_email_group_id_fkey" FOREIGN KEY (email_group_id) REFERENCES pem.email_group(id) ON UPDATE RESTRICT ON DELETE SET DEFAULT
    "alert_high_email_group_id_fkey" FOREIGN KEY (high_email_group_id) REFERENCES pem.email_group(id) ON UPDATE RESTRICT ON DELETE SET DEFAULT
    "alert_low_email_group_id_fkey" FOREIGN KEY (low_email_group_id) REFERENCES pem.email_group(id) ON UPDATE RESTRICT ON DELETE SET DEFAULT
    "alert_med_email_group_id_fkey" FOREIGN KEY (med_email_group_id) REFERENCES pem.email_group(id) ON UPDATE RESTRICT ON DELETE SET DEFAULT
    "alert_template_id_fkey" FOREIGN KEY (template_id) REFERENCES pem.alert_template(id) ON UPDATE RESTRICT ON DELETE CASCADE
Referenced by:
    TABLE "pem.alert_history" CONSTRAINT "alert_history_alert_id_fkey" FOREIGN KEY (alert_id) REFERENCES pem.alert(id) ON UPDATE RESTRICT ON DELETE CASCADE
    TABLE "pem.alert_status" CONSTRAINT "alert_status_alert_id_fkey" FOREIGN KEY (alert_id) REFERENCES pem.alert(id) ON UPDATE RESTRICT ON DELETE CASCADE
Triggers:
    alert_error_timestamp BEFORE UPDATE ON pem.alert FOR EACH ROW WHEN (old.error_message IS DISTINCT FROM new.error_message) EXECUTE PROCEDURE pem.log_alert_error_timestamp()
 
alert_id | current_value | current_state | current_state_since | last_processed | state_change_count | info | info_cols | info_vals | display_value

----------+---------------+---------------+---------------------+----------------+--------------------+------+-----------+-----------+---------------
(0 rows)
 
id | name | enabled | template_id | agent_id | server_id | database_name | schema_name | package_name | object_name | params | operator | thresholds | check_frequency | history_retention
 | error_message | acknowledged | email_group_id | send_email | last_mail_send | flapping_detected | last_flapping_detection_processed | send_trap | snmp_trap_version | error_timestamp | low_send_trap | low_email_group_id | med_send_trap | med_email_group_id
 | high_send_trap | high_email_group_id | execute_script | execute_script_on_clear | execute_script_on_pem_server | script_code | submit_to_nagios | auto_created

----+------+---------+-------------+----------+-----------+---------------+-------------+--------------+-------------+--------+----------+------------+-----------------+-------------------+---------------+--------------+----------------+------------+----------------+-------------------+-----------------------------------+-----------+-------------------+-----------------+---------------+--------------------+---------------+--------------------+----------------+---------------------+----------------+-------------------------+------------------------------+-------------+------------------+--------------

Kind regards,

Paul-Emmanuel

slonkar · ‎11-20-2018

Hi Paul,

Thanks for the update. From the earlier communication, you have removed the corrupted rows and you are able to take dump of alert_status table.

Just to verify, if you have any other corruption present in the DB, could you please first perform the pg_dump of entire database cluster.

If there are no issues in the pg_dump and you still receive error in pemworker.log for alert id 6, could you please try restarting the PEM services in the below order :

1) PEM HTTPD

2) PEM Backend DB

3) PEM agent

As output shared by you, there is no entry for id 6, in both pem.alert and pem.alert_status, we want to verify if it still exists after the restart of the services.

Also, could you please let us know whether you have any backup of this PEM database cluster before the issue, as you have removed the corrupted rows from the cluster and to verify which rows from the table were removed, you can restore that backup and add it in current cluster.

Please let us know in case of any further issues.

Regards,

Sudhir Lonkar

Polem · ‎11-20-2018

Hi,

No corruption in pg_dump. I restart the 3 services in the order requested. There is still errors in worker.log but id have change since this morning 1776 that is not in pem.alert.

I 'm wondering if we can cleanning or purging all alerts in one command. I don't need those old alerts.

Kind regards,

Paul-Emmanuel

kapil · ‎11-21-2018

Hi Polem,

Could you please attach the worker.log for our reference in order to investigate further on this.

Polem · ‎11-21-2018

@kapil wrote:
Hi Polem,

Could you please attach the worker.log for our reference in order to investigate further on this.

Due to limitation of 75000 chars I upload only an extract:

Wed Nov 21 15:36:23 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement

Wed Nov 21 15:36:33 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement

Wed Nov 21 15:36:43 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement

Wed Nov 21 15:36:53 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement

Wed Nov 21 15:37:03 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement

Wed Nov 21 15:37:13 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement

Wed Nov 21 15:37:23 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement

Wed Nov 21 15:37:33 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement

Wed Nov 21 15:37:43 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement

Wed Nov 21 15:37:54 2018 : WARNING: Alerts[1]: Error processing an alert: ERROR:  insert or update on table "alert_status" violates foreign key constraint "alert_status_alert_id_fkey"
DETAIL:  Key (alert_id)=(1776) is not present in table "alert".
CONTEXT:  SQL statement "INSERT INTO pem.alert_status("alert_id", "current_value", "current_state",
		    "current_state_since", "last_processed", "display_value")
		VALUES (alert_rec.id, sql_ret, state,
				CASE
				WHEN state IS NOT NULL
				THEN now()
				ELSE NULL
				END,
				now(), sql_ret_display)"
PL/pgSQL function pem.process_one_alert() line 300 at SQL statement

kapil · ‎11-21-2018

Hi Polem,

Could you please check if their is any further corruption, if yes could you please check and elminate the corruption, once you confirm that corruption is no longer exit could you please reindex the tables "alert" and "alert_status" and verify the woker logs for the error exist or not.

Polem · ‎11-21-2018

Hi,
Concerning corruption I have dumped PEM database successfully, for reindexing the 2 tables tomorrow because I'm not in front of the system and I don't have remote access.
Kindly
Paul-Emmanuel

Ranjan · ‎11-21-2018

Hi Polem,

Sure, let's have reindex for these two tables and share logs with us.

Polem · ‎11-22-2018

@Ranjan wrote:
Hi Polem,

Sure, let's have reindex for these two tables and share logs with us.

Hi,

I get this error when I try to REINDEX:

pem=# reindex table pem.alert;
ERROR:  failed to find parent tuple for heap-only tuple at (50,14) in table "alert"
pem=# reindex table pem.alert_status;
ERROR:  failed to find parent tuple for heap-only tuple at (33,3) in table "alert_status"
pem=#

Kindly

Paul-Emmanuel

kapil · ‎11-22-2018

Hi Polem,

Their is a corruption in the table, You can look at the specific row involved here with i.e,

select * from alert where ctid = '(50,14)';

select * from alert_status where ctid = '(33,13)';

Probably the best recovery strategy is to DELETE that row by ctid,then reinsert the same data. Lather, rinse, repeat until you can reindex successfully. Better check your other tables too.

Polem · ‎11-22-2018

Hi,

There is the output:

pem=# select * from pem.alert where ctid = '(50,14)';
-[ RECORD 1 ]---------------------+------------------------------
id                                | 580
name                              | Server Down
enabled                           | t
template_id                       | 233
agent_id                          | 0
server_id                         | 35
database_name                     |
schema_name                       |
package_name                      |
object_name                       |
params                            | {}
operator                          | >
thresholds                        | {0.1,0.2,0.3}
check_frequency                   | 1
history_retention                 | 30
error_message                     |
acknowledged                      | f
email_group_id                    |
send_email                        | f
last_mail_send                    |
flapping_detected                 | f
last_flapping_detection_processed | 2018-10-05 10:50:32.118661+02
send_trap                         | f
snmp_trap_version                 | 2
error_timestamp                   |
low_send_trap                     | f
low_email_group_id                |
med_send_trap                     | f
med_email_group_id                |
high_send_trap                    | f
high_email_group_id               |
execute_script                    | f
execute_script_on_clear           | f
execute_script_on_pem_server      | f
script_code                       |
submit_to_nagios                  | f
auto_created                      | t
 
pem=# select * from pem.alert_status where ctid = '(33,13)';
-[ RECORD 1 ]-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alert_id            | 2444
current_value       | 1
current_state       |
current_state_since |
last_processed      | 2018-10-29 14:10:34.691279+01
state_change_count  | 0
info                | Query = SELECT 1                                                                                                                                                                                                                                                       +
                    | Is Idle? = t                                                                                                                                                                                                                                                           +
                    | Username = enterprisedb                                                                                                                                                                                                                                                +
                    | Is vacuum? = f                                                                                                                                                                                                                                                         +
                    | Process ID = 18720                                                                                                                                                                                                                                                     +
                    | Wait event =                                                                                                                                                                                                                                                           +
                    | Is Waiting? = f                                                                                                                                                                                                                                                        +
                    | Database name = postgres                                                                                                                                                                                                                                               +
                    | Is autovacuum? = f                                                                                                                                                                                                                                                     +
                    | Wait event type =                                                                                                                                                                                                                                                      +
                    | Query start time = 2018-10-29 14:09:07.800886+01                                                                                                                                                                                                                       +
                    | State change time = 2018-10-29 14:09:07.801004+01                                                                                                                                                                                                                      +
                    | Process start time = 2018-10-29 13:54:45.221114+01                                                                                                                                                                                                                     +
                    | State change since = 00:01:26.890275                                                                                                                                                                                                                                   +
                    | Transaction start time =                                                                                                                                                                                                                                               +
                    | Is idle in transaction? = f                                                                                                                                                                                                                                            +
                    |                                                                                                                                                                                                                                                                        +
                    |                                                                                                                                                                                                                                                                        +
                    |
info_cols           | {Query,"Is Idle?",Username,"Is vacuum?","Process ID","Wait event","Is Waiting?","Database name","Is autovacuum?","Wait event type","Query start time","State change time","Process start time","State change since","Transaction start time","Is idle in transaction?"}
info_vals           | {{"SELECT 1",t,enterprisedb,f,18720,"",f,postgres,f,"","2018-10-29 14:09:07.800886+01","2018-10-29 14:09:07.801004+01","2018-10-29 13:54:45.221114+01",00:01:26.890275,"",f}}
display_value       |
 
pem=#

kapil · ‎11-22-2018

Hi Polem,

Probably the best recovery strategy is to DELETE that row by ctid,then reinsert the same data. Lather, rinse, repeat until you can reindex successfully. Better check your other tables too.

View solution in original post

Polem · ‎11-23-2018

Hi,

I delete manually a lot of corrupted records. Can I truncate those 2 tables and start with a fresh status of alerting in PEM?

Kindly,

Paul-Emmanuel

dhananjayjejur · ‎11-23-2018

Hi Paul,

It is not recommended to truncate the alerts table.

If you wish to go with it then we recommend to re-setup the PEM again.

Hope this helps.

Regards,
Dhananjay

View solution in original post

Krishnan · ‎10-28-2019

Thank you for the function. Thats really helpful.

Regards

Krishnan