Upgrade failed with the error message: "ERROR: cannot ALTER TABLE "content" because it has pending trigger events"
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Problem
Upgrading to Confluence 5.7.4 failed with the following error appears in the atlassian-confluence.log
1
[ALTER TABLE CONTENT add constraint fk6382c05917d4a070 foreign key (PREVVER) references CONTENT(CONTENTID)]; SQL state [55006]; error code [0]; ERROR: cannot ALTER TABLE "content" because it has pending trigger events; nested exception is org.postgresql.util.PSQLException: ERROR: cannot ALTER TABLE "content" because it has pending trigger events
Diagnosis
Diagnostic Steps
Use the following queries to describe the schema of the content table:
PostgreSQL:
1\d+ contentMySQL:
1show create table CONTENT;Microsoft SQL Server:
1sp_help 'CONTENT';Oracle Database:
1 2set long 10000; select dbms_metadata.get_ddl('TABLE','CONTENT') from dual;
Check if your tables has the constraints "DEFERRABLE INITIALLY DEFERRED". If yes, proceed to resolution, for example in PostgreSQL it will look like:
1
2
3
4
5
Foreign-key constraints:
"fk6382c05917d4a070" FOREIGN KEY (prevver) REFERENCES content(contentid) DEFERRABLE INITIALLY DEFERRED
"fk6382c05974b18345" FOREIGN KEY (parentid) REFERENCES content(contentid) DEFERRABLE INITIALLY DEFERRED
"fk6382c0598c38fbea" FOREIGN KEY (pageid) REFERENCES content(contentid) DEFERRABLE INITIALLY DEFERRED
"fk6382c059b2dc6081" FOREIGN KEY (spaceid) REFERENCES spaces(spaceid) DEFERRABLE INITIALLY DEFERREDHere is an example query that you can run to check all constrains:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT tc.constraint_name,
tc.constraint_type,
tc.table_name,
kcu.column_name,
tc.is_deferrable,
tc.initially_deferred,
rc.match_option AS match_type,
rc.update_rule AS on_update,
rc.delete_rule AS on_delete,
ccu.table_name AS references_table,
ccu.column_name AS references_field
FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name
LEFT JOIN information_schema.referential_constraints rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name
LEFT JOIN information_schema.constraint_column_usage ccu
ON rc.unique_constraint_catalog = ccu.constraint_catalog
AND rc.unique_constraint_schema = ccu.constraint_schema
AND rc.unique_constraint_name = ccu.constraint_name
WHERE lower(tc.constraint_type) in ('foreign key');⚠️ The column "initially_deferred" should be "NO" for all constraint.
Cause
By default, the postgres uses set all the constraint as DEFERRABLE INITIALLY IMMEDIATE. The difference between the two type of constraint are as follows:
DEFERRABLE INITIALLY DEFERRED - Check the constraint be deferred to just before each transaction commit.
DEFERRABLE INITIALLY IMMEDIATE - Check the constraint immediately for each statement
In this case, the issue occurs because there is a single SQL query transaction in Confluence that try to perform two different statement.
For example:
1
2
3
4
5
6
7
BEGIN;
UPDATE content
SET content_id = (SELECT attachmentid FROM attachments);
ALTER TABLE content
ADD CONSTRAINT fk6382c05917d4a070
FOREIGN KEY key (PREVVER) references CONTENT(CONTENTID);
COMMIT;In this example transaction we can see that Confluence is trying to perform 2 statement (update & alter). Note that the COMMIT statement is at the end of the transaction. A transaction is complete by performing the commit action. If the constraint is set as *DEFERRABLE INITIALLY IMMEDIATE*, the constraint will be check after each of the statement execute.
However, because *DEFERRABLE INITIALLY DEFERRED* the constraint will not be checked before the transaction complete which is when it is committed. In this case, the pending trigger would be the constraint checking. You are not allowed to update,insert,alter or any other query that will modify the table without executing all the trigger. Therefore the alter fail which caused the issue that you are facing.
Solution
Workaround
Change all the constraint to DEFERRABLE INITIALLY IMMEDIATE so the checking will be performed after each statement.
Check the bug report https://jira.atlassian.com/browse/CONFSERVER-37756 for the txt file
Continue with the upgrade task
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Was this helpful?