duplicate key value violates unique constraint "unique_constraints_pkey"
Use-case: SSO for clients using our company applications.
Environment: Self Hosting
Version: v3.1.0 / v3.3.2
(Encountered on v3.1.0, upgrading to v3.3.2 did not fix what looks like a data issue)
Stack: Zitadel running on Amazon ECS w/ Postgres DB. Using the Typescript UI Frontend. Connected to a handful of company Apps using GoLang/PHP
What you expected to happen: Creating users and adding grants via the Zitadel UI or API endpoints is successful when the users / grants do not exist.
What went wrong: Out of thousands of users created, there have been a handful of users that we experience two different but similar errors when either attempting to create their account or add grants to their account. I don't have the log records for the first time the users were attempted to be created, only subsequent attempts.
When searching for specific users in Zitadel UI or via API, no matching users are found.
When searching for specific grants in Zitadel UI or via API, no matching grants are found.
Investigating the errors, I found records existing in the
eventstore.unique_constraints table, though the matching records do not exist within the projections.users14 and projections.user_grants5 tables.
To fix the data and allow these users to be created, I can delete the records from the eventstore.unique_constraints table. However, I am unsure if there are other records that I would also need to clean up from other tables. Please advise.
Attachments:
2 Replies
Not sure how you get it in the interface, but in the API setting authorizations and the. In v1 setting authorizations a second time generates the 409 autos already exist. There is a second Update auth via PUT instead of POST that has to be used.
With v2 I haven’t check again since 4.0 just released yesterday/today.
When I check the API to see if the user exists, it doesn't return anything, as-if the user does not exist. The user was never successfully created all the way, only the record in the unique_constraints table exists.
I am using POST
v2/users/human to create the users.
Can I safely delete the username and user_grant records in the unique_constraints table?
Then I would recreate the user using POST