v59_amit_3.txt
text/plain
Filename: v59_amit_3.txt
Type: text/plain
Part: 1
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 5dc5ebfd2a9..90d2a4faee2 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -802,19 +802,17 @@ WHERE s.subname = 'regress_conflict_test1';
--
-- re-enable table logging for verification
ALTER SUBSCRIPTION regress_conflict_test1 SET (conflict_log_destination = 'table');
--- We use a DO block with dynamic SQL because the conflict log table
--- name contains the subscription OID, which is non-deterministic. This
--- approach allows us to attempt the DROP and capture the expected error
--- without hard-coding a specific OID in the expected output
+-- The conflict log table name contains the subscription OID, which is
+-- non-deterministic. Capture it into a psql variable and report only the
+-- SQLSTATE, so the expected output does not depend on the OID.
-- fail - drop table not allowed due to internal dependency
SET client_min_messages = NOTICE;
-DO $$
-BEGIN
- EXECUTE 'DROP TABLE ' || (SELECT 'pg_conflict.pg_conflict_log_' || oid FROM pg_subscription WHERE subname = 'regress_conflict_test1');
-EXCEPTION WHEN insufficient_privilege THEN
- RAISE NOTICE 'captured expected error: insufficient_privilege';
-END $$;
-NOTICE: captured expected error: insufficient_privilege
+SELECT 'pg_conflict.pg_conflict_log_' || oid AS clt1
+ FROM pg_subscription WHERE subname = 'regress_conflict_test1' \gset
+\set VERBOSITY sqlstate
+DROP TABLE :clt1;
+ERROR: 42501
+\set VERBOSITY default
-- CLEANUP: DROP SUBSCRIPTION reaps the table
ALTER SUBSCRIPTION regress_conflict_test1 DISABLE;
ALTER SUBSCRIPTION regress_conflict_test1 SET (slot_name = NONE);
@@ -837,335 +835,74 @@ CREATE SUBSCRIPTION regress_conflict_protection_test CONNECTION 'dbname=regress_
PUBLICATION testpub WITH (connect = false, conflict_log_destination = 'table');
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
--- Trying to ALTER the conflict log table
--- This should fail because the table is system-managed
--- As mentioned in previous test cases, we use a DO block to hide dynamic OIDs
-SET client_min_messages = NOTICE;
-DO $$
-DECLARE
- tab_name text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
+-- The conflict log table is system-managed; its name contains the
+-- subscription OID, which is non-deterministic. Capture the name into a psql
+-- variable and report only the SQLSTATE for the operations that must be
+-- rejected, so the expected output stays free of the dynamic OID. Every
+-- statement in the VERBOSITY-sqlstate block below must fail with 42809
+-- (wrong_object_type), except adding the table to a publication, which fails
+-- with 22023 (invalid_parameter_value).
+SELECT 'pg_conflict.' || relname AS clt
FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
-
- RAISE NOTICE 'Attempting ALTER TABLE on conflict log table';
- EXECUTE 'ALTER TABLE ' || tab_name || ' ADD COLUMN extra_info text';
-EXCEPTION WHEN wrong_object_type THEN
- RAISE NOTICE 'captured expected error: wrong_object_type during ALTER';
-END $$;
-NOTICE: Attempting ALTER TABLE on conflict log table
-NOTICE: captured expected error: wrong_object_type during ALTER
--- Test Manual INSERT on conflict log table
--- This should fail because the table is system-managed
--- Hiding the OID in the error message by catching the exception
-DO $$
-DECLARE
- tab_name text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
-
- EXECUTE 'INSERT INTO ' || tab_name || ' (relname) VALUES (''mytest'')';
-EXCEPTION WHEN wrong_object_type THEN
- RAISE NOTICE 'captured expected error: wrong_object_type during INSERT';
-END $$;
-NOTICE: captured expected error: wrong_object_type during INSERT
--- Test Manual UPDATE on conflict log table
--- This should fail because the table is system-managed
--- Hiding the OID in the error message by catching the exception
-DO $$
-DECLARE
- tab_name text;
+ WHERE s.subname = 'regress_conflict_protection_test' \gset
+-- Trigger function used by the CREATE TRIGGER check below.
+CREATE FUNCTION public.dummy_trigger_func() RETURNS trigger AS $$
BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
-
- EXECUTE 'UPDATE ' || tab_name || ' SET relname = ''mytest'' ';
-EXCEPTION WHEN wrong_object_type THEN
- RAISE NOTICE 'captured expected error: wrong_object_type during UPDATE';
-END $$;
-NOTICE: captured expected error: wrong_object_type during UPDATE
--- Trying to perform TRUNCATE/DELETE on the conflict log table
--- This should be allowed so that user can perform cleanup
-SELECT 'pg_conflict.' || relname AS conflict_tab
-FROM pg_class c
-JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
-WHERE s.subname = 'regress_conflict_protection_test' \gset
-TRUNCATE :conflict_tab;
-DELETE FROM :conflict_tab;
--- Trying to create a new table manually in the pg_conflict namespace
--- This should fail as the namespace is reserved for conflict log tables
+ RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+\set VERBOSITY sqlstate
+ALTER TABLE :clt ADD COLUMN extra_info text;
+ERROR: 42809
+INSERT INTO :clt (relname) VALUES ('mytest');
+ERROR: 42809
+UPDATE :clt SET relname = 'mytest';
+ERROR: 42809
+CREATE POLICY p1 ON :clt USING (true);
+ERROR: 42809
+CREATE STATISTICS s1 ON relname, schemaname FROM :clt;
+ERROR: 42809
+CREATE TABLE public.conflict_child () INHERITS (:clt);
+ERROR: 42809
+ALTER TABLE :clt RENAME COLUMN relname TO new_relname;
+ERROR: 42809
+CREATE TABLE public.conflict_fk (relname text REFERENCES :clt(relname));
+ERROR: 42809
+ALTER TABLE :clt OWNER TO regress_subscription_user_dummy;
+ERROR: 42809
+ALTER TABLE :clt SET SCHEMA public;
+ERROR: 42809
+CREATE TRIGGER t1 BEFORE INSERT ON :clt FOR EACH ROW EXECUTE FUNCTION public.dummy_trigger_func();
+ERROR: 42809
+ALTER TRIGGER non_existent_trigger ON :clt RENAME TO new_trigger;
+ERROR: 42809
+CREATE RULE r1 AS ON INSERT TO :clt DO INSTEAD NOTHING;
+ERROR: 42809
+ALTER RULE non_existent_rule ON :clt RENAME TO new_rule;
+ERROR: 42809
+CREATE INDEX idx1 ON :clt (relname);
+ERROR: 42809
+SELECT 1 FROM :clt FOR UPDATE;
+ERROR: 42809
+CREATE PUBLICATION testpub_for_clt FOR TABLE :clt;
+ERROR: 22023
+\set VERBOSITY default
+-- Clean up the trigger function used above.
+DROP FUNCTION public.dummy_trigger_func();
+-- TRUNCATE and DELETE are allowed so that users can prune the conflict log.
+TRUNCATE :clt;
+DELETE FROM :clt;
+-- Creating a table directly in the pg_conflict namespace is rejected for
+-- everyone (the schema is reserved for conflict log tables).
CREATE TABLE pg_conflict.manual_table (id int);
ERROR: permission denied for schema pg_conflict
LINE 1: CREATE TABLE pg_conflict.manual_table (id int);
^
--- Moving an existing table into the pg_conflict namespace
--- Users should not be able to move their own tables within this namespace
+-- Moving a user table into the pg_conflict namespace is likewise rejected.
CREATE TABLE public.test_move (id int);
ALTER TABLE public.test_move SET SCHEMA pg_conflict;
ERROR: permission denied for schema pg_conflict
DROP TABLE public.test_move;
--- Setup dummy trigger function for trigger tests
-CREATE FUNCTION public.dummy_trigger_func() RETURNS trigger AS $$
-BEGIN
- RETURN NEW;
-END;
-$$ LANGUAGE plpgsql;
--- Policy on conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'CREATE POLICY p1 ON ' || tab_name || ' USING (true)';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Policy error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-NOTICE: Policy error: cannot create policy on conflict log table "pg_conflict_log_xxx", detail: Conflict log tables are system-managed tables for logical replication conflicts.
--- Statistics on conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'CREATE STATISTICS s1 ON relname, schemaname FROM ' || tab_name;
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Statistics error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-NOTICE: Statistics error: cannot create statistics on conflict log table "pg_conflict_log_xxx", detail: Conflict log tables are system-managed tables for logical replication conflicts.
--- Inheritance from conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'CREATE TABLE public.conflict_child () INHERITS (' || tab_name || ')';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Inheritance error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-NOTICE: Inheritance error: cannot inherit from conflict log table "pg_conflict_log_xxx", detail: Conflict log tables are system-managed tables for logical replication conflicts.
--- Column rename on conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'ALTER TABLE ' || tab_name || ' RENAME COLUMN relname TO new_relname';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Column rename error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-NOTICE: Column rename error: cannot rename columns of conflict log table "pg_conflict_log_xxx", detail: Conflict log tables are system-managed tables for logical replication conflicts.
--- Foreign key reference to conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'CREATE TABLE public.conflict_fk (relname text REFERENCES ' || tab_name || '(relname))';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Foreign key error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-NOTICE: Foreign key error: cannot reference conflict log table "pg_conflict_log_xxx", detail: Conflict log tables are system-managed tables for logical replication conflicts.
--- Alter table owner
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'ALTER TABLE ' || tab_name || ' OWNER TO regress_subscription_user_dummy';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Alter owner error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-NOTICE: Alter owner error: cannot alter conflict log table "pg_conflict_log_xxx", detail: Conflict log tables are system-managed tables for logical replication conflicts.
--- Alter table schema
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'ALTER TABLE ' || tab_name || ' SET SCHEMA public';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Alter schema error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-NOTICE: Alter schema error: cannot alter conflict log table "pg_conflict_log_xxx", detail: Conflict log tables are system-managed tables for logical replication conflicts.
--- Create trigger on conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'CREATE TRIGGER t1 BEFORE INSERT ON ' || tab_name || ' FOR EACH ROW EXECUTE FUNCTION public.dummy_trigger_func()';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Create trigger error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-NOTICE: Create trigger error: cannot create trigger on conflict log table "pg_conflict_log_xxx", detail: Conflict log tables are system-managed tables for logical replication conflicts.
--- Rename trigger on conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'ALTER TRIGGER non_existent_trigger ON ' || tab_name || ' RENAME TO new_trigger';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Rename trigger error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-NOTICE: Rename trigger error: cannot rename trigger on conflict log table "pg_conflict_log_xxx", detail: Conflict log tables are system-managed tables for logical replication conflicts.
--- Create rule on conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'CREATE RULE r1 AS ON INSERT TO ' || tab_name || ' DO INSTEAD NOTHING';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Create rule error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-NOTICE: Create rule error: conflict log table "pg_conflict_log_xxx" cannot have rules, detail: Conflict log tables are system-managed tables for logical replication conflicts.
--- Rename rule on conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'ALTER RULE non_existent_rule ON ' || tab_name || ' RENAME TO new_rule';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Rename rule error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-NOTICE: Rename rule error: conflict log table "pg_conflict_log_xxx" cannot have rules, detail: Conflict log tables are system-managed tables for logical replication conflicts.
--- Create index on conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'CREATE INDEX idx1 ON ' || tab_name || ' (relname)';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Create index error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-NOTICE: Create index error: cannot change conflict log table "pg_conflict_log_xxx", detail: Conflict log tables are system-managed tables for logical replication conflicts.
--- fail - conflict log table cannot be added to a publication
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'CREATE PUBLICATION testpub_for_clt FOR TABLE ' || tab_name;
-EXCEPTION WHEN invalid_parameter_value THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Publication error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- err_detail;
-END $$;
-NOTICE: Publication error: cannot add relation "pg_conflict_log_xxx" to publication, detail: This operation is not supported for conflict log tables.
--- fail - cannot lock rows in conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'SELECT 1 FROM ' || tab_name || ' FOR UPDATE';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT;
- RAISE NOTICE 'Row lock error: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-NOTICE: Row lock error: cannot lock rows in the conflict log table "pg_conflict_log_xxx"
--- Clean up trigger function
-DROP FUNCTION public.dummy_trigger_func();
SET client_min_messages = WARNING;
-- Clean up remaining test subscription
ALTER SUBSCRIPTION regress_conflict_log_default DISABLE;
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index 503c9ceb272..1e4264e1530 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -604,19 +604,17 @@ WHERE s.subname = 'regress_conflict_test1';
-- re-enable table logging for verification
ALTER SUBSCRIPTION regress_conflict_test1 SET (conflict_log_destination = 'table');
--- We use a DO block with dynamic SQL because the conflict log table
--- name contains the subscription OID, which is non-deterministic. This
--- approach allows us to attempt the DROP and capture the expected error
--- without hard-coding a specific OID in the expected output
+-- The conflict log table name contains the subscription OID, which is
+-- non-deterministic. Capture it into a psql variable and report only the
+-- SQLSTATE, so the expected output does not depend on the OID.
-- fail - drop table not allowed due to internal dependency
SET client_min_messages = NOTICE;
-DO $$
-BEGIN
- EXECUTE 'DROP TABLE ' || (SELECT 'pg_conflict.pg_conflict_log_' || oid FROM pg_subscription WHERE subname = 'regress_conflict_test1');
-EXCEPTION WHEN insufficient_privilege THEN
- RAISE NOTICE 'captured expected error: insufficient_privilege';
-END $$;
+SELECT 'pg_conflict.pg_conflict_log_' || oid AS clt1
+ FROM pg_subscription WHERE subname = 'regress_conflict_test1' \gset
+\set VERBOSITY sqlstate
+DROP TABLE :clt1;
+\set VERBOSITY default
-- CLEANUP: DROP SUBSCRIPTION reaps the table
ALTER SUBSCRIPTION regress_conflict_test1 DISABLE;
@@ -639,336 +637,60 @@ SELECT to_regclass(:'internal_tablename');
CREATE SUBSCRIPTION regress_conflict_protection_test CONNECTION 'dbname=regress_doesnotexist'
PUBLICATION testpub WITH (connect = false, conflict_log_destination = 'table');
--- Trying to ALTER the conflict log table
--- This should fail because the table is system-managed
--- As mentioned in previous test cases, we use a DO block to hide dynamic OIDs
-
-SET client_min_messages = NOTICE;
-DO $$
-DECLARE
- tab_name text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
+-- The conflict log table is system-managed; its name contains the
+-- subscription OID, which is non-deterministic. Capture the name into a psql
+-- variable and report only the SQLSTATE for the operations that must be
+-- rejected, so the expected output stays free of the dynamic OID. Every
+-- statement in the VERBOSITY-sqlstate block below must fail with 42809
+-- (wrong_object_type), except adding the table to a publication, which fails
+-- with 22023 (invalid_parameter_value).
+SELECT 'pg_conflict.' || relname AS clt
FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
-
- RAISE NOTICE 'Attempting ALTER TABLE on conflict log table';
- EXECUTE 'ALTER TABLE ' || tab_name || ' ADD COLUMN extra_info text';
-EXCEPTION WHEN wrong_object_type THEN
- RAISE NOTICE 'captured expected error: wrong_object_type during ALTER';
-END $$;
-
--- Test Manual INSERT on conflict log table
--- This should fail because the table is system-managed
--- Hiding the OID in the error message by catching the exception
-DO $$
-DECLARE
- tab_name text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
-
- EXECUTE 'INSERT INTO ' || tab_name || ' (relname) VALUES (''mytest'')';
-EXCEPTION WHEN wrong_object_type THEN
- RAISE NOTICE 'captured expected error: wrong_object_type during INSERT';
-END $$;
-
--- Test Manual UPDATE on conflict log table
--- This should fail because the table is system-managed
--- Hiding the OID in the error message by catching the exception
-DO $$
-DECLARE
- tab_name text;
+ WHERE s.subname = 'regress_conflict_protection_test' \gset
+
+-- Trigger function used by the CREATE TRIGGER check below.
+CREATE FUNCTION public.dummy_trigger_func() RETURNS trigger AS $$
BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
+ RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
- EXECUTE 'UPDATE ' || tab_name || ' SET relname = ''mytest'' ';
-EXCEPTION WHEN wrong_object_type THEN
- RAISE NOTICE 'captured expected error: wrong_object_type during UPDATE';
-END $$;
+\set VERBOSITY sqlstate
+ALTER TABLE :clt ADD COLUMN extra_info text;
+INSERT INTO :clt (relname) VALUES ('mytest');
+UPDATE :clt SET relname = 'mytest';
+CREATE POLICY p1 ON :clt USING (true);
+CREATE STATISTICS s1 ON relname, schemaname FROM :clt;
+CREATE TABLE public.conflict_child () INHERITS (:clt);
+ALTER TABLE :clt RENAME COLUMN relname TO new_relname;
+CREATE TABLE public.conflict_fk (relname text REFERENCES :clt(relname));
+ALTER TABLE :clt OWNER TO regress_subscription_user_dummy;
+ALTER TABLE :clt SET SCHEMA public;
+CREATE TRIGGER t1 BEFORE INSERT ON :clt FOR EACH ROW EXECUTE FUNCTION public.dummy_trigger_func();
+ALTER TRIGGER non_existent_trigger ON :clt RENAME TO new_trigger;
+CREATE RULE r1 AS ON INSERT TO :clt DO INSTEAD NOTHING;
+ALTER RULE non_existent_rule ON :clt RENAME TO new_rule;
+CREATE INDEX idx1 ON :clt (relname);
+SELECT 1 FROM :clt FOR UPDATE;
+CREATE PUBLICATION testpub_for_clt FOR TABLE :clt;
+\set VERBOSITY default
+
+-- Clean up the trigger function used above.
+DROP FUNCTION public.dummy_trigger_func();
--- Trying to perform TRUNCATE/DELETE on the conflict log table
--- This should be allowed so that user can perform cleanup
-SELECT 'pg_conflict.' || relname AS conflict_tab
-FROM pg_class c
-JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
-WHERE s.subname = 'regress_conflict_protection_test' \gset
-TRUNCATE :conflict_tab;
-DELETE FROM :conflict_tab;
+-- TRUNCATE and DELETE are allowed so that users can prune the conflict log.
+TRUNCATE :clt;
+DELETE FROM :clt;
--- Trying to create a new table manually in the pg_conflict namespace
--- This should fail as the namespace is reserved for conflict log tables
+-- Creating a table directly in the pg_conflict namespace is rejected for
+-- everyone (the schema is reserved for conflict log tables).
CREATE TABLE pg_conflict.manual_table (id int);
--- Moving an existing table into the pg_conflict namespace
--- Users should not be able to move their own tables within this namespace
+-- Moving a user table into the pg_conflict namespace is likewise rejected.
CREATE TABLE public.test_move (id int);
ALTER TABLE public.test_move SET SCHEMA pg_conflict;
DROP TABLE public.test_move;
--- Setup dummy trigger function for trigger tests
-CREATE FUNCTION public.dummy_trigger_func() RETURNS trigger AS $$
-BEGIN
- RETURN NEW;
-END;
-$$ LANGUAGE plpgsql;
-
--- Policy on conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'CREATE POLICY p1 ON ' || tab_name || ' USING (true)';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Policy error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-
--- Statistics on conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'CREATE STATISTICS s1 ON relname, schemaname FROM ' || tab_name;
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Statistics error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-
--- Inheritance from conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'CREATE TABLE public.conflict_child () INHERITS (' || tab_name || ')';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Inheritance error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-
--- Column rename on conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'ALTER TABLE ' || tab_name || ' RENAME COLUMN relname TO new_relname';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Column rename error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-
--- Foreign key reference to conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'CREATE TABLE public.conflict_fk (relname text REFERENCES ' || tab_name || '(relname))';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Foreign key error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-
--- Alter table owner
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'ALTER TABLE ' || tab_name || ' OWNER TO regress_subscription_user_dummy';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Alter owner error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-
--- Alter table schema
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'ALTER TABLE ' || tab_name || ' SET SCHEMA public';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Alter schema error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-
--- Create trigger on conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'CREATE TRIGGER t1 BEFORE INSERT ON ' || tab_name || ' FOR EACH ROW EXECUTE FUNCTION public.dummy_trigger_func()';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Create trigger error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-
--- Rename trigger on conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'ALTER TRIGGER non_existent_trigger ON ' || tab_name || ' RENAME TO new_trigger';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Rename trigger error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-
--- Create rule on conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'CREATE RULE r1 AS ON INSERT TO ' || tab_name || ' DO INSTEAD NOTHING';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Create rule error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-
--- Rename rule on conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'ALTER RULE non_existent_rule ON ' || tab_name || ' RENAME TO new_rule';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Rename rule error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-
--- Create index on conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'CREATE INDEX idx1 ON ' || tab_name || ' (relname)';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Create index error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- regexp_replace(err_detail, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-
--- fail - conflict log table cannot be added to a publication
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
- err_detail text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'CREATE PUBLICATION testpub_for_clt FOR TABLE ' || tab_name;
-EXCEPTION WHEN invalid_parameter_value THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT, err_detail = PG_EXCEPTION_DETAIL;
- RAISE NOTICE 'Publication error: %, detail: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx'),
- err_detail;
-END $$;
-
--- fail - cannot lock rows in conflict log table
-DO $$
-DECLARE
- tab_name text;
- err_msg text;
-BEGIN
- SELECT 'pg_conflict.' || relname INTO tab_name
- FROM pg_class c JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
- WHERE s.subname = 'regress_conflict_protection_test';
- EXECUTE 'SELECT 1 FROM ' || tab_name || ' FOR UPDATE';
-EXCEPTION WHEN wrong_object_type THEN
- GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT;
- RAISE NOTICE 'Row lock error: %',
- regexp_replace(err_msg, 'pg_conflict_log_\d+', 'pg_conflict_log_xxx');
-END $$;
-
--- Clean up trigger function
-DROP FUNCTION public.dummy_trigger_func();
-
SET client_min_messages = WARNING;