Check PRIMARY KEY constraint
-- [4] Attempt to DELETE will error
SET SESSION AUTHORIZATION user_low_priv;
DELETE FROM tmp_pk_log WHERE pk_col = 100;
-- ERROR: permission denied for relation tmp_pk_log
-- [5] Call the SP with LOG action
CALL sp_check_primary_key(
SYSDATE,'tmp_pk_test','LOG','tmp_pk_log',0);
SELECT * FROM tmp_pk_log;
-- check_table | tmp_pk_test
-- check_time | 2019-10-31 00:01:33.849821
-- check_status | ERROR (LOG) - Duplicates found
-- error_count | 4
-- [6] Call the SP with FIX action and 3 rows max fix
CALL sp_check_primary_key(
SYSDATE,'tmp_pk_test','FIX','tmp_pk_log',3);
SELECT * FROM tmp_pk_log;
-- check_table | tmp_pk_test
-- check_time | 2019-10-31 00:01:34.479395
-- check_status | ERROR (FIX) - Duplicate count exceeds…
-- error_count | 4
-- [7] Call the SP with FIX action and 99 rows max fix
CALL sp_check_primary_key(
SYSDATE,'tmp_pk_test','FIX','tmp_pk_log',99);
SELECT * FROM tmp_pk_log;
-- check_table | tmp_pk_test
-- check_time | 2019-10-31 00:01:34.892679
-- check_status | SUCCESS (FIX) - Duplicates corrected.
-- error_count | 4
-- [2] Create Log Table
CREATE TABLE tmp_pk_log(
check_table VARCHAR
, check_time TIMESTAMP
, check_status VARCHAR
, error_count INT);
-- [3] Create A User
CREATE USER user_low_priv …;
-- [4] Grant Execute
GRANT EXECUTE ON PROCEDURE
sp_check_primary_key()
TO user_low_priv;
amazon-redshift-utils/src/StoredProcedures/sp_check_primary_key.sql
Checks that the declared PRIMARY KEY does
not contain any duplicates
sp_check_primary_key() attempts to remove
duplicate rows up the max_fix_rows value
Uses SECURITY DEFINER to allow an
unprivileged user to safely DELETE from a
table where they only have read privileges
-- [1] Create Example Table
CREATE TEMP TABLE tmp_pk_test (
pk_col INTEGER PRIMARY KEY
, entries INTEGER);
INSERT INTO tmp_pk_test VALUES
(100, 7001)
, (100, 7001) -- Duplicate
, (100, 7001) -- Duplicate
, (100, 7001) -- Duplicate
, (101, 20225)
, (102, 22772)
, (103, 4577);