Unexpected different connection is opened inside a transaction
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
When creating or altering a role, Citus needs to perform all operations over a single connection per node to ensure consistency.
This doesn't happen in the following case:
Run the following transaction on a new empty Citus cluster
```SQL
BEGIN;
CREATE USER new_user;
GRANT ALL ON SCHEMA public TO new_user;
CREATE TABLE dist(column1 int PRIMARY KEY, column2 int);
SELECT create_distributed_table('dist', 'column1');
ROLLBACK;
```
Explaining with comments and logs below:
```SQL
BEGIN;
SET citus.log_remote_commands TO on;
-- here we open two connections with id 1 and 2
CREATE USER new_user;
NOTICE: issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 3, '2022-09-27 10:35:26.760141+03');
DETAIL: on server postgres@localhost:9701 connectionId: 1
NOTICE: issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 3, '2022-09-27 10:35:26.760141+03');
DETAIL: on server postgres@localhost:9702 connectionId: 2
NOTICE: issuing SET citus.enable_ddl_propagation TO 'off'
DETAIL: on server postgres@localhost:9701 connectionId: 1
NOTICE: issuing SELECT worker_create_or_alter_role('new_user', 'CREATE USER new_user', 'ALTER ROLE new_user')
DETAIL: on server postgres@localhost:9701 connectionId: 1
NOTICE: issuing SET citus.enable_ddl_propagation TO 'off'
DETAIL: on server postgres@localhost:9702 connectionId: 2
NOTICE: issuing SELECT worker_create_or_alter_role('new_user', 'CREATE USER new_user', 'ALTER ROLE new_user')
DETAIL: on server postgres@localhost:9702 connectionId: 2
NOTICE: issuing SET citus.enable_ddl_propagation TO 'on'
DETAIL: on server postgres@localhost:9701 connectionId: 1
NOTICE: issuing SET citus.enable_ddl_propagation TO 'on'
DETAIL: on server postgres@localhost:9702 connectionId: 2
NOTICE: issuing WITH distributed_object_data(...) AS (...) SELECT citus_internal_add_object_metadata(...) FROM distributed_object_data;
DETAIL: on server postgres@localhost:9701 connectionId: 1
NOTICE: issuing WITH distributed_object_data(...) AS (...) SELECT citus_internal_add_object_metadata(...) FROM distributed_object_data;
DETAIL: on server postgres@localhost:9702 connectionId: 2
-- the following is not propagated
GRANT ALL ON SCHEMA public TO new_user;
-- suddenly we have a new connection opened, with id 3
-- of course this connection does not see the new user as it's not been committed yet
CREATE TABLE dist(column1 int PRIMARY KEY, column2 int);
SELECT create_distributed_table('dist', 'column1');
NOTICE: issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 3, '2022-09-27 10:35:26.760141+03');
DETAIL: on server postgres@localhost:9701 connectionId: 3
NOTICE: issuing SET citus.enable_ddl_propagation TO 'off'
DETAIL: on server postgres@localhost:9701 connectionId: 3
NOTICE: issuing CREATE SCHEMA IF NOT EXISTS public AUTHORIZATION postgres
DETAIL: on server postgres@localhost:9701 connectionId: 3
NOTICE: issuing SET ROLE postgres
DETAIL: on server postgres@localhost:9701 connectionId: 3
NOTICE: issuing GRANT USAGE ON SCHEMA public TO postgres;
DETAIL: on server postgres@localhost:9701 connectionId: 3
NOTICE: issuing GRANT CREATE ON SCHEMA public TO postgres;
DETAIL: on server postgres@localhost:9701 connectionId: 3
NOTICE: issuing RESET ROLE
DETAIL: on server postgres@localhost:9701 connectionId: 3
NOTICE: issuing SET ROLE postgres
DETAIL: on server postgres@localhost:9701 connectionId: 3
NOTICE: issuing GRANT USAGE ON SCHEMA public TO PUBLIC;
DETAIL: on server postgres@localhost:9701 connectionId: 3
NOTICE: issuing GRANT CREATE ON SCHEMA public TO PUBLIC;
DETAIL: on server postgres@localhost:9701 connectionId: 3
NOTICE: issuing RESET ROLE
DETAIL: on server postgres@localhost:9701 connectionId: 3
NOTICE: issuing SET ROLE postgres
DETAIL: on server postgres@localhost:9701 connectionId: 3
NOTICE: issuing GRANT USAGE ON SCHEMA public TO new_user;
DETAIL: on server postgres@localhost:9701 connectionId: 3
NOTICE: issuing ROLLBACK
DETAIL: on server postgres@localhost:9701 connectionId: 1
NOTICE: issuing ROLLBACK
DETAIL: on server postgres@localhost:9702 connectionId: 2
NOTICE: issuing ROLLBACK
DETAIL: on server postgres@localhost:9701 connectionId: 3
ERROR: role "new_user" does not exist
CONTEXT: while executing command on localhost:9701
ROLLBACK;
```
Contributor guide
Assessment
This issue has not been assessed yet.