Search code examples
postgresqlfunctiontriggerspgadmin

Deleted trigger in Postgres - still trying to run somewhere. Showing up as failed in postgres Log

I'm not even sure where to begin in exploring this one.

I have a postgres database. I created three functions that look like this one - 

CREATE OR REPLACE FUNCTION mid1_spm()
  RETURNS trigger AS
$BODY$
BEGIN
    insert into machines_spm_storage ( SELECT * FROM machineid1_spm_step4);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION mid1_spm()
  OWNER TO postgres;

I used the following script to create the trigger:

--trigger function to populate spm storage table
CREATE OR REPLACE FUNCTION mid3_spm() RETURNS TRIGGER AS
$BODY$
BEGIN
    insert into machines_spm_storage ( SELECT * FROM machineid3_spm_step4);
END;
$BODY$
language plpgsql;


--trigger to initiate function
CREATE TRIGGER trigger_mid3_spm
    AFTER UPDATE ON machines_machineevent
    FOR EACH ROW
    EXECUTE PROCEDURE mid3_spm();

The goal of the trigger is to pull data from a view and store it in the machines_spm_storage table.

When I first created the test trigger the table name was machine_5_spm_storage. Now I keep getting this error (in postgres logs)

2017-03-06 14:05:01 EST [9468-1] epic@epic_ar_db ERROR:  relation "machine_5_spm_storage" does not exist at character 13
2017-03-06 14:05:01 EST [9468-2] epic@epic_ar_db STATEMENT:  INSERT INTO machine_5_spm_storage ( SELECT * FROM machine_5_spm_step4);

I have deleted this table, deleted all triggers, restarted the postgres service, dropped and rebuilt the database. I have no idea why (or even HOW) it's still trying to run my original trigger.

I had just deleted the original to fine-tune what I had. I did discover if I change the table name I'm trying to insert the data into to the one in the error - the problem goes away and the trigger works fine. But there's nothing in the script that even references that old table.

ANY help or ideas are appreciated!

P.S. I know the trigger works in and of itself - my problem is that it seems to keep referencing a table that no longer exists. And it's not recognizing that I have created a new one - same function - I just changed the name).

UPDATE: Here is what the newest version of the trigger and function looks like:

Function: 

-- Function: mid1_spm()

-- DROP FUNCTION mid1_spm();

CREATE OR REPLACE FUNCTION mid1_spm()
  RETURNS trigger AS
$BODY$
BEGIN
    insert into machines_spm_storage ( SELECT * FROM machineid1_spm_step4);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION mid1_spm()
  OWNER TO postgres;

Trigger:

CREATE TRIGGER trigger_mid2_spm
  AFTER UPDATE
  ON machines_machineevent
  FOR EACH ROW
  EXECUTE PROCEDURE mid2_spm();

The only difference really is the name of the view and the name of the table. But I completely dropped (cascaded) the original test version.

Today the postgres log looks like this:

2017-03-07 10:45:01 EST [12069-1] epic@epic_ar_db ERROR:  relation "machine_5_spm_storage" does not exist at character 13
2017-03-07 10:45:01 EST [12069-2] epic@epic_ar_db STATEMENT:  INSERT INTO machine_5_spm_storage ( SELECT * FROM machine_5_spm_step4);
2017-03-07 10:50:01 EST [12210-1] epic@epic_ar_db ERROR:  relation "machine_5_spm_storage" does not exist at character 13
2017-03-07 10:50:01 EST [12210-2] epic@epic_ar_db STATEMENT:  INSERT INTO machine_5_spm_storage ( SELECT * FROM machine_5_spm_step4);
2017-03-07 10:55:02 EST [12434-1] epic@epic_ar_db ERROR:  relation "machine_5_spm_storage" does not exist at character 13
2017-03-07 10:55:02 EST [12434-2] epic@epic_ar_db STATEMENT:  INSERT INTO machine_5_spm_storage ( SELECT * FROM machine_5_spm_step4);
2017-03-07 11:00:01 EST [12759-1] epic@epic_ar_db ERROR:  relation "machine_5_spm_storage" does not exist at character 13
2017-03-07 11:00:01 EST [12759-2] epic@epic_ar_db STATEMENT:  INSERT INTO machine_5_spm_storage ( SELECT * FROM machine_5_spm_step4);

It's trying to run the triggers, but failing because (somehow) it's referencing the ORIGINAL name of the view and table - even though it's not written that way. I'm at a complete loss for this one.

UPDATE as of 3/7/17 - I deleted all of the triggers I created. And that script is STILL trying to run somehow. So I guess this is a different problem than first realized. The script is still failing and showing up in the log even though there are NO triggers, No functions and no running jobs.

Solution

  • I was not able to drop the database and start with a clean schema, however when I tried to drop and restore the original (with the data) to see if that would reset whatever was running, I got a port 5432 already in use error.

    I'm not exactly sure how to explain it - but it's as if there were two occurrences of the database running. After I dropped the database, restarted pgadmin, the database was there but was a previous version. I restored with the latest data (again) and I no longer have a "ghost trigger" running in the background.