Multiple assignee trigger causes Error 500s due to PG::ObjectNotInPrerequisiteState: ERROR: record "old" is not assigned yet

Created by @stanhu.

https://sentry.gitlap.com/gitlab/gitlabcom/issues/30488/

PG::ObjectNotInPrerequisiteState: ERROR:  record "old" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  PL/pgSQL function replicate_assignee_id() line 3 at IF

  lib/gitlab/database/load_balancing/connection_proxy.rb:75:in `block in write_using_load_balancer'
    connection.send(name, *args, &block)
  lib/gitlab/database/load_balancing/load_balancer.rb:66:in `block in read_write'
    yield ActiveRecord::Base.connection
  lib/gitlab/database/load_balancing/load_balancer.rb:116:in `retry_with_backoff'
    return yield
  lib/gitlab/database/load_balancing/load_balancer.rb:65:in `read_write'
    retry_with_backoff do
  lib/gitlab/database/load_balancing/connection_proxy.rb:69:in `write_using_load_balancer'
    result = @load_balancer.read_write do |connection|
...
(191 additional frame(s) were not displayed)

ActiveRecord::StatementInvalid: PG::ObjectNotInPrerequisiteState: ERROR:  record "old" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  PL/pgSQL function replicate_assignee_id() line 3 at IF
: INSERT INTO "issues" ("title", "description", "state", "project_id", "author_id", "relative_position", "iid", "created_at", "updated_at", "title_html", "description_html", "cached_markdown_version", "time_estimate") VALUES ('revision formulario dinamico', 'revision formulario dinamico', 'opened', 3134540, 1268278, 1073762823, 42, '2017-05-17 20:09:12.340595', '2017-05-17 20:09:12.340595', 'revision formulario dinamico', '<p dir="auto">revision formulario dinamico</p>', 1, 0) RETURNING "id"

ActiveRecord::StatementInvalid: PG::ObjectNotInPrerequisiteState: ERROR:  record "old" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  PL/pgSQL function replicate_assignee_id() line 3 at IF
: INSERT INTO "issues" ("title", "description", "state", "project_id", "author_id", "relative_position", "iid", "created_at", "updated_at", "title_html", "description_html", "cached_markdown_version", "time_estimate") VALUES ('revision formulario dinamico', 'revision formulario dinamico', 'opened', 3134540, 1268278, 1073762823, 42, '2017-05-17 20:09:12.340595', '2017-05-17 20:09:12.340595', 'revision formulario dinamico', '<p dir="auto">revision formulario dinamico</p>', 1, 0) RETURNING "id"

/cc: @yorickpeterse, @vsizov

added database label

This problem went away as soon as we removed the trigger.

Argh, fsck. Looking at the MR I now see what's wrong. The trigger is created using BEFORE INSERT OR UPDATE OF assignee_id, which will lead to OLD not being set. We need to adjust the procedure so that instead of this:

if OLD.assignee_id IS NOT NULL THEN

it uses this:

if OLD IS NOT NULL AND OLD.assignee_id IS NOT NULL THEN

cc @vsizov

mentioned in merge request !11461 (merged)

closed via merge request !11461 (merged)

mentioned in commit 0b946a7b

mentioned in commit b9fc5bf0