問題

Upgrading to Confluence 5.7.4 failed with the following error appears in the atlassian-confluence.log

[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

診断

Diagnostic Steps

Use the following queries to describe the schema of the content table:

• PostgreSQL

  \d+ content

• MySQL

  show create table CONTENT;

• Microsoft SQL Server

  sp_help 'CONTENT';

• Oracle Database:

  set 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:

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 DEFERRED

Here is an example query that you can run to check all constrains:

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.

原因

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:

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.

回避策

1. Change all the constraint to DEFERRABLE INITIALLY IMMEDIATE so the checking will be performed after each statement.
   1. Check the bug report CONF-37756 - Getting issue details... STATUS for the txt file
2. Continue with the upgrade task
   

   データベースの変更を行う場合は必ず事前にバックアップを取得してください。可能な場合は、まずステージング サーバーで SQL コマンドの変更、挿入、更新、または削除を行うようにします。