CREATE SCHEMA alter_null_dist_key;
SET search_path TO alter_null_dist_key;
SET citus.next_shard_id TO 1720000;
SET citus.shard_count TO 32;
SET citus.shard_replication_factor TO 1;
CREATE SEQUENCE dist_seq;
CREATE TABLE null_dist_table(a bigint DEFAULT nextval('dist_seq') UNIQUE, "b" text, c bigint GENERATED BY DEFAULT AS IDENTITY);
INSERT INTO null_dist_table("b") VALUES ('test');
SELECT create_distributed_table('null_dist_table', null, colocate_with=>'none', distribution_type=>null);
NOTICE:  Copying data from local table...
NOTICE:  copying the data has completed
DETAIL:  The local data in the table is no longer visible, but is still on disk.
HINT:  To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$alter_null_dist_key.null_dist_table$$)
 create_distributed_table
---------------------------------------------------------------------

(1 row)

-- add column
ALTER TABLE null_dist_table ADD COLUMN d bigint DEFAULT 2;
SELECT * FROM null_dist_table ORDER BY c;
 a |  b   | c | d
---------------------------------------------------------------------
 1 | test | 1 | 2
(1 row)

-- alter default, set to 3
ALTER TABLE null_dist_table ALTER COLUMN d SET DEFAULT 3;
INSERT INTO null_dist_table("b") VALUES ('test');
SELECT * FROM null_dist_table ORDER BY c;
 a |  b   | c | d
---------------------------------------------------------------------
 1 | test | 1 | 2
 2 | test | 2 | 3
(2 rows)

-- drop default, see null
ALTER TABLE null_dist_table ALTER COLUMN d DROP DEFAULT;
INSERT INTO null_dist_table("b") VALUES ('test');
SELECT * FROM null_dist_table ORDER BY c;
 a |  b   | c | d
---------------------------------------------------------------------
 1 | test | 1 | 2
 2 | test | 2 | 3
 3 | test | 3 |
(3 rows)

-- cleanup the rows that were added to test the default behavior
DELETE FROM null_dist_table WHERE "b" = 'test' AND a > 1;
-- alter column type
ALTER TABLE null_dist_table ALTER COLUMN d TYPE text;
UPDATE null_dist_table SET d = 'this is a text' WHERE d = '2';
SELECT * FROM null_dist_table ORDER BY c;
 a |  b   | c |       d
---------------------------------------------------------------------
 1 | test | 1 | this is a text
(1 row)

-- drop seq column
ALTER TABLE null_dist_table DROP COLUMN a;
SELECT * FROM null_dist_table ORDER BY c;
  b   | c |       d
---------------------------------------------------------------------
 test | 1 | this is a text
(1 row)

-- add not null constraint
ALTER TABLE null_dist_table ALTER COLUMN b SET NOT NULL;
-- not null constraint violation, error out
INSERT INTO null_dist_table VALUES (NULL, 2, 'test');
ERROR:  null value in column "b" violates not-null constraint
DETAIL:  Failing row contains (null, 2, test).
CONTEXT:  while executing command on localhost:xxxxx
-- drop not null constraint and try again
ALTER TABLE null_dist_table ALTER COLUMN b DROP NOT NULL;
INSERT INTO null_dist_table VALUES (NULL, 3, 'test');
SELECT * FROM null_dist_table ORDER BY c;
  b   | c |       d
---------------------------------------------------------------------
 test | 1 | this is a text
      | 3 | test
(2 rows)

-- add exclusion constraint
ALTER TABLE null_dist_table ADD CONSTRAINT exc_b EXCLUDE USING btree (b with =);
-- rename the exclusion constraint, errors out
ALTER TABLE null_dist_table RENAME CONSTRAINT exc_b TO exc_b_1;
ERROR:  renaming constraints belonging to distributed tables is currently unsupported
-- create exclusion constraint without a name
ALTER TABLE null_dist_table ADD EXCLUDE USING btree (b with =);
-- test setting autovacuum option
ALTER TABLE null_dist_table SET (autovacuum_enabled = false);
-- test multiple subcommands
ALTER TABLE null_dist_table ADD COLUMN int_column1 INTEGER,
	DROP COLUMN d;
SELECT * FROM null_dist_table ORDER BY c;
  b   | c | int_column1
---------------------------------------------------------------------
 test | 1 |
      | 3 |
(2 rows)

-- test policy and row level security
CREATE TABLE null_dist_key_with_policy (table_user text);
INSERT INTO null_dist_key_with_policy VALUES ('user_1');
SELECT create_distributed_table('null_dist_key_with_policy', null);
NOTICE:  Copying data from local table...
NOTICE:  copying the data has completed
DETAIL:  The local data in the table is no longer visible, but is still on disk.
HINT:  To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$alter_null_dist_key.null_dist_key_with_policy$$)
 create_distributed_table
---------------------------------------------------------------------

(1 row)

-- enable rls
ALTER TABLE null_dist_key_with_policy ENABLE ROW LEVEL SECURITY;
-- user_1 will be allowed to see the inserted row
CREATE ROLE user_1 WITH LOGIN;
GRANT ALL ON SCHEMA alter_null_dist_key TO user_1;
GRANT ALL ON TABLE alter_null_dist_key.null_dist_key_with_policy TO user_1;
CREATE POLICY table_policy ON null_dist_key_with_policy TO user_1
   USING (table_user = current_user);
-- user_2 will not be allowed to see the inserted row
CREATE ROLE user_2 WITH LOGIN;
GRANT ALL ON SCHEMA alter_null_dist_key TO user_2;
GRANT ALL ON TABLE alter_null_dist_key.null_dist_key_with_policy TO user_2;
CREATE POLICY table_policy_1 ON null_dist_key_with_policy TO user_2
   USING (table_user = current_user);
\c - user_1 -
SELECT * FROM alter_null_dist_key.null_dist_key_with_policy;
 table_user
---------------------------------------------------------------------
 user_1
(1 row)

\c - user_2 -
SELECT * FROM alter_null_dist_key.null_dist_key_with_policy;
 table_user
---------------------------------------------------------------------
(0 rows)

-- postgres will always be allowed to see the row as a superuser
\c - postgres -
SELECT * FROM alter_null_dist_key.null_dist_key_with_policy;
 table_user
---------------------------------------------------------------------
 user_1
(1 row)

-- cleanup
SET client_min_messages TO ERROR;
DROP SCHEMA alter_null_dist_key CASCADE;
DROP ROLE user_1, user_2;