This is an internal Citus function can only be used in a distributed transaction

[HelloWorld-9527]

After I remove all node and plan to add node (use another ip and port but still the same server) I get a mistake. Although I removed the database and recreated database and citus extension, I still get the error

test_citus=# select master_add_node('10.50.208.240', 22012);
WARNING:  This is an internal Citus function can only be used in a distributed transaction
^CCancel request sent
ERROR:  failure on connection marked as essential: 10.50.208.240:22012
test_citus=# select citus_add_node('10.50.208.240', 22012);
WARNING:  This is an internal Citus function can only be used in a distributed transaction
ERROR:  failure on connection marked as essential: 10.50.208.240:22012

Can someone tell me what's going on or how to fix it？this might be a small problem thank you!

[onderkalaci]

but still the same server

Maybe drop & create Citus extension on this server could help.

Also, can you re-run with:

SET citus.log_remote_commands TO ON;
SELECT citus_add_node...

[HelloWorld-9527]

SET citus.log_remote_commands TO ON; SELECT citus_add_node...

SET citus.log_remote_commands TO ON;
SET
test_citus=# select citus_add_node('10.50.208.240', 44012);
NOTICE:  issuing SELECT metadata ->> 'server_id' AS server_id FROM pg_dist_node_metadata
DETAIL:  on server [email protected]:44012 connectionId: 1
NOTICE:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 5, '2022-08-30 16:56:09.113311+08');
DETAIL:  on server [email protected]:44012 connectionId: 1
NOTICE:  issuing UPDATE pg_dist_local_group SET groupid = 5
DETAIL:  on server [email protected]:44012 connectionId: 1
NOTICE:  issuing SELECT pg_catalog.worker_drop_sequence_dependency(logicalrelid::regclass::text) FROM pg_dist_partition;SELECT worker_drop_shell_table(logicalrelid::regclass::text) FROM pg_dist_partition;SET citus.enable_ddl_propagation TO 'off';SELECT worker_create_or_alter_role('root', 'CREATE ROLE root SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION BYPASSRLS CONNECTION LIMIT -1 PASSWORD ''md5d5ae7ce831be4eb906cda2ac14d87031'' VALID UNTIL ''infinity''', 'ALTER ROLE root SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION BYPASSRLS CONNECTION LIMIT -1 PASSWORD ''md5d5ae7ce831be4eb906cda2ac14d87031'' VALID UNTIL ''infinity''');SELECT worker_create_or_alter_role('test_citus', 'CREATE ROLE test_citus NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN NOREPLICATION NOBYPASSRLS CONNECTION LIMIT -1 PASSWORD ''md5b68e4650c7e9d97071419daee5f33fbf'' VALID UNTIL ''infinity''', 'ALTER ROLE test_citus NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN NOREPLICATION NOBYPASSRLS CONNECTION LIMIT -1 PASSWORD ''md5b68e4650c7e9d97071419daee5f33fbf'' VALID UNTIL ''infinity''');ALTER DATABASE test_citus OWNER TO test_citus;;SET citus.enable_ddl_propagation TO 'on';SET citus.enable_ddl_propagation TO 'off';SET citus.enable_ddl_propagation TO 'on'
DETAIL:  on server [email protected]:44012 connectionId: 1
NOTICE:  issuing UPDATE pg_dist_node SET hasmetadata = TRUE WHERE nodeid = 5
DETAIL:  on server [email protected]:44012 connectionId: 1
NOTICE:  issuing UPDATE pg_dist_local_group SET groupid = 5;DELETE FROM pg_dist_node;INSERT INTO pg_dist_node (nodeid, groupid, nodename, nodeport, noderack, hasmetadata, metadatasynced, isactive, noderole, nodecluster, shouldhaveshards) VALUES (5, 5, '10.50.208.240', 44012, 'default', TRUE, TRUE, TRUE, 'primary'::noderole, 'default', TRUE)
DETAIL:  on server [email protected]:44012 connectionId: 1
NOTICE:  issuing SET citus.enable_ddl_propagation TO 'off';DELETE FROM pg_dist_partition;DELETE FROM pg_dist_shard;DELETE FROM pg_dist_placement;DELETE FROM pg_catalog.pg_dist_object;DELETE FROM pg_catalog.pg_dist_colocation;WITH distributed_object_data(typetext, objnames, objargs, distargumentindex, colocationid, force_delegation)  AS (VALUES ('role', ARRAY['root']::text[], ARRAY[]::text[], -1, 0, false), ('database', ARRAY['test_citus']::text[], ARRAY[]::text[], -1, 0, false)) SELECT citus_internal_add_object_metadata(typetext, objnames, objargs, distargumentindex::int, colocationid::int, force_delegation::bool) FROM distributed_object_data;;SET citus.enable_ddl_propagation TO 'on'
DETAIL:  on server [email protected]:44012 connectionId: 1
WARNING:  This is an internal Citus function can only be used in a distributed transaction
ERROR:  failure on connection marked as essential: 10.50.208.240:44012
test_citus=# 

The situation is like this, the basis database system is a high-availability active-standby cluster，

so I have to use the constant ip, and add node by another constant ip.....

[HelloWorld-9527]

but still the same server

Maybe drop & create Citus extension on this server could help.

Also, can you re-run with:

SET citus.log_remote_commands TO ON;
SELECT citus_add_node...

I got a series of errors，and the situation is as above. can you give me some advice?

[marcocitus]

Is there anything like pgbouncer in between the coordinator and the worker nodes?