conflicts_grants.sh

#!/bin/bash
PSQL="psql-17 --no-psqlrc"  # Disable .psqlrc to avoid timing output
DB="test_pg_concurrent"
NUM_ROLES=5

echo "PostgreSQL Concurrent GRANT Test (${NUM_ROLES} roles)"
echo "========================================================"

cleanup() {
    $PSQL -d postgres -q -c "DROP DATABASE IF EXISTS $DB;" 2>/dev/null || true
    for i in $(seq 1 $NUM_ROLES); do
        $PSQL -d postgres -q -c "DROP ROLE IF EXISTS r${i};" 2>/dev/null || true
    done
}
cleanup
trap cleanup EXIT

echo "Setup: Creating database, ${NUM_ROLES} tables, ${NUM_ROLES} schemas, ${NUM_ROLES} roles..."
$PSQL -d postgres -q -c "CREATE DATABASE $DB;"
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "CREATE TABLE t${i} (c${i} int);"
    $PSQL -d $DB -q -c "CREATE SCHEMA s${i};"
    $PSQL -d $DB -q -c "CREATE ROLE r${i};"
done

test_grant() {
    local tmpfile=$(mktemp)
    if ! $PSQL -d $DB -q -c "$1" 2>"$tmpfile"; then
        grep -q "tuple concurrently updated" "$tmpfile" && echo "CONFLICT $1"
    else
        echo "SUCCESS $1"
    fi
    rm "$tmpfile"
}

echo ""
echo "Test 1: Concurrent GRANTs on SAME table (t1)"
echo "  Running ${NUM_ROLES} GRANTs in parallel on table t1..."
for i in $(seq 1 $NUM_ROLES); do
    (test_grant "GRANT SELECT ON t1 TO r$i;") &
done
wait


echo ""
echo "Test 2: Concurrent GRANTs on DIFFERENT tables (t1-t${NUM_ROLES})"
echo "  Running ${NUM_ROLES} GRANTs in parallel, each on different table..."
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "REVOKE ALL ON ALL TABLES IN SCHEMA public FROM r$i;" 2>&1 || true
done

for i in $(seq 1 $NUM_ROLES); do
    (test_grant "GRANT SELECT ON t$i TO r$i;") &
done
wait

echo ""
echo "Test 3: Concurrent GRANTs on SAME column (c1 on t1)"
echo "  Running ${NUM_ROLES} GRANTs in parallel on column c1 of table t1..."
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "REVOKE ALL ON ALL TABLES IN SCHEMA public FROM r$i;" 2>&1 || true
done

for i in $(seq 1 $NUM_ROLES); do
    (test_grant "GRANT SELECT (c1) ON t1 TO r$i;") &
done
wait


echo ""
echo "Test 4: Concurrent GRANTs on DIFFERENT columns"
echo "  Running ${NUM_ROLES} GRANTs in parallel, each on different column..."
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "REVOKE ALL ON ALL TABLES IN SCHEMA public FROM r$i;" 2>&1 || true
done

for i in $(seq 1 $NUM_ROLES); do
    (test_grant "GRANT SELECT (c$i) ON t$i TO r$i;") &
done
wait


echo ""
echo "Test 5: Concurrent GRANTs on SAME schema (public)"
echo "  Running ${NUM_ROLES} GRANTs in parallel on schema public..."
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "REVOKE ALL ON SCHEMA public FROM r$i;" 2>&1 || true
done

for i in $(seq 1 $NUM_ROLES); do
    (test_grant "GRANT USAGE ON SCHEMA public TO r$i;") &
done
wait

with some sort of better parallelism

#!/bin/bash
PSQL="psql-17 --no-psqlrc"
DB="test_pg_concurrent"
NUM_ROLES=10

cleanup() {
    $PSQL -d postgres -q -c "DROP DATABASE IF EXISTS $DB;" 2>/dev/null || true
    for i in $(seq 1 $NUM_ROLES); do
        $PSQL -d postgres -q -c "DROP ROLE IF EXISTS r${i};" 2>/dev/null || true
    done
}
cleanup
trap cleanup EXIT

echo "Setup..."
$PSQL -d postgres -q -c "CREATE DATABASE $DB;"
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "CREATE TABLE t${i} (c${i} int); CREATE ROLE r${i};" 2>/dev/null
done

generate_lock_id() {
    echo -n "$1" | cksum | awk '{print $1}'
}

test_grant_with_lock() {
    local sql="$1"
    local lock_key="$2"
    local barrier="$3"

    local lock_id=$(generate_lock_id "$lock_key")
    local tmpfile=$(mktemp)
    local timefile=$(mktemp)

    # Wait for barrier
    while [ -f "$barrier" ]; do sleep 0.001; done

    if $PSQL -d $DB -q -t 2>"$tmpfile" >"$timefile" <<-SQL
		BEGIN;
		SELECT CURRENT_TIMESTAMP(3);
		SELECT pg_advisory_xact_lock($lock_id);
		$sql
		COMMIT;
	SQL
    then
        local timestamp=$(head -1 "$timefile" | xargs)
        echo "✓ [$timestamp] $sql"
    else
        grep -q "tuple concurrently updated" "$tmpfile" && echo "✗ CONFLICT: $sql"
    fi
    rm "$tmpfile" "$timefile"
}

test_grant_no_lock() {
    local sql="$1"
    local barrier="$2"
    local tmpfile=$(mktemp)

    while [ -f "$barrier" ]; do sleep 0.001; done

    # Get timestamp first, then execute
    local timestamp=$($PSQL -d $DB -q -t -c "SELECT CURRENT_TIMESTAMP(3);" | xargs)

    if $PSQL -d $DB -q 2>"$tmpfile" >/dev/null -c "$sql"
    then
        echo "✓ [$timestamp] $sql"
    else
        if grep -q "tuple concurrently updated" "$tmpfile"; then
            echo "✗ [$timestamp] CONFLICT: $sql"
        fi
    fi
    rm "$tmpfile"
}

run_test() {
    local barrier=$(mktemp)
    for cmd in "$@"; do
        eval "$cmd &"
    done
    sleep 0.1
    rm "$barrier"
    wait
}

echo ""
echo "Test 1: Same table WITH locks"
echo "------------------------------------------------------------"
cmds=()
for i in $(seq 1 $NUM_ROLES); do
    cmds+=("test_grant_with_lock 'GRANT SELECT ON t1 TO r$i;' 'grant:table:$DB.public.t1' '$barrier'")
done
run_test "${cmds[@]}"

echo ""
echo "Test 2: Same column WITH locks"
echo "------------------------------------------------------------"
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "REVOKE ALL ON ALL TABLES IN SCHEMA public FROM r$i;" 2>/dev/null || true
done
cmds=()
for i in $(seq 1 $NUM_ROLES); do
    cmds+=("test_grant_with_lock 'GRANT SELECT (c1) ON t1 TO r$i;' 'grant:column:$DB.public.t1' '$barrier'")
done
run_test "${cmds[@]}"

echo ""
echo "Test 3: Same table WITHOUT locks (expect conflicts!)"
echo "------------------------------------------------------------"
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "REVOKE ALL ON ALL TABLES IN SCHEMA public FROM r$i;" 2>/dev/null || true
done
cmds=()
for i in $(seq 1 $NUM_ROLES); do
    cmds+=("test_grant_no_lock 'GRANT SELECT ON t1 TO r$i;' '$barrier'")
done
run_test "${cmds[@]}"

echo ""
echo "Done!"

./test_concurrent_simple.sh
Setup...

Test 1: Same table WITH locks
------------------------------------------------------------
✓ [2025-11-22 16:12:18.674+01] GRANT SELECT ON t1 TO r2;
✓ [2025-11-22 16:12:18.69+01] GRANT SELECT ON t1 TO r4;
✓ [2025-11-22 16:12:18.698+01] GRANT SELECT ON t1 TO r10;
✓ [2025-11-22 16:12:18.717+01] GRANT SELECT ON t1 TO r3;
✓ [2025-11-22 16:12:18.723+01] GRANT SELECT ON t1 TO r1;
✓ [2025-11-22 16:12:18.744+01] GRANT SELECT ON t1 TO r7;
✓ [2025-11-22 16:12:18.752+01] GRANT SELECT ON t1 TO r5;
✓ [2025-11-22 16:12:18.761+01] GRANT SELECT ON t1 TO r8;
✓ [2025-11-22 16:12:18.778+01] GRANT SELECT ON t1 TO r6;
✓ [2025-11-22 16:12:18.787+01] GRANT SELECT ON t1 TO r9;

Test 2: Same column WITH locks
------------------------------------------------------------
✓ [2025-11-22 16:12:19.115+01] GRANT SELECT (c1) ON t1 TO r2;
✓ [2025-11-22 16:12:19.126+01] GRANT SELECT (c1) ON t1 TO r1;
✓ [2025-11-22 16:12:19.135+01] GRANT SELECT (c1) ON t1 TO r3;
✓ [2025-11-22 16:12:19.147+01] GRANT SELECT (c1) ON t1 TO r4;
✓ [2025-11-22 16:12:19.194+01] GRANT SELECT (c1) ON t1 TO r9;
✓ [2025-11-22 16:12:19.234+01] GRANT SELECT (c1) ON t1 TO r10;
✓ [2025-11-22 16:12:19.229+01] GRANT SELECT (c1) ON t1 TO r7;
✓ [2025-11-22 16:12:19.217+01] GRANT SELECT (c1) ON t1 TO r6;
✓ [2025-11-22 16:12:19.197+01] GRANT SELECT (c1) ON t1 TO r5;
✓ [2025-11-22 16:12:19.252+01] GRANT SELECT (c1) ON t1 TO r8;

Test 3: Same table WITHOUT locks (expect conflicts!)
------------------------------------------------------------
✓ [2025-11-22 16:12:19.567+01] GRANT SELECT ON t1 TO r1;
✓ [2025-11-22 16:12:19.592+01] GRANT SELECT ON t1 TO r5;
✗ [2025-11-22 16:12:19.615+01] CONFLICT: GRANT SELECT ON t1 TO r7;
✗ [2025-11-22 16:12:19.589+01] CONFLICT: GRANT SELECT ON t1 TO r3;
✗ [2025-11-22 16:12:19.609+01] CONFLICT: GRANT SELECT ON t1 TO r6;
✓ [2025-11-22 16:12:19.635+01] GRANT SELECT ON t1 TO r8;
✗ [2025-11-22 16:12:19.588+01] CONFLICT: GRANT SELECT ON t1 TO r4;
✓ [2025-11-22 16:12:19.592+01] GRANT SELECT ON t1 TO r2;
✓ [2025-11-22 16:12:19.716+01] GRANT SELECT ON t1 TO r9;
✗ [2025-11-22 16:12:19.678+01] CONFLICT: GRANT SELECT ON t1 TO r10;

Done!