DB issue with 2024.9.6 upgrade

#1

I am attempting to make the database modifications for the upgrade to 2024.9.6. 3/4 of them work, but when I attempt this:

ALTER TABLE role_members
ADD CONSTRAINT role_members_pkey
PRIMARY KEY (rel_resource, rel_role);

I get this error:

ERROR: column “rel_resource” of relation “role_members” contains null values

How do I resolve this?

#2

@colby.ritter Did you figure this out? I’m running into this, and haven’t been able to resolve.

#3

I ran this script which it seemed like is what should be added. The app runs, but I’ve noticed I’ve lost my super admin priviliges, so something is definitely not going quite right

UPDATE role_members
SET rel_resource = rel_user::text
WHERE rel_resource IS NULL;

#4

I did something similar, except that I didn’t include the WHERE clause of your SQL statement. I modified the tables with the first two statements (ADD COLUMN rel_resource TEXT and DROP CONSTRAINT role_members_pkey), then did this:

UDPATE role_members
SET rel_resource = rel_user

Once I did that, all the null values went away and I could add the new PKEY constraint and drop the NOT NULL constraint on rel_user.

I didn’t run into any problems with my superadmin privileges that I can remember, but if you do you may need to revert to 2024.9.5, restore your backup, redo this pre-update process, and then change the version to 2024.9.6.

You may also want to do a backup of your database after you do the pre-update SQL statements, which will allow you to do a docker-compose down, then a docker-compose up -d, then restore the backup to the new version.

#5

Thanks for the response. I noticed if I actually didn’t run any of the Upgrade Instructions it actually worked! So those instructions must’ve been added to the application itself, and no longer needed. Just putting that there in case other folks in the future run into this error.