citusdata / citusdata/citus

Unexpected different connection is opened inside a transaction

Open
#6,377 6 comments 0 reactions 0 assignees View on GitHub
bug dev_reported
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.