problem with plpgsql trigger during update table with triggered column

How to add a record ID and STATUS to the exception message to see which process ID is causing the problem?

Start with exhausting the options offered by raise statement on its own. Lifting straight from 41.9.1. Reporting Errors and Messages:

RAISE [ level ] 'format' [, expression [, ... ]] [ USING option { = | := } expression [, ... ] ];
RAISE [ level ] condition_name [ USING option { = | := } expression [, ... ] ];
RAISE [ level ] SQLSTATE 'sqlstate' [ USING option { = | := } expression [, ... ] ];
RAISE [ level ] USING option { = | := } expression [, ... ];
RAISE ;

It's a good idea to use the right level, the right error code and make it point at the table and column in question, make the message only describe the problem, put the record values (your STATUS and ID) in the detail and if you already know what could lead to this error, add an adequate hint.

You can combine that with what trigger variables can tell you about the situation, and use string functions like format or concat_ws() to construct each element of what'll be exposed by raise. I'd advise against chaining || because:

• a single null value will nullify the whole message
• you need to double the amount of || to add separators
• to secure it against null values, you can wrap each concatenated element in coalesce() making it even longer

Both concat_ws and format() handle all three of the above on their own, in a much more compact and readable manner.

What often ends up happening is you construct a custom text serializer for a given record type and operation, mapping out each column name with it's value by hand. What you can do instead is pass the new and old records through to_jsonb() and that'll build you a map with column names and values automatically, or inject the entire new/old into the message, to get just the values, without the long chain of new.col1, new.col2, new.col3 and so on.

Here's an example from another thread showing a combination of the above:

create function trgf_parent_only_with_true_my_field_a()returns trigger as
$f$ declare is_violated boolean:=false;
begin
case TG_TABLE_NAME 
  when 'my_table_a' 
  then is_violated:=exists(select from my_table_b 
                           where fk_id=NEW.id);
  when 'my_table_b' --allows `NEW.fk_id` reference, absent in 'my_table_a' 
  then is_violated:=not exists(select from my_table_a 
                               where my_field_a is true
                               and id=NEW.fk_id);
end case;
if is_violated then raise exception using
    message = format('%s on table %I violates foreign key constraint %I'
                     , TG_OP, TG_TABLE_NAME, TG_NAME),
    errcode = 'foreign_key_violation', -- sqlstate 23503
    schema  = TG_TABLE_SCHEMA,
    table   = TG_TABLE_NAME,
    column  = 'fk_id',
    detail  = format('violating row of %I was %L'
                     , TG_TABLE_NAME, NEW); 
end if;
return new;--doesn't matter if you return `null` or `new` in an `after` trigger
end $f$ language plpgsql;

If you'll have to do a lot of trigger debugging, also consider the order in which multiple triggers get fired:

If more than one trigger is defined for the same event on the same relation, the triggers will be fired in alphabetical order by trigger name. In the case of BEFORE and INSTEAD OF triggers, the possibly-modified row returned by each trigger becomes the input to the next trigger. If any BEFORE or INSTEAD OF trigger returns NULL, the operation is abandoned for that row and subsequent triggers are not fired (for that row).

You could have more than one before update trigger and the one that's alphabetically/lexicographically earlier is able to alter what's in new record before the later trigger inspects it. In that case, the update statement that started it all can by all means carry a contradicting where clause compared to what some of your triggers actually see because by the time they launched, the record deviated from what was initially fetched from the table.

The first check your trigger does could be handled by a simple check constraint:

alter table failed_process
  add constraint valid_status 
  check (status in ('STARTED','READY','FAILED'));

Or, you could use an enum to enforce that at the type level.

As to locking status to prevent transition from ready once it's set to that, you still need a trigger for it, but it's worth making it a proper constraint trigger since that's what it really is. That lets you do things like defer it's enforcement using set constraints.

Potentially useful and/or related:

• What is the 'E' in RAISE NOTICE E'msg %', mymsg

although the old status is not READY in dynamically selected records by subquery

Your UPDATE query makes sure of this:

WHERE status = 'FAILED'

So the query you show cannot trigger the 2nd exception. Just not possible. An UPDATE takes a write lock on the rows to be updated. The trigger sees the same row version - and hence the same old.status value as the WHERE condition of the query. There must be some other explanation. Like:

• The trigger or trigger function are not what you assume them to be.
• The error message is triggered by a different statement.
• The executed statement is not exactly what you show.
  ...

Notably, another BEFORE UPDATE trigger with a name that sorts earlier cannot be the culprit. That could only change NEW values, but not OLD values. (Technically speaking, you can assign different values to OLD columns in a trigger function. But that's only visible within the scope of that function, barely makes any sense, and has no bearing on the OLD row in the next trigger function, which works with a fresh copy form the actual old row.)

Aside

You might replace the 1st check in your trigger function with a simpler, cheaper and safer CHECK constraint:

ALTER TABLE failed_process
  ADD CONSTRAINT status_valid CHECK (status IN ('STARTED','READY','FAILED'));

See:

• Trigger vs. check constraint

And you probably want to disallow null values (unless you already have):

ALTER TABLE failed_process
  ALTER COLUMN status SET NOT NULL;