How to fix 2202G: invalid_tablesample_repeat in PostgreSQL

-- Example of a TABLESAMPLE query with REPEAT
SELECT * FROM users TABLESAMPLE SYSTEM (10) REPEATABLE (12345);
-- The REPEAT parameter is 12345 in this example

-- Valid examples:
SELECT * FROM table TABLESAMPLE BERNOULLI (5) REPEATABLE (0);     -- Minimum valid
SELECT * FROM table TABLESAMPLE SYSTEM (10) REPEATABLE (1000);    -- Typical value
SELECT * FROM table TABLESAMPLE BERNOULLI (15) REPEATABLE (2147483647); -- Maximum valid

-- Invalid examples that cause the error:
SELECT * FROM table TABLESAMPLE SYSTEM (10) REPEATABLE (-1);      -- Negative
SELECT * FROM table TABLESAMPLE BERNOULLI (5) REPEATABLE (2147483648); -- Too large
SELECT * FROM table TABLESAMPLE SYSTEM (10) REPEATABLE (NULL);    -- NULL
SELECT * FROM table TABLESAMPLE BERNOULLI (5) REPEATABLE ('seed'); -- String

-- Problematic: Using a variable that might be invalid
DO $$
DECLARE
  my_seed INTEGER := -1;  -- This will cause the error
BEGIN
  PERFORM * FROM users TABLESAMPLE SYSTEM (10) REPEATABLE (my_seed);
END $$;

-- Solution: Validate and clamp the value
DO $$
DECLARE
  my_seed INTEGER := -1;
  valid_seed INTEGER;
BEGIN
  -- Ensure seed is within valid range
  valid_seed := GREATEST(0, LEAST(my_seed, 2147483647));
  PERFORM * FROM users TABLESAMPLE SYSTEM (10) REPEATABLE (valid_seed);
END $$;

-- Option 1: Use a fixed valid seed
SELECT * FROM users TABLESAMPLE SYSTEM (10) REPEATABLE (123456);

-- Option 2: Use random() with setseed() for custom reproducibility
SELECT setseed(0.12345);
SELECT * FROM users ORDER BY random() LIMIT (SELECT COUNT(*) * 0.1 FROM users);

-- Option 3: Use md5 hash for deterministic sampling
SELECT * FROM users 
WHERE (md5(id::text) < '80000000000000000000000000000000')
LIMIT (SELECT COUNT(*) * 0.1 FROM users);

-- Test with a valid REPEAT value
EXPLAIN ANALYZE 
SELECT * FROM large_table TABLESAMPLE SYSTEM (5) REPEATABLE (42);

-- Verify it returns consistent results
SELECT COUNT(*) FROM (SELECT * FROM large_table TABLESAMPLE SYSTEM (5) REPEATABLE (42)) s1;
SELECT COUNT(*) FROM (SELECT * FROM large_table TABLESAMPLE SYSTEM (5) REPEATABLE (42)) s2;
-- Both counts should be identical (within sampling variance)