diff options
Diffstat (limited to '')
| -rw-r--r-- | src/test/regress/expected/subscription.out | 480 |
1 files changed, 480 insertions, 0 deletions
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out new file mode 100644 index 0000000..b15eddb --- /dev/null +++ b/src/test/regress/expected/subscription.out @@ -0,0 +1,480 @@ +-- +-- 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_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; |