Must be superuser to run disable_duplicate_key_error

Any user that has the PSEUDOSUPERUSER role active can call the DISABLE_DUPLICATE_KEY_ERROR() function.

But be careful, the PSEUDOSUPERUSER role gives a lot of power to the user!

See:
https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/DBUsersAndPrivileges/Roles/PSEUDOSUPERUSERRole.htm

Example:

dbadmin=> CREATE USER athena_ui;
CREATE USER

dbadmin=> GRANT pseudosuperuser TO athena_ui;
GRANT ROLE

dbadmin=> \c - athena_ui;
You are now connected as user "athena_ui".

dbadmin=> SELECT disable_duplicate_key_error();
ERROR 4019:  Must be superuser to run disable_duplicate_key_error

dbadmin=> SET ROLE pseudosuperuser;
SET

dbadmin=> SELECT disable_duplicate_key_error();
WARNING 3152:  Duplicate values in columns marked as UNIQUE will now be ignored for the remainder of your session or until reenable_duplicate_key_error() is called
WARNING 3539:  Incorrect results are possible. Please contact Vertica Support if unsure
 disable_duplicate_key_error
------------------------------
 Duplicate key error disabled
(1 row)

@Jim_Knicely Thank you for the quick reply, so there is no way to grant execute access to just this function?

@rajatpaliwal86 - Nope... Why not just fix the constraint violations?

@Jim_Knicely said:
@rajatpaliwal86 - Nope... Why not just fix the constraint violations?

We have a cron that deduplicates those events but it is scheduled every 1 hour, but meanwhile, if there are duplicates then UI becomes useless because of the JOIN query errors for the duplicates. We also don't want to give a lot of power to the UI user.