問題

The Calendar does not display and the following error is visible from the logs.

Caused by: org.postgresql.util.PSQLException: ERROR: column ao_950dc3_tc_reminder_users.SUB_CALENDAR_ID does not exist

診断

Diagnostic Steps

• List all the table in the Database and check if the table which are show in the error exist. In this case, the table isao_950dc3_tc_reminder_users. Ensure that the table exist in the list and the table name is in the same casing as the one shown in the error(lower/upper case)

  \dt

• If the table exist, check the table structure  to see if the column exist and it is in the correct casing. If the column exist but it is in a different casing. Please proceed to the workaround.

  \d+ ao_950dc3_tc_reminder_users

原因

Postgres is case sensitive, therefore if the column is in a different casing, the Confluence will not be able to query it. 

回避策

To fix this issue, we will need to recreate the table to change the casing of all the tables column. Please ensure that you have backup your Confluence database before running the query below:

1. Backup all the Team Calendar Table by creating a backup table:

   CREATE TABLE "AO_950DC3_TC_CUSTOM_EV_TYPES_BACKUP" AS
   SELECT * FROM "AO_950DC3_TC_CUSTOM_EV_TYPES"
   CREATE TABLE "AO_950DC3_TC_EVENTS_BACKUP" AS
   SELECT * FROM "AO_950DC3_TC_EVENTS" ;
   CREATE TABLE "AO_950DC3_TC_DISABLE_EV_TYPES_BACKUP" AS
   SELECT * FROM "AO_950DC3_TC_DISABLE_EV_TYPES" ; 
   CREATE TABLE "AO_950DC3_TC_EVENTS_EXCL_BACKUP" AS
   SELECT * FROM "AO_950DC3_TC_EVENTS_EXCL" ; 
   CREATE TABLE "AO_950DC3_TC_EVENTS_INVITEES_BACKUP" AS
   SELECT * FROM "AO_950DC3_TC_EVENTS_INVITEES" ; 
   CREATE TABLE "AO_950DC3_TC_JIRA_REMI_EVENTS_BACKUP" AS
   SELECT * FROM "AO_950DC3_TC_JIRA_REMI_EVENTS" ; 
   CREATE TABLE "AO_950DC3_TC_REMINDER_SETTINGS_BACKUP" AS
   SELECT * FROM "AO_950DC3_TC_REMINDER_SETTINGS" ; 
   CREATE TABLE "AO_950DC3_TC_REMINDER_USERS_BACKUP" AS
   SELECT * FROM "AO_950DC3_TC_REMINDER_USERS" ; 
   CREATE TABLE "AO_950DC3_TC_SUBCALS_BACKUP" AS
   SELECT * FROM "AO_950DC3_TC_SUBCALS" ; 
   CREATE TABLE "AO_950DC3_TC_SUBCALS_IN_SPACE_BACKUP" AS
   SELECT * FROM "AO_950DC3_TC_SUBCALS_IN_SPACE" ; 
   CREATE TABLE "AO_950DC3_TC_SUBCALS_PRIV_GRP_BACKUP" AS
   SELECT * FROM "AO_950DC3_TC_SUBCALS_PRIV_GRP" ; 
   CREATE TABLE "AO_950DC3_TC_SUBCALS_PRIV_USR_BACKUP" AS
   SELECT * FROM "AO_950DC3_TC_SUBCALS_PRIV_USR" ; 
   CREATE TABLE "AO_950DC3_TC_SUBCALS_PROPS_BACKUP" AS
   SELECT * FROM "AO_950DC3_TC_SUBCALS_PROPS" ; 

2. Drop all the Team Calendar Table

   DROP TABLE AO_950DC3_TC_CUSTOM_EV_TYPES;
   DROP TABLE AO_950DC3_TC_DISABLE_EV_TYPES;
   DROP TABLE AO_950DC3_TC_EVENTS;
   DROP TABLE AO_950DC3_TC_EVENTS_EXCL;
   DROP TABLE AO_950DC3_TC_EVENTS_INVITEES;
   DROP TABLE AO_950DC3_TC_JIRA_REMI_EVENTS;
   DROP TABLE AO_950DC3_TC_REMINDER_SETTINGS;
   DROP TABLE AO_950DC3_TC_REMINDER_USERS;
   DROP TABLE AO_950DC3_TC_SUBCALS_IN_SPACE;
   DROP TABLE AO_950DC3_TC_SUBCALS;
   DROP TABLE AO_950DC3_TC_SUBCALS_PRIV_GRP;
   DROP TABLE AO_950DC3_TC_SUBCALS_PRIV_USR;
   DROP TABLE AO_950DC3_TC_SUBCALS_PROPS;

3.  Uninstall your Team Calendar 
4. Reinstall the Team Calendar so it will recreate the table

5. Insert the data from the backup table to the newly created table:

   INSERT INTO "AO_950DC3_TC_CUSTOM_EV_TYPES" 
   SELECT * FROM "AO_950DC3_TC_CUSTOM_EV_TYPES_BACKUP";
   INSERT INTO "AO_950DC3_TC_EVENTS" 
   SELECT * FROM "AO_950DC3_TC_EVENTS_BACKUP" ;
   INSERT INTO "AO_950DC3_TC_DISABLE_EV_TYPES" 
   SELECT * FROM "AO_950DC3_TC_DISABLE_EV_TYPES_BACKUP" ;  
   INSERT INTO "AO_950DC3_TC_EVENTS_EXCL" 
   SELECT * FROM "AO_950DC3_TC_EVENTS_EXCL_BACKUP" ;   
   INSERT INTO "AO_950DC3_TC_EVENTS_INVITEES" 
   SELECT * FROM "AO_950DC3_TC_EVENTS_INVITEES_BACKUP" ;  
   INSERT INTO "AO_950DC3_TC_JIRA_REMI_EVENTS" 
   SELECT * FROM "AO_950DC3_TC_JIRA_REMI_EVENTS_BACKUP" ;  
   INSERT INTO "AO_950DC3_TC_REMINDER_SETTINGS" 
   SELECT * FROM "AO_950DC3_TC_REMINDER_SETTINGS_BACKUP" ;  
   INSERT INTO "AO_950DC3_TC_REMINDER_USERS" 
   SELECT * FROM "AO_950DC3_TC_REMINDER_USERS_BACKUP" ;  
   INSERT INTO "AO_950DC3_TC_SUBCALS" 
   SELECT * FROM "AO_950DC3_TC_SUBCALS_BACKUP" ;  
   INSERT INTO "AO_950DC3_TC_SUBCALS_IN_SPACE" 
   SELECT * FROM "AO_950DC3_TC_SUBCALS_IN_SPACE_BACKUP" ;  
   INSERT INTO "AO_950DC3_TC_SUBCALS_PRIV_GRP" 
   SELECT * FROM "AO_950DC3_TC_SUBCALS_PRIV_GRP_BACKUP" ; 
   INSERT INTO "AO_950DC3_TC_SUBCALS_PRIV_USR" 
   SELECT * FROM "AO_950DC3_TC_SUBCALS_PRIV_USR_BACKUP" ;   
   INSERT INTO "AO_950DC3_TC_SUBCALS_PROPS" 
   SELECT * FROM "AO_950DC3_TC_SUBCALS_PROPS_BACKUP" ;   

6. Drop all the backup table that we previously created. 

   DROP TABLE "AO_950DC3_TC_CUSTOM_EV_TYPES_BACKUP";
   DROP TABLE "AO_950DC3_TC_DISABLE_EV_TYPES_BACKUP";
   DROP TABLE "AO_950DC3_TC_EVENTS_BACKUP";
   DROP TABLE "AO_950DC3_TC_EVENTS_EXCL_BACKUP";
   DROP TABLE "AO_950DC3_TC_EVENTS_INVITEES_BACKUP";
   DROP TABLE "AO_950DC3_TC_JIRA_REMI_EVENTS_BACKUP";
   DROP TABLE "AO_950DC3_TC_REMINDER_SETTINGS_BACKUP";
   DROP TABLE "AO_950DC3_TC_REMINDER_USERS_BACKUP";
   DROP TABLE "AO_950DC3_TC_SUBCALS_IN_SPACE_BACKUP";
   DROP TABLE "AO_950DC3_TC_SUBCALS_BACKUP";
   DROP TABLE "AO_950DC3_TC_SUBCALS_PRIV_GRP_BACKUP";
   DROP TABLE "AO_950DC3_TC_SUBCALS_PRIV_USR_BACKUP";
   DROP TABLE "AO_950DC3_TC_SUBCALS_PROPS_BACKUP";