Why this trigger is raising "record "old" is not assigned yet" exception?

All we need is an easy explanation of the problem, so here it is.

I have following trigger on "Posts" table:

create
or replace function update_last_post_series () returns trigger as $ $ begin
update
  "Series"
set
  "lastPostAdded" = now()
where
  id = NEW."SeriesId"
  and NEW."SeriesId" is not null
  and (
    TG_OP = 'INSERT'
    or OLD."SeriesId" is null
    or (
      OLD.status != 'published'
      and NEW.status = 'published'
    )
  )
  and NEW."isPublished" = true
  and NEW.status = 'published';return NEW;end;$ $ language plpgsql;

create trigger update_last_post_series_trg
after
  insert
  or
update
  on "Posts" for each row execute procedure update_last_post_series();

Since few days (it seems after pg database has been updated to version 10.21 but not sure from which version) trigger above started throwing exceptions:

POST_CREATE_ERROR record "old" is not assigned yet { error: record "old" is not assigned yet
Jun 28 11:57:26     name: 'error',
Jun 28 11:57:26     length: 547,
Jun 28 11:57:26     severity: 'ERROR',
Jun 28 11:57:26     code: '55000',
Jun 28 11:57:26     detail: 'The tuple structure of a not-yet-assigned record is indeterminate.',
Jun 28 11:57:26     hint: undefined,
Jun 28 11:57:26     position: undefined,
Jun 28 11:57:26     internalPosition: undefined,
Jun 28 11:57:26     internalQuery: undefined,
Jun 28 11:57:26     where: 'SQL statement "update "Series"\n        set "lastPostAdded" = now()\n        where id = NEW."SeriesId" and NEW."SeriesId" is not null\n        and (TG_OP = \'INSERT\' or OLD."SeriesId" is null or (OLD.status != \'published\' and NEW.status = \'published\'))\n        and NEW."isPublished" = true and NEW.status = \'published\'"\nPL/pgSQL function update_last_post_series() line 3 at SQL statement',
Jun 28 11:57:26     schema: undefined,
Jun 28 11:57:26     table: undefined,
Jun 28 11:57:26     column: undefined,
Jun 28 11:57:26     dataType: undefined,
Jun 28 11:57:26     constraint: undefined,
Jun 28 11:57:26     file: 'pl_exec.c',
Jun 28 11:57:26     line: '4932',
Jun 28 11:57:26     routine: 'exec_eval_datum',
Jun 28 11:57:26     sql: 'INSERT INTO "Posts" ..... 

No idea why it is happening because it seems that condition before accessing OLD record should not let this happen:

TG_OP = 'INSERT'

or OLD."SeriesId" is null -- should never be called if TG_OP = 'INSERT' because first condition is true in this case

or (OLD.status != 'published' and NEW.status = 'published') -- should never be called if TG_OP = 'INSERT' because first condition is true in this case

What is going on here, is it possible that query executor evaluates conditions separated with OR in parallel? Why it was working for several years until now?

Using PG 10.21.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The UPDATE query in the trigger relies on subexpressions in an OR condition being evaluated left-to-right, and short-circuiting happening when a left operand is false.

The problem is that this premise is not true. The documentation explicitly warns that we can’t count on it:

Expression Evaluation Rules:

The order of evaluation of subexpressions is not defined. In
particular, the inputs of an operator or function are not necessarily
evaluated left-to-right or in any other fixed order.

[…]

Note that this is not the same as the left-to-right “short-circuiting”
of Boolean operators that is found in some programming languages.

As a consequence, it is unwise to use functions with side effects as
part of complex expressions. It is particularly dangerous to rely on
side effects or evaluation order in WHERE and HAVING clauses, since
those clauses are extensively reprocessed as part of developing an
execution plan. Boolean expressions (AND/OR/NOT combinations) in those
clauses can be reorganized in any manner allowed by the laws of
Boolean algebra.

Why didn’t it error out before? Well, execution plan can change due to the size of data changing, or to code changes in minor updates (although they’re limited to bug fixes, side effects can happen).

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply