"Create extension if not exists" fails on Citus workers

[thamerlan]

If non-power user tries to create extension with "IF NOT EXISTS" - DB with Citus generates exception:

must be owner of extension uuid-ossp

even if extension already exists.

Vanilla PostgreSQL allows such operation (just ignores).

Example Citus:

[myuser@mydb]SQL=> CREATE EXTENSION IF NOT EXISTS "uuid-ossp" SCHEMA public;
ERROR:  must be owner of extension uuid-ossp
CONTEXT:  while executing command on my_worker_host:my_worker_port

Logs on worker:

<2023-07-31 14:00:43 UTC> [app:citus_internal gpid=10000005497] ERROR:  must be owner of extension uuid-ossp
<2023-07-31 14:00:43 UTC> [app:citus_internal gpid=10000005497] STATEMENT:  
WITH distributed_object_data(typetext, objnames, objargs, distargumentindex, colocationid, force_delegation)  AS (VALUES ('extension', ARRAY['uuid-ossp']::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;

Vanilla PostgreSQL:

[myuser@mydb]SQL=> CREATE EXTENSION IF NOT EXISTS "uuid-ossp" SCHEMA public;
CREATE EXTENSION
Time: 0.653 ms 

Environment: PostgreSQL : (14.8 (Ubuntu 14.8-1.pgdg18.04+1) Citus: 11.3-1

[mulander]

I reproduced this locally with Citus 12.1.4 and Postgres 16.3. This indeed is a bug in Citus.

awolk=> create extension if not exists "uuid-ossp";
NOTICE:  extension "uuid-ossp" already exists, skipping
NOTICE:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 11, '2024-06-13 12:13:03.915846+02');
DETAIL:  on server a_user@localhost:9701 connectionId: 1
NOTICE:  issuing SET citus.enable_ddl_propagation TO 'off'
DETAIL:  on server a_user@localhost:9701 connectionId: 1
NOTICE:  issuing CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA  public;
DETAIL:  on server a_user@localhost:9701 connectionId: 1
NOTICE:  issuing SET citus.enable_ddl_propagation TO 'on'
DETAIL:  on server a_user@localhost:9701 connectionId: 1
NOTICE:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 11, '2024-06-13 12:13:03.915846+02');
DETAIL:  on server a_user@localhost:9702 connectionId: 3
NOTICE:  issuing SET citus.enable_ddl_propagation TO 'off'
DETAIL:  on server a_user@localhost:9702 connectionId: 3
NOTICE:  issuing CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA  public;
DETAIL:  on server a_user@localhost:9702 connectionId: 3
NOTICE:  issuing SET citus.enable_ddl_propagation TO 'on'
DETAIL:  on server a_user@localhost:9702 connectionId: 3
NOTICE:  issuing WITH distributed_object_data(typetext, objnames, objargs, distargumentindex, colocationid, force_delegation)  AS (VALUES ('extension', ARRAY['uuid-ossp']::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;
DETAIL:  on server a_user@localhost:9701 connectionId: 1
NOTICE:  issuing WITH distributed_object_data(typetext, objnames, objargs, distargumentindex, colocationid, force_delegation)  AS (VALUES ('extension', ARRAY['uuid-ossp']::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;
DETAIL:  on server a_user@localhost:9702 connectionId: 3
ERROR:  must be owner of extension uuid-ossp
CONTEXT:  while executing command on localhost:9701
NOTICE:  issuing ROLLBACK
DETAIL:  on server a_user@localhost:9701 connectionId: 1
awolk=>