Skip to content

Instantly share code, notes, and snippets.

@onderkalaci
Created November 24, 2021 12:49
Show Gist options
  • Save onderkalaci/9689f46727f62220a9761315d454db1d to your computer and use it in GitHub Desktop.
Save onderkalaci/9689f46727f62220a9761315d454db1d to your computer and use it in GitHub Desktop.
-- prevent sending types /schema
SET citus.enable_ddl_propagation TO OFF;
CREATE SCHEMA local_schema;
SET search_path TO local_schema;
CREATE TYPE dup_result AS (f1 int, f2 text);
CREATE FUNCTION dup(int) RETURNS dup_result
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
LANGUAGE SQL;
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE local_table(a int, b mood, c dup_result);
CREATE SEQUENCE my_seq;
-- show that nothing is marked as dist object
select pg_identify_object_as_address(classid, objid, objsubid) from citus.pg_dist_object ;
SET client_min_messages TO ERROR;
CREATE USER test_user;
GRANT ALL ON SCHEMA local_schema TO test_user;
SELECT run_command_on_workers($$CREATE USER test_user;$$);
SET ROLE test_user;
SET search_path TO local_schema;
CREATE TABLE dist_table(a int, b mood, c dup_result, d int DEFAULT nextval('my_seq'));
SELECT create_distributed_table('local_schema.dist_table', 'a');
SELECT create_distributed_function('dup(int)');
-- ensure we can load data
INSERT INTO dist_table VALUES (1, 'sad', (1,'onder')::dup_result),
(2, 'ok', (1,'burak')::dup_result),
(3, 'happy', (1,'marco')::dup_result);
SELECT a, b, c , d FROM dist_table ORDER BY 1,2,3,4;
SET ROLE onderkalaci;
-- show that types are now distributed
select pg_identify_object_as_address(classid, objid, objsubid) from citus.pg_dist_object ;
-- show that schema is owned by the superuser
SELECT rolname FROM pg_roles JOIN pg_namespace ON(pg_namespace.nspowner = pg_roles.oid) WHERE nspname = 'local_schema';
SELECT run_command_on_workers($$SELECT rolname FROM pg_roles JOIN pg_namespace ON(pg_namespace.nspowner = pg_roles.oid) WHERE nspname = 'local_schema';$$);
-- show that types are owned by the superuser
SELECT DISTINCT(rolname) FROM pg_roles JOIN pg_type ON(pg_type.typowner = pg_roles.oid) WHERE typname IN ('dup_result', 'mood');
SELECT run_command_on_workers($$SELECT DISTINCT(rolname) FROM pg_roles JOIN pg_type ON(pg_type.typowner = pg_roles.oid) WHERE typname IN ('dup_result', 'mood');$$);
-- show that table is owned by the test_user
SELECT rolname FROM pg_roles JOIN pg_class ON(pg_class.relowner = pg_roles.oid) WHERE relname = 'dist_table';
SELECT run_command_on_workers($$SELECT rolname FROM pg_roles JOIN pg_class ON(pg_class.relowner = pg_roles.oid) WHERE relname = 'dist_table'$$);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment