Debian Question posted in
The oficial documentation can be found here.

A value of a NEW record seems to be kept as the OLD record between two if/elseif blocks in a PostgreSQL trigger function – Debian

I have an enum ('not created', 'in creation', 'created', 'updated', 'rejected')
and a trigger in PostgreSQL which holds some of the logic for changing the status of a record on an INSERT or UPDATE:

CREATE OR REPLACE FUNCTION set_status()
    RETURNS trigger AS
    $BODY$
    BEGIN
        IF TG_OP ILIKE 'UPDATE' THEN -- this line was added after Pavel Stehule's answer
            IF (NEW.field1 IS NULL) THEN
                NEW.status = 'not created';
            ELSEIF (NEW.field1 IS NOT NULL) THEN
                IF OLD.status IN ('not created')
                THEN
                    NEW.status = 'created'; -- point 1
                ELSEIF OLD.status IN ('created', 'updated', 'rejected') -- point 2
                AND NEW.status NOT IN ('not created')
                THEN
                    NEW.status = 'updated';
                ELSEIF NEW.status NOT IN ('not created', 'created', 'updated', 'rejected')
                THEN
                    NEW.status = 'in creation';
                ELSE
                    NEW.status = 'not created';
                END IF;
            END IF;
        END IF; -- this line was added after Pavel Stehule's answer
        RETURN NEW;
    END
    $BODY$
LANGUAGE 'plpgsql';

CREATE TRIGGER update_status_biut
    BEFORE INSERT OR UPDATE
    ON table
    FOR EACH ROW
    EXECUTE PROCEDURE set_status();

Everything works fine, except that when the status starts with a 'not created' value (it’s the default for new records), it directly switch to 'updated' on UPDATE or INSERT where it should stay on 'created' the very first time the record is processed.

To me, it sounds like where I placed the "– point 1", that the NEW.status is taking the value 'created' here, which is correct, but on the next ELSEIF block, it re-uses that value as the ‘OLD.status’ (?!), hence verifying the condition, which makes it switch to ‘updated’.

I also verified this by trying to remove the 'created' value from the list on "– point 2". In that case, the status stays gently on 'created' after an UPDATE/INSERT.

How to avoid that strange behaviour and keep the first value which has been met ('created' in that case) in the if-blocks and ‘pass’ the (following) others?

Version info (Dockerized PostgreSQL):

# psql --version
psql (PostgreSQL) 13.3 (Debian 13.3-1.pgdg100+1)

EDIT: I found the source of my troubles…

NEW and OLD pseudorelations

Source: https://www.postgresql.org/docs/9.1/rules-update.html

And I really don’t know how to correctly handle those pseudorelations in a the case of two triggers, both modifying two different pairs of columns, but which need some checks on a bit more incoming attributes. Mandatory attributes for trigger A are a subset of the whole table, same for trigger B, with a small overlap. All other features are simply not passed from the Python client application, hence they de facto take the OLD value in the database. If such value needs to be empty to fire trigger B, once they have been filled, it can no more fire, even if it needs to!

Two triggers acting on different columns

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I was getting into something much complicated that what I actually exposed on my first message. Sorry for that.

    I don't have much time to expand on the solution, but the usage of the `OF column_name[, ...] was the main part of it:

    the OF column_name option when creating a PostgreSQL trigger Source: https://www.postgresql.org/docs/13/sql-createtrigger.html

    By using this option, the trigger is firing only when updating the specified column(s), which avoid the "artifact" (well, it's not really an artifact, but it's super strange when you first meet that behaviour) of a NEW.not_specified_field (during a partial UPDATE request) which instantaneously (i.e. 'on-the-fly', i.e. prior to its usage inside the trigger function) get assigned the corresponding OLD value from the DB (which you may not want if it's necessary to check for an empty incoming value!).

    So that I was able to drop messy checks I had done in the trigger function. It's much more cleaner now.

    This post also helped me figuring it out: https://stackoverflow.com/a/8762116/6630397


  2. 0

    The behaviour of NEW and OLD variables depends on used PostgreSQL version. NEW is well defined for INSERT or UPDATE event. OLD is well defined for UPDATE and DELETE. When you use OLD in INSERT event, then you got an exception on older Postgreses, and today you will get NULL.

    Your design is not good. For this case you should to use implicit variable TG_OP, that holds strings: INSERT or UPDATE or DELETE, and then you can set correct status.

    Login or Signup to reply.
  3. 0

    You may be unaware of basic SQL UPDATE mechanics. The manual:

    columns not explicitly modified retain their previous values.

    So all fields of NEW in a trigger function which have not been updated explicitly or by another, earlier trigger retain their original value – are identical to their OLD counterpart.

    Also, this interpretation of yours is off target:

    but on the next ELSEIF block, it re-uses that value as the OLD.status (?!)

    That’s not how it works. In an IF statement only one branch can be executed. After that, control jumps to END IF. The manual:

    The IF conditions are tested successively until the first one that
    is true is found. Then the associated statement(s) are executed,
    after which control passes to the next statement after END IF. (Any
    subsequent IF conditions are not tested.) If none of the IF
    conditions is true, then the ELSE block (if any) is executed.

    That aside, since your (updated) trigger does nothing for the INSERT case anyway, simplify:

    CREATE OR REPLACE FUNCTION tbl_set_status()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.field1 IS NULL THEN
      NEW.status = 'not created';
   ELSE
      IF OLD.status = 'not created' THEN
         NEW.status = 'created'; -- point 1
      ELSIF OLD.status IN ('created', 'updated', 'rejected') -- point 2
      AND NEW.status <> 'not created' THEN
         NEW.status = 'updated';
      ELSIF NEW.status NOT IN ('not created', 'created', 'updated', 'rejected') THEN
         NEW.status = 'in creation';
      ELSE  --  redundant catch-all
         NEW.status = 'not created';
      END IF;
   END IF;
   
   RETURN NEW;
END
$func$;

CREATE TRIGGER update_status_biut
BEFORE UPDATE ON tbl
FOR EACH ROW EXECUTE FUNCTION tbl_set_status();

    Consider a separate trigger (and function) if you need an INSERT trigger for additional stuff.

    Aside: your automatic promotion from 'rejected' to 'updated' seems odd.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search