Environment

OpenText Filr 5 / 23.x

Situation

Filr 'appserver.log' contains entries like:

2023-12-31T21:04:15,336 ERROR [Sitescape_Worker-12] [org.hibernate.util.JDBCExceptionReporter] - ERROR: update or delete on table "ss_forums" violates foreign key constraint "fkdf668a515b285d8d" on table "ss_forums"

2023-12-31T21:04:25,121 ERROR [Sitescape_Worker-10] [org.hibernate.util.JDBCExceptionReporter] - ERROR: update or delete on table "ss_forums" violates foreign key constraint "fkdf668a511cacb780" on table "ss_forums"

Cause

[Net] folder delete action failed in the past.

Resolution

Delete all records which are related to the affected record from table 'ss_forums' reported in the error message:
1. Take a reliable backup of Filr database.

2. Get a list of all affected records for table 'ss_forums':

grep 'is still referenced from table "ss_forums' /var/opt/novell/tomcat-filr/logs/appserver.log | cut -d'(' -f3 | cut -d')' -f1 | sort -g | uniq | sed 's/$/,/g' | tr -d '\n' ; echo

Output of above command will provide IDs of affected records. Use them in below queries for replacing of "id1, id2, id3" at next step. It could be useful to not put all IDs at once, but split them in sets of 10 to 20 IDs.

Note: the same IDs could be retrieved from database log as well:

grep 'is still referenced from table "ss_forums' /path/to/postgresql.log | cut -d'(' -f3 | cut -d')' -f1 | sort -g | uniq | sed 's/$/,/g' |  tr -d '\n' ; echo

3. There are two options to address the observed issue:
"Smooth" option will mark all subfolders from deleted folder(s) to be deleted as well. Upcoming folders clean action (runs every 10 minutes) will remove all metadata (records in linked tables) related to the subfolders.
This option is strongly recommended for the first try.

-- "Smooth" option --
-- PotgreSQL query version --
WITH RECURSIVE folders AS (SELECT id FROM ss_forums WHERE id IN (id1, id2, id3) 
UNION SELECT subfolder.id FROM ss_forums subfolder 
INNER JOIN folders f ON f.id = subfolder.parentbinder) 
UPDATE ss_forums SET deleted = true, topfolder = NULL, parentbinder = NULL 
WHERE id IN (SELECT id FROM folders);

-- MSSQL query version -- 
WITH folders AS (SELECT child.id, child.parentbinder FROM SS_Forums child 
WHERE parentBinder IN (id1, id2, id3) UNION ALL SELECT child.id, child.parentbinder 
FROM folders parent 
INNER JOIN SS_Forums child ON child.parentbinder = parent.id) 
UPDATE SS_Forums SET deleted = 1, topfolder = NULL, parentbinder = NULL 
WHERE id IN (SELECT id FROM folders);

"Brutal" option consists of a few SQL queries and will delete all records which are causing the issue.
This option is recommended if the "Smooth" option didn't help or there is a need of more control.

-- "Brutal" option --
-- PotgreSQL query 1 --
WITH RECURSIVE folders AS (SELECT id FROM ss_forums WHERE id IN (id1, id2, id3) UNION SELECT c.id FROM ss_forums c INNER JOIN folders f ON c.parentbinder = f.id) 
DELETE FROM ss_customattributes WHERE ownerid IN (SELECT id FROM folders) OR owningbinderid IN (SELECT id FROM folders);

-- PostgreSQL query 2 --
WITH RECURSIVE folders AS (SELECT id FROM ss_forums WHERE id IN (id1, id2, id3) UNION SELECT c.id FROM ss_forums c INNER JOIN folders f ON c.parentbinder = f.id) 
DELETE FROM ss_attachments WHERE ownerid IN (SELECT id FROM folders) OR owningbinderid IN (SELECT id FROM folders);

-- PostgreSQL query 3 --
WITH RECURSIVE folders AS (SELECT id FROM ss_forums WHERE id IN (id1, id2, id3) UNION SELECT c.id FROM ss_forums c INNER JOIN folders f ON c.parentbinder = f.id) 
DELETE FROM ss_folderentries WHERE parentbinder IN (SELECT id FROM folders);

-- PostgreSQL query 4 (likely 0 records will be deleted) --
WITH RECURSIVE folders AS (SELECT id FROM ss_forums WHERE id IN (id1, id2, id3) UNION SELECT c.id FROM ss_forums c INNER JOIN folders f ON c.parentbinder = f.id) 
DELETE FROM ss_definitionmap WHERE binder IN (SELECT id FROM folders);

-- PostgreSQL query 5 --
WITH RECURSIVE folders AS (SELECT id FROM ss_forums WHERE id IN (id1, id2, id3) UNION SELECT c.id FROM ss_forums c INNER JOIN folders f ON c.parentbinder = f.id) 
DELETE FROM ss_forums WHERE id IN (SELECT id FROM folders);

Note1: Make sure to execute the queries in given order
Note2: The first two queries might take significant time to proceed. To get an approximate time estimation you can change them into SELECT COUNT(*) queries. Counting the number of records will take pretty similar time.
Note3: The execution of DELETE queries might take significant time to proceed, it is strongly recommended to execute the queries from command line. Here is the command for terminal console of database server:

psql -U db_username -d db_name -c "WITH RECURSIVE folders AS (SELECT id FROM ss_forums WHERE id IN (id1, id2, id3) UNION SELECT subfolder.id FROM ss_forums subfolder INNER JOIN folders f ON f.id = subfolder.parentbinder) 
UPDATE ss_forums SET deleted = true, topfolder = NULL, parentbinder = NULL WHERE id IN (SELECT id FROM folders);"

which with "real" values could look like (an example):

psql -U filr -d filr -c "WITH RECURSIVE folders AS (SELECT id FROM ss_forums WHERE id IN (120448, 122018, 220123, 333445) UNION SELECT subfolder.id FROM ss_forums subfolder INNER JOIN folders f ON f.id = subfolder.parentbinder) 
UPDATE ss_forums SET deleted = true, topfolder = NULL, parentbinder = NULL WHERE id IN (SELECT id FROM folders);"

After all records are successfully deleted, it is expected PostgreSQL database engine to reclaim disk space within 24 hours. It is possible to push the reclaim of the disk space straight away by VACUUM command:

psql -U db_username -d db_name "VACUUM"

In event of failure restore the database.
Contact with OpenText technical support.

Access article on support portal