Guard uses of IF NOT EXISTS in CREATE INDEX: changed in pg9.5 (N)

[KevinKelly25]

Prior to 9.5 CREATE UNIQUE INDEX does not support IF NOT EXISTS. This causes a problem in addRoleBaseMgmtCore.sql LN 61, when trying to create a unique index on folded RoleName.

CREATE UNIQUE INDEX IF NOT EXISTS idx_Unique_FoldedRoleName
ON ClassDB.RoleBase(ClassDB.foldPgID(RoleName));

Error:

psql:core/addRoleBaseMgmtCore.sql:62: ERROR:  syntax error at or near "NOT"
LINE 1: CREATE UNIQUE INDEX IF NOT EXISTS idx_Unique_FoldedRoleName

As seen here in the docs IF NOT EXISTS is an modifier added to the functionality of CREATE UNIQUE INDEX in 9.5

[afig]

Since DROP INDEX IF EXISTS is available as far back as at least 9.3, implementing this enhancement is relatively simple, and involves a guarded (only on server versions < 9.5) implementation of dropping the index (if it exists) and creating the index. CREATE UNIQUE INDEX IF NOT EXISTS should still be used on server versions >= 9.6.

Edit: See comment from @smurthys directly below for an issue with this implementation. The index should only be created if it does not already exist, and not dropped if it already exists.

[smurthys]

Just noting that this issue cannot be addressed using DROP INDEX IF EXISTS because that would unnecessarily drop an already existing index. Also, it does not in any way help us in conditionally creating the index.

[KevinKelly25]

After removing some issues with versioning I have found another CREATE UNIQUE INDEX IF NOT EXISTS so I ran a search to find all CREATE UNIQUE INDEX IF NOT EXISTS in the project. There are two instances, both in addUserMgmtCore.sql ln 200 and 216. This should probably also be addressed in PR #233

I apologize for not searching and mentioning additional cases of CREATE UNIQUE INDEX IF NOT EXISTS in the original issue creation.

[smurthys]

Thanks @KevinKelly25 for pointing out other places where the fix is required. I am working on the additional locations now.