Marking Ticket as failed fails with DatabaseException

[fkusei]

When "Cutting failed" an encoding ticket, the tracker says:

ERROR:  null value in column "ticket_state" of relation "tbl_ticket" violates not-null constraint
DETAIL:  Failing row contains (2096, 2094, 16, null, null, 38, 1, encoding, null, null, f, 11, null, 2024-03-08 11:01:07.979056+01, 2024-03-08 13:03:13.584681+01, f, f, 68). (UPDATE "tbl_ticket" SET "ticket_state" = '', "failed" = 'FALSE', "handle_id" = '' WHERE ("tbl_ticket"."parent_id" = '2094') AND ("tbl_ticket"."ticket_type" = 'encoding') RETURNING *)

The recording gets set to "cutting failed" correctly, however the encoding ticket does not get reset to "material needed", nor does the assignee get removed.

Database status:

                                               Table "public.tbl_ticket"
           Column            |           Type           | Collation | Nullable |                Default                 
-----------------------------+--------------------------+-----------+----------+----------------------------------------
 id                          | bigint                   |           | not null | nextval('tbl_ticket_id_seq'::regclass)
 parent_id                   | bigint                   |           |          | 
 project_id                  | bigint                   |           | not null | 
 import_id                   | bigint                   |           |          | 
 title                       | text                     |           |          | 
 fahrplan_id                 | integer                  |           | not null | 
 priority                    | real                     |           | not null | 1
 ticket_type                 | enum_ticket_type         |           | not null | 
 ticket_state                | enum_ticket_state        |           | not null | 
 ticket_state_next           | enum_ticket_state        |           |          | 
 service_executable          | boolean                  |           | not null | false
 encoding_profile_version_id | bigint                   |           |          | 
 handle_id                   | bigint                   |           |          | 
 created                     | timestamp with time zone |           | not null | now()
 modified                    | timestamp with time zone |           | not null | now()
 failed                      | boolean                  |           | not null | false
 needs_attention             | boolean                  |           | not null | false
 progress                    | double precision         |           | not null | 0.0
Indexes:
    "tbl_ticket_pk" PRIMARY KEY, btree (id)
    "tbl_ticket_fahrplan_id_idx" btree (fahrplan_id)
    "tbl_ticket_handle_id_idx" btree (handle_id)
    "tbl_ticket_parent_id_idx" hash (parent_id)
    "tbl_ticket_project_id_idx" btree (project_id)
    "tbl_ticket_view_servicable_idx" btree (failed, service_executable, ticket_type)
    "unique_fahrplan_id" UNIQUE, btree (project_id, fahrplan_id) WHERE parent_id IS NULL
Check constraints:
    "tbl_ticket_encoding_profile_check" CHECK (
CASE
    WHEN ticket_type = 'encoding'::enum_ticket_type THEN encoding_profile_version_id IS NOT NULL
    ELSE true
END)
    "ticket_valid_inheritence" CHECK (check_fahrplan_inheritance(parent_id, fahrplan_id) IS TRUE)
Foreign-key constraints:
    "tbl_ticket_encoding_profile_version_fk" FOREIGN KEY (encoding_profile_version_id) REFERENCES tbl_encoding_profile_version(id) ON UPDATE CASCADE ON DELETE CASCADE
    "tbl_ticket_import_fk" FOREIGN KEY (import_id) REFERENCES tbl_import(id) ON UPDATE CASCADE ON DELETE SET NULL
    "tbl_ticket_parent_fk" FOREIGN KEY (parent_id) REFERENCES tbl_ticket(id) ON UPDATE CASCADE ON DELETE CASCADE
    "tbl_ticket_project_fk" FOREIGN KEY (project_id) REFERENCES tbl_project(id) ON UPDATE CASCADE ON DELETE CASCADE
    "tbl_ticket_state_fk" FOREIGN KEY (ticket_type, ticket_state, project_id) REFERENCES tbl_project_ticket_state(ticket_type, ticket_state, project_id) ON UPDATE RESTRICT ON DELETE RESTRICT
Referenced by:
    TABLE "tbl_comment" CONSTRAINT "tbl_comment_log_fkt_referenced_ticket_fk" FOREIGN KEY (referenced_ticket_id) REFERENCES tbl_ticket(id) ON UPDATE CASCADE ON DELETE SET NULL
    TABLE "tbl_comment" CONSTRAINT "tbl_comment_log_fkt_ticket_fk" FOREIGN KEY (ticket_id) REFERENCES tbl_ticket(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "tbl_log" CONSTRAINT "tbl_log_ticket_fk" FOREIGN KEY (ticket_id) REFERENCES tbl_ticket(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "tbl_ticket" CONSTRAINT "tbl_ticket_parent_fk" FOREIGN KEY (parent_id) REFERENCES tbl_ticket(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "tbl_ticket_property" CONSTRAINT "tbl_ticket_property_ticket_fk" FOREIGN KEY (ticket_id) REFERENCES tbl_ticket(id) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
    inherit_fahrplan_id BEFORE INSERT OR UPDATE ON tbl_ticket FOR EACH ROW EXECUTE FUNCTION inherit_fahrplan_id()
    initial_state_trigger BEFORE INSERT ON tbl_ticket FOR EACH ROW EXECUTE FUNCTION set_ticket_initial_state()
    progress_trigger AFTER INSERT OR DELETE OR UPDATE OF ticket_state, parent_id ON tbl_ticket FOR EACH ROW EXECUTE FUNCTION update_ticket_progress()
    set_encoding_ticket_state BEFORE INSERT ON tbl_ticket FOR EACH ROW EXECUTE FUNCTION set_encoding_ticket_state()
    state_trigger BEFORE INSERT OR UPDATE OF ticket_state ON tbl_ticket FOR EACH ROW EXECUTE FUNCTION update_ticket_next_state()
    update_encoding_ticket_state AFTER UPDATE ON tbl_ticket FOR EACH ROW EXECUTE FUNCTION update_encoding_ticket_state()
    valid_handle BEFORE INSERT OR UPDATE ON tbl_ticket FOR EACH ROW EXECUTE FUNCTION valid_handle()

Running postgresql 13 on debian 20.04. The database was created on postgresql 9.6 in late 2017 and was upgraded to postgresql 13 later. As far as i know, there have been no modifications to the database outside of the tracker itself.

[a-tze]

Which states are enabled in the corresponding project's settings for encoding tickets?

[fkusei]

All, apart from "postprocessed"

[a-tze]

can you tell me wether ticket with DB id 2094 is the recording ticket or the master encoding ticket? are sub-encodings used in the project?

[fkusei]

2094 is the master ticket, with sub-tickets 2095 (recording) and 2096 (encoding). There are no other tickets attached to the master ticket (we don't use sub-encodings in our setup)

[a-tze]

Could you please perform the following queries in the database:

select ticket_state_initial(16, 'encoding');
-- should return "material needed" but probably returns null, might emit a warning

[a-tze]

A probable cause would be that

https://github.com/crs-tools/tracker/blob/master/src/Application/Migrations/__2020-04-15_ticket_initial_state.sql

is missing in your database. But that depends if this already worked before and only fails in this project.

[fkusei]

tracker=# select ticket_state_initial(16, 'encoding');
 ticket_state_initial 
----------------------
 material needed
(1 row)

However it seems we are missing all migrations after https://github.com/crs-tools/tracker/blob/master/src/Application/Migrations/__2018-06-21_fix-trigger-for-progress-recalculation.sql

Is there a dedicated command to load them, or should i just psql tracker < migration.sql them?

[a-tze]

There is no mechanism or automatism. Just load them in the ascending order of the date in their filename.

Chances are good that this error is fixed by the most recent migration.

[fkusei]

Yeah, that did the trick. During the migrations, i also found an issue in our configuration management, which lead to us not getting any updates since 17392620fa2a064679ec59311d76dd9e1aa98ba4 :woozy_face:

Thanks for the quick help, issue is fixed!