diff options
Diffstat (limited to 'src/test/regress/sql/subscription.sql')
| -rw-r--r-- | src/test/regress/sql/subscription.sql | 128 |
1 files changed, 128 insertions, 0 deletions
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql new file mode 100644 index 0000000..277b929 --- /dev/null +++ b/src/test/regress/sql/subscription.sql @@ -0,0 +1,128 @@ +-- +-- SUBSCRIPTION +-- + +CREATE ROLE regress_subscription_user LOGIN SUPERUSER; +CREATE ROLE regress_subscription_user2; +CREATE ROLE regress_subscription_user_dummy LOGIN NOSUPERUSER; +SET SESSION AUTHORIZATION 'regress_subscription_user'; + +-- fail - no publications +CREATE SUBSCRIPTION regress_testsub CONNECTION 'foo'; + +-- fail - no connection +CREATE SUBSCRIPTION regress_testsub PUBLICATION foo; + +-- fail - cannot do CREATE SUBSCRIPTION CREATE SLOT inside transaction block +BEGIN; +CREATE SUBSCRIPTION regress_testsub CONNECTION 'testconn' PUBLICATION testpub WITH (create_slot); +COMMIT; + +-- fail - invalid connection string +CREATE SUBSCRIPTION regress_testsub CONNECTION 'testconn' PUBLICATION testpub; + +-- fail - duplicate publications +CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION foo, testpub, foo WITH (connect = false); + +-- ok +CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false); + +COMMENT ON SUBSCRIPTION regress_testsub IS 'test subscription'; +SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s; + +-- fail - name already exists +CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false); + +-- fail - must be superuser +SET SESSION AUTHORIZATION 'regress_subscription_user2'; +CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION foo WITH (connect = false); +SET SESSION AUTHORIZATION 'regress_subscription_user'; + +-- fail - invalid option combinations +CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, copy_data = true); +CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, enabled = true); +CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, create_slot = true); +CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, enabled = true); +CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot = true); +CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE); +CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, enabled = false); +CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot = false); + +-- ok - with slot_name = NONE +CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false); +-- fail +ALTER SUBSCRIPTION regress_testsub3 ENABLE; +ALTER SUBSCRIPTION regress_testsub3 REFRESH PUBLICATION; + +DROP SUBSCRIPTION regress_testsub3; + +-- fail - invalid connection string +ALTER SUBSCRIPTION regress_testsub CONNECTION 'foobar'; + +\dRs+ + +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'); + +-- 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); + +\dRs+ + +BEGIN; +ALTER SUBSCRIPTION regress_testsub ENABLE; + +\dRs + +ALTER SUBSCRIPTION regress_testsub DISABLE; + +\dRs + +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); + +\dRs+ + +-- rename back to keep the rest simple +ALTER SUBSCRIPTION regress_testsub_foo RENAME TO regress_testsub; + +-- fail - new owner must be superuser +ALTER SUBSCRIPTION regress_testsub OWNER TO regress_subscription_user2; +ALTER ROLE regress_subscription_user2 SUPERUSER; +-- now it works +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); + +\dRs+ + +-- now it works +BEGIN; +DROP SUBSCRIPTION regress_testsub; +COMMIT; + +DROP SUBSCRIPTION IF EXISTS regress_testsub; +DROP SUBSCRIPTION regress_testsub; -- fail + +RESET SESSION AUTHORIZATION; +DROP ROLE regress_subscription_user; +DROP ROLE regress_subscription_user2; +DROP ROLE regress_subscription_user_dummy; |