cancel
Showing results for 
Search instead for 
Did you mean: 

Error in PEM worker on PEM server

SOLVED
Level 3 Adventurer

Error in PEM worker on PEM server

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:

Tags (2)
2 ACCEPTED SOLUTIONS

Accepted Solutions
EDB Team Member

Re: Error in PEM worker on PEM server

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.

EDB Team Member

Re: Error in PEM worker on PEM server

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.

30 REPLIES
EDB Team Member

Re: Error in PEM worker on PEM server

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 .

Level 3 Adventurer

Re: Error in PEM worker on PEM server

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
EDB Team Member

Re: Error in PEM worker on PEM server

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 .

Highlighted
EDB Team Member

Re: Error in PEM worker on PEM server

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 

EDB Team Member

Re: Error in PEM worker on PEM server

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.

EDB Team Member

Re: Error in PEM worker on PEM server

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

EDB Team Member

Re: Error in PEM worker on PEM server

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

Level 3 Adventurer

Re: Error in PEM worker on PEM server

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

EDB Team Member

Re: Error in PEM worker on PEM server

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.