| -- |
| -- 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'; |
| -- fail - no publications |
| CREATE SUBSCRIPTION regress_testsub CONNECTION 'foo'; |
| ERROR: syntax error at or near ";" |
| LINE 1: CREATE SUBSCRIPTION regress_testsub CONNECTION 'foo'; |
| ^ |
| -- fail - no connection |
| CREATE SUBSCRIPTION regress_testsub PUBLICATION foo; |
| ERROR: syntax error at or near "PUBLICATION" |
| LINE 1: 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); |
| ERROR: CREATE SUBSCRIPTION ... WITH (create_slot = true) cannot run inside a transaction block |
| COMMIT; |
| -- fail - invalid connection string |
| CREATE SUBSCRIPTION regress_testsub CONNECTION 'testconn' PUBLICATION testpub; |
| ERROR: invalid connection string syntax: missing "=" after "testconn" in connection info string |
| -- fail - duplicate publications |
| CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION foo, testpub, foo WITH (connect = false); |
| ERROR: publication name "foo" used more than once |
| -- ok |
| CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false); |
| WARNING: subscription was created, but is not connected |
| HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription. |
| COMMENT ON SUBSCRIPTION regress_testsub IS 'test subscription'; |
| SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s; |
| obj_description |
| ------------------- |
| test subscription |
| (1 row) |
| |
| -- 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'; |
| subname | stats_reset_is_null |
| -----------------+--------------------- |
| regress_testsub | t |
| (1 row) |
| |
| SELECT pg_stat_reset_subscription_stats(oid) FROM pg_subscription WHERE subname = 'regress_testsub'; |
| pg_stat_reset_subscription_stats |
| ---------------------------------- |
| |
| (1 row) |
| |
| SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub'; |
| subname | stats_reset_is_null |
| -----------------+--------------------- |
| regress_testsub | f |
| (1 row) |
| |
| -- 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' \gset |
| SELECT pg_stat_reset_subscription_stats(oid) FROM pg_subscription WHERE subname = 'regress_testsub'; |
| pg_stat_reset_subscription_stats |
| ---------------------------------- |
| |
| (1 row) |
| |
| SELECT :'prev_stats_reset' < stats_reset FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub'; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| -- fail - name already exists |
| CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false); |
| ERROR: subscription "regress_testsub" already exists |
| -- fail - must be superuser |
| SET SESSION AUTHORIZATION 'regress_subscription_user2'; |
| CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION foo WITH (connect = false); |
| ERROR: permission denied to create subscription |
| DETAIL: Only roles with privileges of the "pg_create_subscription" role may create subscriptions. |
| 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); |
| ERROR: connect = false and copy_data = true are mutually exclusive options |
| CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, enabled = true); |
| ERROR: connect = false and enabled = true are mutually exclusive options |
| CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, create_slot = true); |
| ERROR: connect = false and create_slot = true are mutually exclusive options |
| CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, enabled = true); |
| ERROR: slot_name = NONE and enabled = true are mutually exclusive options |
| CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, enabled = false, create_slot = true); |
| ERROR: slot_name = NONE and create_slot = true are mutually exclusive options |
| CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE); |
| ERROR: subscription with slot_name = NONE must also set enabled = false |
| CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, enabled = false); |
| ERROR: subscription with slot_name = NONE must also set create_slot = false |
| CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot = false); |
| ERROR: subscription with slot_name = NONE must also set enabled = false |
| -- ok - with slot_name = NONE |
| CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false); |
| WARNING: subscription was created, but is not connected |
| HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription. |
| -- fail |
| ALTER SUBSCRIPTION regress_testsub3 ENABLE; |
| ERROR: cannot enable subscription that does not have a slot name |
| ALTER SUBSCRIPTION regress_testsub3 REFRESH PUBLICATION; |
| ERROR: ALTER SUBSCRIPTION ... REFRESH is not allowed for disabled subscriptions |
| -- fail - origin must be either none or any |
| CREATE SUBSCRIPTION regress_testsub4 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false, origin = foo); |
| ERROR: unrecognized origin value: "foo" |
| -- now it works |
| CREATE SUBSCRIPTION regress_testsub4 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false, origin = none); |
| WARNING: subscription was created, but is not connected |
| HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription. |
| \dRs+ regress_testsub4 |
| List of subscriptions |
| Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN |
| ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+--------------------+-----------------------------+---------- |
| regress_testsub4 | regress_subscription_user | f | {testpub} | f | off | d | f | none | t | f | off | dbname=regress_doesnotexist | 0/0 |
| (1 row) |
| |
| ALTER SUBSCRIPTION regress_testsub4 SET (origin = any); |
| \dRs+ regress_testsub4 |
| List of subscriptions |
| Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN |
| ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+--------------------+-----------------------------+---------- |
| regress_testsub4 | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | off | dbname=regress_doesnotexist | 0/0 |
| (1 row) |
| |
| DROP SUBSCRIPTION regress_testsub3; |
| DROP SUBSCRIPTION regress_testsub4; |
| -- fail, connection string does not parse |
| CREATE SUBSCRIPTION regress_testsub5 CONNECTION 'i_dont_exist=param' PUBLICATION testpub; |
| ERROR: invalid connection string syntax: invalid connection option "i_dont_exist" |
| -- fail, connection string parses, but doesn't work (and does so without |
| -- connecting, so this is reliable and safe) |
| CREATE SUBSCRIPTION regress_testsub5 CONNECTION 'port=-1' PUBLICATION testpub; |
| ERROR: could not connect to the publisher: invalid port number: "-1" |
| -- fail - invalid connection string during ALTER |
| ALTER SUBSCRIPTION regress_testsub CONNECTION 'foobar'; |
| ERROR: invalid connection string syntax: missing "=" after "foobar" in connection info string |
| \dRs+ |
| List of subscriptions |
| Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN |
| -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+--------------------+-----------------------------+---------- |
| regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | off | dbname=regress_doesnotexist | 0/0 |
| (1 row) |
| |
| 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); |
| \dRs+ |
| List of subscriptions |
| Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN |
| -----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+--------------------+------------------------------+---------- |
| regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | f | t | off | dbname=regress_doesnotexist2 | 0/0 |
| (1 row) |
| |
| 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 = ''); |
| ERROR: replication slot name "" is too short |
| -- fail |
| ALTER SUBSCRIPTION regress_testsub SET (slot_name = ''); |
| ERROR: replication slot name "" is too short |
| -- fail |
| ALTER SUBSCRIPTION regress_doesnotexist CONNECTION 'dbname=regress_doesnotexist2'; |
| ERROR: subscription "regress_doesnotexist" does not exist |
| ALTER SUBSCRIPTION regress_testsub SET (create_slot = false); |
| ERROR: unrecognized subscription parameter: "create_slot" |
| -- ok |
| ALTER SUBSCRIPTION regress_testsub SKIP (lsn = '0/12345'); |
| \dRs+ |
| List of subscriptions |
| Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN |
| -----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+--------------------+------------------------------+---------- |
| regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | t | f | off | dbname=regress_doesnotexist2 | 0/12345 |
| (1 row) |
| |
| -- ok - with lsn = NONE |
| ALTER SUBSCRIPTION regress_testsub SKIP (lsn = NONE); |
| -- fail |
| ALTER SUBSCRIPTION regress_testsub SKIP (lsn = '0/0'); |
| ERROR: invalid WAL location (LSN): 0/0 |
| \dRs+ |
| List of subscriptions |
| Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN |
| -----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+--------------------+------------------------------+---------- |
| regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | t | f | off | dbname=regress_doesnotexist2 | 0/0 |
| (1 row) |
| |
| BEGIN; |
| ALTER SUBSCRIPTION regress_testsub ENABLE; |
| \dRs |
| List of subscriptions |
| Name | Owner | Enabled | Publication |
| -----------------+---------------------------+---------+--------------------- |
| regress_testsub | regress_subscription_user | t | {testpub2,testpub3} |
| (1 row) |
| |
| ALTER SUBSCRIPTION regress_testsub DISABLE; |
| \dRs |
| List of subscriptions |
| Name | Owner | Enabled | Publication |
| -----------------+---------------------------+---------+--------------------- |
| regress_testsub | regress_subscription_user | f | {testpub2,testpub3} |
| (1 row) |
| |
| COMMIT; |
| -- fail - must be owner of subscription |
| SET ROLE regress_subscription_user_dummy; |
| ALTER SUBSCRIPTION regress_testsub RENAME TO regress_testsub_dummy; |
| ERROR: must be owner of subscription regress_testsub |
| 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); |
| ERROR: invalid value for parameter "synchronous_commit": "foobar" |
| HINT: Available values: local, remote_write, remote_apply, on, off. |
| \dRs+ |
| List of subscriptions |
| Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN |
| ---------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+--------------------+------------------------------+---------- |
| regress_testsub_foo | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | t | f | local | dbname=regress_doesnotexist2 | 0/0 |
| (1 row) |
| |
| -- 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; |
| ERROR: DROP SUBSCRIPTION cannot run inside a transaction block |
| COMMIT; |
| ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); |
| -- now it works |
| BEGIN; |
| DROP SUBSCRIPTION regress_testsub; |
| COMMIT; |
| DROP SUBSCRIPTION IF EXISTS regress_testsub; |
| NOTICE: subscription "regress_testsub" does not exist, skipping |
| DROP SUBSCRIPTION regress_testsub; -- fail |
| ERROR: subscription "regress_testsub" does not exist |
| -- fail - binary must be boolean |
| CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, binary = foo); |
| ERROR: binary requires a Boolean value |
| -- now it works |
| CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, binary = true); |
| WARNING: subscription was created, but is not connected |
| HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription. |
| \dRs+ |
| List of subscriptions |
| Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN |
| -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+--------------------+-----------------------------+---------- |
| regress_testsub | regress_subscription_user | f | {testpub} | t | off | d | f | any | t | f | off | dbname=regress_doesnotexist | 0/0 |
| (1 row) |
| |
| ALTER SUBSCRIPTION regress_testsub SET (binary = false); |
| ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); |
| \dRs+ |
| List of subscriptions |
| Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN |
| -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+--------------------+-----------------------------+---------- |
| regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | off | dbname=regress_doesnotexist | 0/0 |
| (1 row) |
| |
| DROP SUBSCRIPTION regress_testsub; |
| -- fail - streaming must be boolean or 'parallel' |
| CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, streaming = foo); |
| ERROR: streaming requires a Boolean value or "parallel" |
| -- now it works |
| CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, streaming = true); |
| WARNING: subscription was created, but is not connected |
| HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription. |
| \dRs+ |
| List of subscriptions |
| Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN |
| -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+--------------------+-----------------------------+---------- |
| regress_testsub | regress_subscription_user | f | {testpub} | f | on | d | f | any | t | f | off | dbname=regress_doesnotexist | 0/0 |
| (1 row) |
| |
| ALTER SUBSCRIPTION regress_testsub SET (streaming = parallel); |
| \dRs+ |
| List of subscriptions |
| Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN |
| -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+--------------------+-----------------------------+---------- |
| regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | off | dbname=regress_doesnotexist | 0/0 |
| (1 row) |
| |
| ALTER SUBSCRIPTION regress_testsub SET (streaming = false); |
| ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); |
| \dRs+ |
| List of subscriptions |
| Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN |
| -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+--------------------+-----------------------------+---------- |
| regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | off | dbname=regress_doesnotexist | 0/0 |
| (1 row) |
| |
| -- fail - publication already exists |
| ALTER SUBSCRIPTION regress_testsub ADD PUBLICATION testpub WITH (refresh = false); |
| ERROR: publication "testpub" is already in subscription "regress_testsub" |
| -- fail - publication used more than once |
| ALTER SUBSCRIPTION regress_testsub ADD PUBLICATION testpub1, testpub1 WITH (refresh = false); |
| ERROR: publication name "testpub1" used more than once |
| -- 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); |
| ERROR: publication "testpub1" is already in subscription "regress_testsub" |
| \dRs+ |
| List of subscriptions |
| Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN |
| -----------------+---------------------------+---------+-----------------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+--------------------+-----------------------------+---------- |
| regress_testsub | regress_subscription_user | f | {testpub,testpub1,testpub2} | f | off | d | f | any | t | f | off | dbname=regress_doesnotexist | 0/0 |
| (1 row) |
| |
| -- fail - publication used more than once |
| ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub1, testpub1 WITH (refresh = false); |
| ERROR: publication name "testpub1" used more than once |
| -- fail - all publications are deleted |
| ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub, testpub1, testpub2 WITH (refresh = false); |
| ERROR: cannot drop all the publications from a subscription |
| -- fail - publication does not exist in subscription |
| ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub3 WITH (refresh = false); |
| ERROR: publication "testpub3" is not in subscription "regress_testsub" |
| -- ok - delete publications |
| ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub1, testpub2 WITH (refresh = false); |
| \dRs+ |
| List of subscriptions |
| Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN |
| -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+--------------------+-----------------------------+---------- |
| regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | off | dbname=regress_doesnotexist | 0/0 |
| (1 row) |
| |
| DROP SUBSCRIPTION regress_testsub; |
| CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION mypub |
| WITH (connect = false, create_slot = false, copy_data = false); |
| WARNING: subscription was created, but is not connected |
| HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription. |
| 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); |
| ERROR: ALTER SUBSCRIPTION with refresh cannot run inside a transaction block |
| END; |
| BEGIN; |
| ALTER SUBSCRIPTION regress_testsub REFRESH PUBLICATION; |
| ERROR: ALTER SUBSCRIPTION ... REFRESH cannot run inside a transaction block |
| END; |
| CREATE FUNCTION func() RETURNS VOID AS |
| $$ ALTER SUBSCRIPTION regress_testsub SET PUBLICATION mypub WITH (refresh = true) $$ LANGUAGE SQL; |
| SELECT func(); |
| ERROR: ALTER SUBSCRIPTION with refresh cannot be executed from a function |
| CONTEXT: SQL function "func" statement 1 |
| ALTER SUBSCRIPTION regress_testsub DISABLE; |
| ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); |
| DROP SUBSCRIPTION regress_testsub; |
| DROP FUNCTION func; |
| -- fail - two_phase must be boolean |
| CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, two_phase = foo); |
| ERROR: two_phase requires a Boolean value |
| -- now it works |
| CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, two_phase = true); |
| WARNING: subscription was created, but is not connected |
| HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription. |
| \dRs+ |
| List of subscriptions |
| Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN |
| -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+--------------------+-----------------------------+---------- |
| regress_testsub | regress_subscription_user | f | {testpub} | f | off | p | f | any | t | f | off | dbname=regress_doesnotexist | 0/0 |
| (1 row) |
| |
| --fail - alter of two_phase option not supported. |
| ALTER SUBSCRIPTION regress_testsub SET (two_phase = false); |
| ERROR: unrecognized subscription parameter: "two_phase" |
| -- but can alter streaming when two_phase enabled |
| ALTER SUBSCRIPTION regress_testsub SET (streaming = true); |
| \dRs+ |
| List of subscriptions |
| Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN |
| -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+--------------------+-----------------------------+---------- |
| regress_testsub | regress_subscription_user | f | {testpub} | f | on | p | f | any | t | f | off | dbname=regress_doesnotexist | 0/0 |
| (1 row) |
| |
| ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); |
| DROP SUBSCRIPTION regress_testsub; |
| -- two_phase and streaming are compatible. |
| CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, streaming = true, two_phase = true); |
| WARNING: subscription was created, but is not connected |
| HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription. |
| \dRs+ |
| List of subscriptions |
| Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN |
| -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+--------------------+-----------------------------+---------- |
| regress_testsub | regress_subscription_user | f | {testpub} | f | on | p | f | any | t | f | off | dbname=regress_doesnotexist | 0/0 |
| (1 row) |
| |
| ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); |
| DROP SUBSCRIPTION regress_testsub; |
| -- fail - disable_on_error must be boolean |
| CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, disable_on_error = foo); |
| ERROR: disable_on_error requires a Boolean value |
| -- now it works |
| CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, disable_on_error = false); |
| WARNING: subscription was created, but is not connected |
| HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription. |
| \dRs+ |
| List of subscriptions |
| Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN |
| -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+--------------------+-----------------------------+---------- |
| regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | off | dbname=regress_doesnotexist | 0/0 |
| (1 row) |
| |
| ALTER SUBSCRIPTION regress_testsub SET (disable_on_error = true); |
| \dRs+ |
| List of subscriptions |
| Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN |
| -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+--------------------+-----------------------------+---------- |
| regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | t | any | t | f | off | dbname=regress_doesnotexist | 0/0 |
| (1 row) |
| |
| 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, not enough privileges |
| CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false); |
| ERROR: permission denied for database regression |
| -- fail, must specify password |
| RESET SESSION AUTHORIZATION; |
| GRANT CREATE ON DATABASE REGRESSION TO regress_subscription_user3; |
| SET SESSION AUTHORIZATION regress_subscription_user3; |
| CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false); |
| ERROR: password is required |
| DETAIL: Non-superusers must provide a password in the connection string. |
| -- 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; |
| CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, password_required = false); |
| ERROR: password_required=false is superuser-only |
| HINT: Subscriptions with the password_required option set to false may only be created or modified by the superuser. |
| -- ok |
| RESET SESSION AUTHORIZATION; |
| GRANT CREATE ON DATABASE REGRESSION TO regress_subscription_user3; |
| SET SESSION AUTHORIZATION regress_subscription_user3; |
| CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist password=regress_fakepassword' PUBLICATION testpub WITH (connect = false); |
| WARNING: subscription was created, but is not connected |
| HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription. |
| -- we cannot give the subscription away to some random user |
| ALTER SUBSCRIPTION regress_testsub OWNER TO regress_subscription_user; |
| ERROR: must be able to SET ROLE "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; |
| ERROR: permission denied for database regression |
| -- 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; |
