On Tue, Jun 30, 2026 at 11:02 AM Amit Kapila <[email protected]> wrote:
>
> On Tue, Jun 30, 2026 at 9:22 AM Dilip Kumar <[email protected]> wrote:
> >
> > > ======
> > > src/backend/commands/tablecmds.c
> > >
> > > 2.
> > > + /*
> > > + * Conflict log tables are managed by the system for logical
> > > + * replication and should not be used as parent tables, as
> > > + * inheritance could interfere with the logging behavior.
> > > + */
> > > + if (IsConflictLogTableNamespace(relation->rd_rel->relnamespace))
> > > + ereport(ERROR,
> > > + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
> > > + errmsg("permission denied: cannot inherit from conflict log table
> > > \"%s\"",
> > > + RelationGetRelationName(relation)),
> > > + errdetail("Conflict log tables are system-managed tables for logical
> > > replication conflicts.")));
> > >
> > > It is not really a lack of "privilege" preventing this -- it is just
> > > disallowed for everybody. Would ERRCODE_WRONG_OBJECT_TYPE be a more
> > > appropriate errcode?
> >
> > I agree that we currently restrict most operations on CLTs, but the
> > issue isn't that the CLT is the wrong object type, they are still
> > relations. While we might open up certain operations in the future
> > based on business needs (such as allowing index creation), I still
> > believe this is a matter of privileges rather than object type.
> >
>
> If we look from this POV where we want to support some features in
> future, ERRCODE_FEATURE_NOT_SUPPORTED sounds like a better option.
> OTOH, in execMain.c, in 0001, WRONG_OBJECT_TYPE is any way a better
> fit because we never want to allow INSERT/UPDATE/MERGE. Having, single
> error_code makes it easy to understand/explain/extend this feature,
> otherwise, the future users will always be confused as to which
> error_code is better fit. I think insuffcient_privilige doesn't suit
> even if we want to support such features in future because those won't
> require a new grant kind of privilege. Even though WRONG_OBJECT_TYPE
> is slightly over stretched for some cases but I think consistency
> helps, so, I would prefer WRONG_OBJECT_TYPE in the case Peter pointed
> out and other DDL operations where we have a similar check.
>
I have changed the error code to WRONG_OBJECT_TYPE in the attached
v59_amit_2.txt. The other thing that bothers me about this patch is
the usage of DO blocks in the tests to hide the CLT name (as the same
has OID in its name which is non-deterministic) which doesn't seem to
be the best way as that bloats the test code and make the test cases
difficult to read. I have changed those specific tests in the attached
v59_amit_3.txt by using \gset and \set VERBOSITY sqlstate which we
also use in arrays.sql and a few other places. Let me know what you
think of attached top-up patches. Also, now let's merge your 0001 and
0002 patches.
--
With Regards,
Amit Kapila.
diff --git a/src/backend/commands/policy.c b/src/backend/commands/policy.c
index 10ea599bac0..ebab2edde92 100644
--- a/src/backend/commands/policy.c
+++ b/src/backend/commands/policy.c
@@ -86,8 +86,8 @@ RangeVarCallbackForPolicy(const RangeVar *rv, Oid relid, Oid
oldrelid,
*/
if (IsConflictLogTableClass(classform))
ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("permission denied: \"%s\" is a
conflict log table",
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot create policy on conflict log
table \"%s\"",
rv->relname),
errdetail("Conflict log tables are
system-managed tables for logical replication conflicts.")));
diff --git a/src/backend/commands/statscmds.c b/src/backend/commands/statscmds.c
index e8fa81ef555..ce98ebb35ea 100644
--- a/src/backend/commands/statscmds.c
+++ b/src/backend/commands/statscmds.c
@@ -156,8 +156,8 @@ CreateStatistics(CreateStatsStmt *stmt, bool check_rights)
*/
if (IsConflictLogTableClass(rel->rd_rel))
ereport(ERROR,
-
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("permission denied: \"%s\" is a
conflict log table",
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot create statistics on
conflict log table \"%s\"",
RelationGetRelationName(rel)),
errdetail("Conflict log tables are
system-managed tables for logical replication conflicts.")));
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index a3cda01d80f..47dafc0dba8 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -2764,8 +2764,8 @@ MergeAttributes(List *columns, const List *supers, char
relpersistence,
*/
if (IsConflictLogTableNamespace(relation->rd_rel->relnamespace))
ereport(ERROR,
-
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("permission denied: cannot
inherit from conflict log table \"%s\"",
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot inherit from conflict
log table \"%s\"",
RelationGetRelationName(relation)),
errdetail("Conflict log tables are
system-managed tables for logical replication conflicts.")));
@@ -3914,8 +3914,8 @@ renameatt_check(Oid myrelid, Form_pg_class classform,
bool recursing)
*/
if (IsConflictLogTableClass(classform))
ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("permission denied: \"%s\" is a
conflict log table",
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot rename columns of conflict log
table \"%s\"",
NameStr(classform->relname)),
errdetail("Conflict log tables are
system-managed tables for logical replication conflicts.")));
@@ -6932,8 +6932,8 @@ ATSimplePermissions(AlterTableType cmdtype, Relation rel,
int allowed_targets)
*/
if (IsConflictLogTableClass(rel->rd_rel))
ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("permission denied: \"%s\" is a
conflict log table",
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot alter conflict log table
\"%s\"",
RelationGetRelationName(rel)),
errdetail("Conflict log tables are
system-managed tables for logical replication conflicts.")));
@@ -10253,8 +10253,8 @@ ATAddForeignKeyConstraint(List **wqueue,
AlteredTableInfo *tab, Relation rel,
*/
if (IsConflictLogTableClass(pkrel->rd_rel))
ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("permission denied: \"%s\" is a
conflict log table",
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot reference conflict log table
\"%s\"",
RelationGetRelationName(pkrel)),
errdetail("Conflict log tables are
system-managed tables for logical replication conflicts.")));
@@ -20101,8 +20101,8 @@ RangeVarCallbackOwnsRelation(const RangeVar *relation,
*/
if (IsConflictLogTableClass((Form_pg_class) GETSTRUCT(tuple)))
ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("permission denied: \"%s\" is a
conflict log table",
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot change conflict log table
\"%s\"",
relation->relname),
errdetail("Conflict log tables are
system-managed tables for logical replication conflicts.")));
@@ -20148,8 +20148,8 @@ RangeVarCallbackForAlterRelation(const RangeVar *rv,
Oid relid, Oid oldrelid,
*/
if (IsConflictLogTableClass(classform))
ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("permission denied: \"%s\" is a
conflict log table",
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot alter conflict log table
\"%s\"",
rv->relname),
errdetail("Conflict log tables are
system-managed tables for logical replication conflicts.")));
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index de7957a15f7..00d3c90b599 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -321,8 +321,8 @@ CreateTriggerFiringOn(const CreateTrigStmt *stmt, const
char *queryString,
*/
if (IsConflictLogTableClass(rel->rd_rel))
ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("permission denied: \"%s\" is a
conflict log table",
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot create trigger on conflict log
table \"%s\"",
RelationGetRelationName(rel)),
errdetail("Conflict log tables are
system-managed tables for logical replication conflicts.")));
@@ -1463,8 +1463,8 @@ RangeVarCallbackForRenameTrigger(const RangeVar *rv, Oid
relid, Oid oldrelid,
*/
if (IsConflictLogTableClass(form))
ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("permission denied: \"%s\" is a
conflict log table",
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot rename trigger on conflict log
table \"%s\"",
rv->relname),
errdetail("Conflict log tables are
system-managed tables for logical replication conflicts.")));
diff --git a/src/backend/rewrite/rewriteDefine.c
b/src/backend/rewrite/rewriteDefine.c
index d00d76613a1..968ed8ccfce 100644
--- a/src/backend/rewrite/rewriteDefine.c
+++ b/src/backend/rewrite/rewriteDefine.c
@@ -268,8 +268,8 @@ DefineQueryRewrite(const char *rulename,
*/
if (IsConflictLogTableClass(event_relation->rd_rel))
ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("permission denied: \"%s\" is a
conflict log table",
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("conflict log table \"%s\" cannot have
rules",
RelationGetRelationName(event_relation)),
errdetail("Conflict log tables are
system-managed tables for logical replication conflicts.")));
@@ -789,8 +789,8 @@ RangeVarCallbackForRenameRule(const RangeVar *rv, Oid
relid, Oid oldrelid,
*/
if (IsConflictLogTableClass(form))
ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("permission denied: \"%s\" is a
conflict log table",
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("conflict log table \"%s\" cannot have
rules",
rv->relname),
errdetail("Conflict log tables are
system-managed tables for logical replication conflicts.")));
diff --git a/src/test/regress/expected/subscription.out
b/src/test/regress/expected/subscription.out
index 6f1b2c47a6d..5dc5ebfd2a9 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -851,11 +851,11 @@ BEGIN
RAISE NOTICE 'Attempting ALTER TABLE on conflict log table';
EXECUTE 'ALTER TABLE ' || tab_name || ' ADD COLUMN extra_info text';
-EXCEPTION WHEN insufficient_privilege THEN
- RAISE NOTICE 'captured expected error: insufficient_privilege during
ALTER';
+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: insufficient_privilege during ALTER
+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
@@ -925,13 +925,13 @@ BEGIN
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 insufficient_privilege THEN
+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: permission denied: "pg_conflict_log_xxx" is a conflict
log table, detail: Conflict log tables are system-managed tables for logical
replication conflicts.
+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
@@ -943,13 +943,13 @@ BEGIN
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 insufficient_privilege THEN
+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: permission denied: "pg_conflict_log_xxx" is a
conflict log table, detail: Conflict log tables are system-managed tables for
logical replication conflicts.
+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
@@ -961,13 +961,13 @@ BEGIN
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 insufficient_privilege THEN
+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: permission denied: cannot inherit from conflict
log table "pg_conflict_log_xxx", detail: Conflict log tables are system-managed
tables for logical replication conflicts.
+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
@@ -979,13 +979,13 @@ BEGIN
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 insufficient_privilege THEN
+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: permission denied: "pg_conflict_log_xxx" is a
conflict log table, detail: Conflict log tables are system-managed tables for
logical replication conflicts.
+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
@@ -997,13 +997,13 @@ BEGIN
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 insufficient_privilege THEN
+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: permission denied: "pg_conflict_log_xxx" is a
conflict log table, detail: Conflict log tables are system-managed tables for
logical replication conflicts.
+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
@@ -1015,13 +1015,13 @@ BEGIN
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 insufficient_privilege THEN
+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: permission denied: "pg_conflict_log_xxx" is a
conflict log table, detail: Conflict log tables are system-managed tables for
logical replication conflicts.
+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
@@ -1033,13 +1033,13 @@ BEGIN
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 insufficient_privilege THEN
+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: permission denied: "pg_conflict_log_xxx" is a
conflict log table, detail: Conflict log tables are system-managed tables for
logical replication conflicts.
+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
@@ -1051,13 +1051,13 @@ BEGIN
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 insufficient_privilege THEN
+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: permission denied: "pg_conflict_log_xxx" is a
conflict log table, detail: Conflict log tables are system-managed tables for
logical replication conflicts.
+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
@@ -1069,13 +1069,13 @@ BEGIN
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 insufficient_privilege THEN
+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: permission denied: "pg_conflict_log_xxx" is a
conflict log table, detail: Conflict log tables are system-managed tables for
logical replication conflicts.
+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
@@ -1087,13 +1087,13 @@ BEGIN
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 insufficient_privilege THEN
+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: permission denied: "pg_conflict_log_xxx" is a
conflict log table, detail: Conflict log tables are system-managed tables for
logical replication conflicts.
+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
@@ -1105,13 +1105,13 @@ BEGIN
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 insufficient_privilege THEN
+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: permission denied: "pg_conflict_log_xxx" is a
conflict log table, detail: Conflict log tables are system-managed tables for
logical replication conflicts.
+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
@@ -1123,13 +1123,13 @@ BEGIN
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 insufficient_privilege THEN
+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: permission denied: "pg_conflict_log_xxx" is a
conflict log table, detail: Conflict log tables are system-managed tables for
logical replication conflicts.
+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
diff --git a/src/test/regress/sql/subscription.sql
b/src/test/regress/sql/subscription.sql
index 084e5e7e78f..503c9ceb272 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -654,8 +654,8 @@ BEGIN
RAISE NOTICE 'Attempting ALTER TABLE on conflict log table';
EXECUTE 'ALTER TABLE ' || tab_name || ' ADD COLUMN extra_info text';
-EXCEPTION WHEN insufficient_privilege THEN
- RAISE NOTICE 'captured expected error: insufficient_privilege during
ALTER';
+EXCEPTION WHEN wrong_object_type THEN
+ RAISE NOTICE 'captured expected error: wrong_object_type during ALTER';
END $$;
-- Test Manual INSERT on conflict log table
@@ -727,7 +727,7 @@ BEGIN
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 insufficient_privilege THEN
+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'),
@@ -745,7 +745,7 @@ BEGIN
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 insufficient_privilege THEN
+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'),
@@ -763,7 +763,7 @@ BEGIN
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 insufficient_privilege THEN
+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'),
@@ -781,7 +781,7 @@ BEGIN
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 insufficient_privilege THEN
+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'),
@@ -799,7 +799,7 @@ BEGIN
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 insufficient_privilege THEN
+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'),
@@ -817,7 +817,7 @@ BEGIN
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 insufficient_privilege THEN
+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'),
@@ -835,7 +835,7 @@ BEGIN
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 insufficient_privilege THEN
+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'),
@@ -853,7 +853,7 @@ BEGIN
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 insufficient_privilege THEN
+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'),
@@ -871,7 +871,7 @@ BEGIN
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 insufficient_privilege THEN
+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'),
@@ -889,7 +889,7 @@ BEGIN
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 insufficient_privilege THEN
+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'),
@@ -907,7 +907,7 @@ BEGIN
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 insufficient_privilege THEN
+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'),
@@ -925,7 +925,7 @@ BEGIN
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 insufficient_privilege THEN
+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'),
diff --git a/src/test/subscription/t/035_conflicts.pl
b/src/test/subscription/t/035_conflicts.pl
index 4260c4a5803..3c2d2debf54 100644
--- a/src/test/subscription/t/035_conflicts.pl
+++ b/src/test/subscription/t/035_conflicts.pl
@@ -724,7 +724,7 @@ ok( $node_A->poll_query_until(
"ALTER TABLE $clt SET TABLESPACE pg_default");
like(
$stderr,
- qr/permission denied: "pg_conflict_log_\d+" is a conflict log table/,
+ qr/cannot alter conflict log table "pg_conflict_log_\d+"/,
"moving a conflict log table with ALTER TABLE SET TABLESPACE is
rejected");
###############################################################################
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;
