--
-- SUBSCRIPTION
--

CREATE ROLE regress_subscription_user LOGIN SUPERUSER;
CREATE ROLE regress_subscription_user2;
CREATE ROLE regress_subscription_user3 IN ROLE pg_create_subscription;
CREATE ROLE regress_subscription_user_dummy LOGIN NOSUPERUSER;
SET SESSION AUTHORIZATION 'regress_subscription_user';


COMMIT;







COMMENT ON SUBSCRIPTION regress_testsub IS 'test subscription';
SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s;

-- Check if the subscription stats are created and stats_reset is updated
-- by pg_stat_reset_subscription_stats().
SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub';
SELECT pg_stat_reset_subscription_stats(oid) FROM pg_subscription WHERE subname = 'regress_testsub';
SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub';

-- Reset the stats again and check if the new reset_stats is updated.
SELECT stats_reset as prev_stats_reset FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub' ;
SELECT pg_stat_reset_subscription_stats(oid) FROM pg_subscription WHERE subname = 'regress_testsub';
SELECT 'prev_stats_reset' < stats_reset FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub';



-- fail - must be superuser
SET SESSION AUTHORIZATION 'regress_subscription_user2';

SET SESSION AUTHORIZATION 'regress_subscription_user';











-- fail
ALTER SUBSCRIPTION regress_testsub3 ENABLE;
ALTER SUBSCRIPTION regress_testsub3 REFRESH PUBLICATION;




ALTER SUBSCRIPTION regress_testsub4 SET (origin = any);

DROP SUBSCRIPTION regress_testsub3;
DROP SUBSCRIPTION regress_testsub4;





-- fail - invalid connection string during ALTER
ALTER SUBSCRIPTION regress_testsub CONNECTION 'foobar';


ALTER SUBSCRIPTION regress_testsub SET PUBLICATION testpub2, testpub3 WITH (refresh = false);
ALTER SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist2';
ALTER SUBSCRIPTION regress_testsub SET (slot_name = 'newname');
ALTER SUBSCRIPTION regress_testsub SET (password_required = false);
ALTER SUBSCRIPTION regress_testsub SET (run_as_owner = true);

ALTER SUBSCRIPTION regress_testsub SET (password_required = true);
ALTER SUBSCRIPTION regress_testsub SET (run_as_owner = false);

-- fail
ALTER SUBSCRIPTION regress_testsub SET (slot_name = '');

-- fail
ALTER SUBSCRIPTION regress_doesnotexist CONNECTION 'dbname=regress_doesnotexist2';
ALTER SUBSCRIPTION regress_testsub SET (create_slot = false);

-- ok
ALTER SUBSCRIPTION regress_testsub SKIP (lsn = '0/12345');


-- ok - with lsn = NONE
ALTER SUBSCRIPTION regress_testsub SKIP (lsn = NONE);

-- fail
ALTER SUBSCRIPTION regress_testsub SKIP (lsn = '0/0');


BEGIN;
ALTER SUBSCRIPTION regress_testsub ENABLE;


ALTER SUBSCRIPTION regress_testsub DISABLE;


COMMIT;

-- fail - must be owner of subscription
SET ROLE regress_subscription_user_dummy;
ALTER SUBSCRIPTION regress_testsub RENAME TO regress_testsub_dummy;
RESET ROLE;

ALTER SUBSCRIPTION regress_testsub RENAME TO regress_testsub_foo;
ALTER SUBSCRIPTION regress_testsub_foo SET (synchronous_commit = local);
ALTER SUBSCRIPTION regress_testsub_foo SET (synchronous_commit = foobar);


-- rename back to keep the rest simple
ALTER SUBSCRIPTION regress_testsub_foo RENAME TO regress_testsub;

-- ok, we're a superuser
ALTER SUBSCRIPTION regress_testsub OWNER TO regress_subscription_user2;

-- fail - cannot do DROP SUBSCRIPTION inside transaction block with slot name
BEGIN;
DROP SUBSCRIPTION regress_testsub;
COMMIT;

ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);

-- now it works
BEGIN;
DROP SUBSCRIPTION regress_testsub;
COMMIT;

DROP SUBSCRIPTION IF EXISTS regress_testsub;
DROP SUBSCRIPTION regress_testsub;




ALTER SUBSCRIPTION regress_testsub SET (binary = false);
ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);


DROP SUBSCRIPTION regress_testsub;






ALTER SUBSCRIPTION regress_testsub SET (streaming = parallel);


ALTER SUBSCRIPTION regress_testsub SET (streaming = false);
ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);


-- fail - publication already exists
ALTER SUBSCRIPTION regress_testsub ADD PUBLICATION testpub WITH (refresh = false);

-- fail - publication used more than once
ALTER SUBSCRIPTION regress_testsub ADD PUBLICATION testpub1, testpub1 WITH (refresh = false);

-- ok - add two publications into subscription
ALTER SUBSCRIPTION regress_testsub ADD PUBLICATION testpub1, testpub2 WITH (refresh = false);

-- fail - publications already exist
ALTER SUBSCRIPTION regress_testsub ADD PUBLICATION testpub1, testpub2 WITH (refresh = false);


-- fail - publication used more than once
ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub1, testpub1 WITH (refresh = false);

-- fail - all publications are deleted
ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub, testpub1, testpub2 WITH (refresh = false);

-- fail - publication does not exist in subscription
ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub3 WITH (refresh = false);

-- ok - delete publications
ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub1, testpub2 WITH (refresh = false);


DROP SUBSCRIPTION regress_testsub;



ALTER SUBSCRIPTION regress_testsub ENABLE;

-- fail - ALTER SUBSCRIPTION with refresh is not allowed in a transaction
-- block or function
BEGIN;
ALTER SUBSCRIPTION regress_testsub SET PUBLICATION mypub WITH (refresh = true);
END;

BEGIN;
ALTER SUBSCRIPTION regress_testsub REFRESH PUBLICATION;
END;

CREATE FUNCTION func() RETURNS VOID AS
$$ ALTER SUBSCRIPTION regress_testsub SET PUBLICATION mypub WITH (refresh = true) $$ LANGUAGE SQL;
SELECT func();

ALTER SUBSCRIPTION regress_testsub DISABLE;
ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
DROP SUBSCRIPTION regress_testsub;
DROP FUNCTION func;





-- we can alter streaming when two_phase enabled
ALTER SUBSCRIPTION regress_testsub SET (streaming = true);


ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
DROP SUBSCRIPTION regress_testsub;




ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
DROP SUBSCRIPTION regress_testsub;






ALTER SUBSCRIPTION regress_testsub SET (disable_on_error = true);


ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
DROP SUBSCRIPTION regress_testsub;

-- let's do some tests with pg_create_subscription rather than superuser
SET SESSION AUTHORIZATION regress_subscription_user3;



-- fail, must specify password
RESET SESSION AUTHORIZATION;
GRANT CREATE ON DATABASE REGRESSION TO regress_subscription_user3;
SET SESSION AUTHORIZATION regress_subscription_user3;


-- fail, can't set password_required=false
RESET SESSION AUTHORIZATION;
GRANT CREATE ON DATABASE REGRESSION TO regress_subscription_user3;
SET SESSION AUTHORIZATION regress_subscription_user3;


-- ok
RESET SESSION AUTHORIZATION;
GRANT CREATE ON DATABASE REGRESSION TO regress_subscription_user3;
SET SESSION AUTHORIZATION regress_subscription_user3;


-- we cannot give the subscription away to some random user
ALTER SUBSCRIPTION regress_testsub OWNER TO regress_subscription_user;

-- but we can rename the subscription we just created
ALTER SUBSCRIPTION regress_testsub RENAME TO regress_testsub2;

-- ok, even after losing pg_create_subscription we can still rename it
RESET SESSION AUTHORIZATION;
REVOKE pg_create_subscription FROM regress_subscription_user3;
SET SESSION AUTHORIZATION regress_subscription_user3;
ALTER SUBSCRIPTION regress_testsub2 RENAME TO regress_testsub;

-- fail, after losing CREATE on the database we can't rename it any more
RESET SESSION AUTHORIZATION;
REVOKE CREATE ON DATABASE REGRESSION FROM regress_subscription_user3;
SET SESSION AUTHORIZATION regress_subscription_user3;
ALTER SUBSCRIPTION regress_testsub RENAME TO regress_testsub2;

-- fail - cannot do ALTER SUBSCRIPTION SET (failover) inside transaction block
BEGIN;
ALTER SUBSCRIPTION regress_testsub SET (failover);
COMMIT;

-- ok, owning it is enough for this stuff
ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
DROP SUBSCRIPTION regress_testsub;

RESET SESSION AUTHORIZATION;
DROP ROLE regress_subscription_user;
DROP ROLE regress_subscription_user2;
DROP ROLE regress_subscription_user3;
DROP ROLE regress_subscription_user_dummy;
