benoittgt/conflicts_grants.sh

## 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
	#!/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
No results found