Description
Describe the problem
When we create SERIAL types and we have serial_normalization set to 'sql_sequence' we construct a new sequence. Unfortunately, the sequence we construct is in the current database rather than the database of the table. This leads to strange semantics where the table can only be written to if the current session is in the database of the sequence.
The problem here is that we use the session's database to resolve the sequence rather than the table's. In postgres, IIUC, the table's database and schema are used to resolve the sequence.
To Reproduce
root@localhost:26257/defaultdb> SET serial_normalization = 'sql_sequence';
SET
root@localhost:26257/defaultdb> CREATE DATABASE other_db;
CREATE DATABASE
root@localhost:26257/defaultdb> CREATE TABLE other_db.t (s SERIAL PRIMARY KEY, i INT);
CREATE TABLE
root@localhost:26257/defaultdb> INSERT INTO other_db.t (i) VALUES (1);
INSERT 1
root@localhost:26257/defaultdb> USE other_db;
SET
root@localhost:26257/other_db> INSERT INTO t (i) VALUES (2);
ERROR: nextval(): relation "t_s_seq" does not exist
SQLSTATE: 42P01Even worse, if I create a sequence in that database with that name, it'll get used:
root@localhost:26257/other_db> CREATE SEQUENCE t_s_seq;
CREATE SEQUENCE
root@localhost:26257/other_db> INSERT INTO t (i) VALUES (2);
ERROR: duplicate key value (s)=(1) violates unique constraint "primary"
SQLSTATE: 23505Expected behavior
The SERIAL column will work regardless of which database the current session is in. Furthermore, as we move to user-defined schemas, it should refer to a single sequence regardless of the current session's search_path.