Foreign key creation allowed in 'read repeatable' mode but inserts fail

[nspiegelberg]

Using pgbench for SQL testing, I am not able to INSERT rows after adding a foreign key to a table in 'read committed' (default) mode. However, I am able to add a foreign key after the INSERT. Talking with @ndeodhar , it seems like the SQL processor should not allow ALTER ADD CONSTRAINT FOREIGN KEY in 'read committed' mode at all

postgres=# alter database postgres set default_transaction_isolation='read committed';
ALTER DATABASE
postgres=# \c
You are now connected to database "postgres" as user "postgres".
postgres=# select current_setting('transaction_isolation');
 current_setting
-----------------
 read committed
(1 row)

This flow allows the addition of a foreign key, but not the addition of new rows..

14:02 $ ./build/latest/postgres/bin/pgbench -i -Id
dropping old tables...
done.
Nicolass-MacBook-Pro.local:/code/yugabyte-db [pgbench_alter L|…6]
14:02 $ ./build/latest/postgres/bin/pgbench -i -Itp
creating tables (with primary keys)...
done.
Nicolass-MacBook-Pro.local:/code/yugabyte-db [pgbench_alter L|…6]
14:05 $ ./build/latest/postgres/bin/pgbench -i -If
creating foreign keys...
done.
Nicolass-MacBook-Pro.local:~/code/yugabyte-db [pgbench_alter L|…6]
14:05 $ ./build/latest/postgres/bin/pgbench -i -Ig
generating data...
ERROR: Operation only supported in SERIALIZABLE isolation level
HINT: See #1199. Click '+' on the description to raise its priority

This flow has no errors until the 2nd generation:

dropping old tables...
done.
Nicolass-MacBook-Pro.local:/code/yugabyte-db [pgbench_alter L|…6]
14:11 $ ./build/latest/postgres/bin/pgbench -i -Itp
creating tables (with primary keys)...
done.
Nicolass-MacBook-Pro.local:/code/yugabyte-db [pgbench_alter L|…6]
14:11 $ ./build/latest/postgres/bin/pgbench -i -Ig
generating data...
100000 of 100000 tuples (100%) done (elapsed 1.74 s, remaining 0.00 s)
done.
Nicolass-MacBook-Pro.local:~/code/yugabyte-db [pgbench_alter L|…6]
14:11 $ ./build/latest/postgres/bin/pgbench -i -If
creating foreign keys...
done.
14:13 $ time ./build/latest/postgres/bin/pgbench -i -Ig
generating data...
ERROR: Operation only supported in SERIALIZABLE isolation level
HINT: See #1199. Click '+' on the description to raise its priority

[ndeodhar]

The discrepancy is because postgres uses FOR SHARE locking while inserting FKs. However, looks like it doesn't use locking while checking FK constraints when it's added after the fact.

Since we are adding support for locking in the next 1-2 weeks, there's no action needed here. I'll close this once we close #1199 .