Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wZ3D9-000jXD-1q for pgsql-hackers@arkaria.postgresql.org; Mon, 15 Jun 2026 09:02:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wZ3D8-00AzQ8-1Q for pgsql-hackers@arkaria.postgresql.org; Mon, 15 Jun 2026 09:02:26 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wZ3D7-00AzPy-2x for pgsql-hackers@lists.postgresql.org; Mon, 15 Jun 2026 09:02:26 +0000 Received: from mail-pl1-x630.google.com ([2607:f8b0:4864:20::630]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wZ3D6-00000000S4i-1UUt for pgsql-hackers@lists.postgresql.org; Mon, 15 Jun 2026 09:02:25 +0000 Received: by mail-pl1-x630.google.com with SMTP id d9443c01a7336-2c0aa420401so22104535ad.3 for ; Mon, 15 Jun 2026 02:02:23 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1781514143; cv=none; d=google.com; s=arc-20240605; b=YrPPF2HTzXuSCJBT0WhZgd7sn3e9fZqW1s+h8z+LkIVLq8cK08FaxNgponPqj/ZeKa xHEoJATqD4uf9yAFekpAHxPdW7NFDF5xMzg7IYdam1CAB5ljejxG5TQ15NGkdmXP41KK Sgj4tuhNQtiYFsrGrx4hPJcPE0ha8D8ZwEXat6RfRUs2RyReXJOJVwhmmXfKRqYCo9DN VjDMzr70X0FR3+4KlsTzIor0xyBmHkEWgiILkl8Jx3l1bkPVtswUX+kfoYTBZqWGGNW3 jauaj6u36D7WqbqXfeJnMMd1ZCTlEr+S1K69gaqPW8oyVkcamkJ74z3Q+XBLOpntm+qA p49A== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:dkim-signature; bh=D0/mCdmS1N6ybqhRaHsi2Ju/x74zibdunWH8PP+HBig=; fh=Q4aPS6RJ60LOPG0wwJOvSI69ETwiPEeuj2aUcGrNEs4=; b=J77O34QMquF0zsSyPL8nlqkQHXReYflw3CORwQSZSTM/HMA5yhH2pbJS1peEz6UT47 xiwDbLZN979Z/ynhi3s+dmtj7unpuC9jfULyCwzvZHWBD8t2JzwEC/KI2OBuFCEXBqTK Bydo/ZnUoSD6K+7R8BHOACR+jRtRK79zatg8wXCTKfqWGYgU6Jeiv6Vc03kizJZBgN2E aqz3YfmEWRB6nS1DWUYjUBf1ZMHzBBZAn6uSI9qhfnc2Wp1Smv3OcwKau2hEX26wpgnD PL2ViB25YE7L6P7Xya7NlgJcgc0OyXLmnQUXcecSZt36eOaMjzih0L7Lyc3vRN4Ee2ki sZEA==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1781514143; x=1782118943; darn=lists.postgresql.org; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=D0/mCdmS1N6ybqhRaHsi2Ju/x74zibdunWH8PP+HBig=; b=ET6azTcVV4f1jJhQAxw1UnwKVq/XAeu1CwHhu5WsdvfRxXdEfZQfb6YXCVsqx6EauQ QU6Nh8nhYm8rTVDj+ddwOBPAwlG7rg8qjdzAbVySwishya862IhTlAgUyAyQAHhgUWu1 Nvzbw5B9HWmyDhPWnugHBnb1mIyZcrbUH9g0iqsQ01GRfHl3VZnRrfNkd223JZlo3fpY zE0xneZcZs4KocWN1/eWT3qZKR9gyt5l1LqbR9Ce0blSmNhnq+feoSe4rRhGpCfA12VW 4B5eD+0vjTnXnP96NMxs2X7ubLtmabrYUcz1/A2dzDWgVfmPPq8HeBXagOmAHWiGqh3c ikkg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1781514143; x=1782118943; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:x-gm-gg:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=D0/mCdmS1N6ybqhRaHsi2Ju/x74zibdunWH8PP+HBig=; b=r4PdHqs55t07Kr3+Bmb2g3mhyiUYfSG6hzcLgulz7Z4MU1YAFhztS6jRnHGFsBn2ya d8klMnPuucS0jMxCQT+nxgfV6weZyJbrwysuqqVTn7U3JM+Hpw1Tkr++omXUdsgdIyIJ nPRMUEpQpGgu84L/fXynxom2y3qa9BiGxlSTDafBpogtJUEDBNqAQDioQYkdHMGRQh8w C6Hnlf1LuFt7mQgaYfuzbJzYoMkydpN/HOP9KsMS4xc3OYJeRoy5RCoNwY2Ds39NfVJ2 +8EHWkQONYsEnVYC5bNA52CBt+PaQGmXqh8etXaN1du3N4J9CV+AZjkmsAkjxShVT3jw WQvg== X-Forwarded-Encrypted: i=1; AFNElJ+54mUobk3Dq0oM/rIlIkQX1GVS2L90fbwqK9KmL8knosfAouOo+Lp8JtCQcQ83bpE2IJhAbVj6FzQsPSVY@lists.postgresql.org X-Gm-Message-State: AOJu0YznpodC0UdraQUV4EYBt3o/Jtkgiv0a0tIERHkvQoxk97tNEFe8 UMD1+qYkcvVndoNaq+7vFlogOQglLioG6A/f1qX/7UxWYG8ADKqkBSHiwH5/C4f8t0wNh5Ir5Wy CySMbhBIe4Rbx9cr8AHpyFnpeaT/2om4= X-Gm-Gg: Acq92OE0m9KR/yYcmhVBnFb3BpaVb7OoAP3HeSO0R8RM3+vQbTP5WAekqwkHjU/Ebuw Qb2PilRLapDdzBLN2JUwXrL9torFKe0ZWqRPW0m4X3ST7bLPwgFUWTa9lYspNqayGS7Ah4Oqy/n e77md+YQCpKhZqTf7H8Q0eHkmXqrgX2vD05awAM9l2N3T9Z/DkqD3K45EcsyKX+62NQ65P56W2r 7GIeh2rW5modQbUOq1leuQydgbMBx84TTjBPMaEayoNJ9BF1PEJQX8ermNnrfUy+GiSoSn1J/KG JY4XNXsWn9oJifi8JzPGWEA/MKUdrcnXNLFYYsOC02P7USbs05Np X-Received: by 2002:a17:903:1b67:b0:2bf:172d:ef7e with SMTP id d9443c01a7336-2c4135e5c1cmr158173265ad.34.1781514142883; Mon, 15 Jun 2026 02:02:22 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: shveta malik Date: Mon, 15 Jun 2026 14:32:09 +0530 X-Gm-Features: AVVi8CeGyZ5pz7RxwUij_LDBYlfwaCAUGY1G_RGFJsNuwkfKtZMqxClm6cBHqo0 Message-ID: Subject: Re: Proposal: Conflict log history table for Logical Replication To: Amit Kapila Cc: vignesh C , Dilip Kumar , Nisha Moond , Peter Smith , Masahiko Sawada , Bharath Rupireddy , PostgreSQL Hackers , shveta malik Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Sharing my thoughts: On Mon, Jun 15, 2026 at 2:20=E2=80=AFPM Amit Kapila wrote: > > On Mon, Jun 15, 2026 at 11:57=E2=80=AFAM vignesh C = wrote: > > > > While reviewing operations on the pg_conflict schema, I noticed a few > > behaviors that I wasn't sure were intentional. > > 1. REINDEX is allowed on conflict log tables > > postgres=3D# REINDEX TABLE pg_conflict.pg_conflict_log_16404; > > REINDEX > > > > I was not sure whether allowing REINDEX on conflict log tables is > > intentional, given that these are system-managed tables. > > > > I think this should be disallowed. +1 I think REINDEX is harmless for CLT as curently there are no indexes, so it should be okay to let it be. For toast-tables too, we have simialr behaviour. We restrict index creation while REINDEX is allowed: postgres=3D# show allow_system_table_mods; allow_system_table_mods ------------------------- on (1 row) postgres=3D# create index on pg_toast.pg_toast_826 (chunk_id); ERROR: cannot create index on relation "pg_toast_826" DETAIL: This operation is not supported for TOAST tables. postgres=3D# REINDEX TABLE pg_toast.pg_toast_826; REINDEX > > > 2. Views are disallowed, but functions are allowed > > Creating a view in the pg_conflict schema is rejected: > > postgres=3D# CREATE VIEW v1 AS > > SELECT * FROM pg_conflict.pg_conflict_log_16435; > > ERROR: permission denied to create "pg_conflict.v1" > > DETAIL: Conflict schema modifications are currently disallowed. > > Okay, I can create views outside the pg_conflict schema for CLT tables, but creation fails inside pg_conflict irrespective of 'allow_system_table_mods '. postgres=3D# CREATE VIEW public.v1 AS SELECT * FROM pg_conflict.pg_conflict_log_24576; CREATE VIEW postgres=3D# CREATE VIEW pg_conflict.v1 AS SELECT * FROM pg_conflict.pg_conflict_log_24576; ERROR: permission denied to create "pg_conflict.v1" DETAIL: Conflict schema modifications are currently disallowed. While for toast table, behaviour is slightly different. With allow_system_table_mods =3D off postgres=3D# CREATE VIEW public.v2 AS select * from pg_toast.pg_toast_826; CREATE VIEW postgres=3D# CREATE VIEW pg_toast.v2 AS select * from pg_toast.pg_toast_826= ; ERROR: permission denied to create "pg_toast.v2" DETAIL: System catalog modifications are currently disallowed. With allow_system_table_mods =3D on, view creation inside TOAST schema work= s: postgres=3D# CREATE VIEW pg_toast.v2 AS select * from pg_toast.pg_toast_826= ; CREATE VIEW IMO, creating views can be allowed on CLT outside of CLT schema as it can help DBAs to analyze the data better. For creating views inside the CLT schema, we currently allow table creation (see my last email) in the CLT schema as well. IMO, we can either disallow both or allow both. The behaviour should be consistent. > > However, creating a function in the same schema succeeds: > > CREATE FUNCTION pg_conflict.get_conflict_count() > > RETURNS bigint > > LANGUAGE sql > > AS $$ > > SELECT count(*) FROM pg_conflict.pg_conflict_log_16404; > > $$; > > CREATE FUNCTION > > > > This is okay because the function is doing SELECT which we allow on > these tables. +1 > > > I noticed similar behavior with the pg_toast schema as well, where > > function creation is allowed: > > > > CREATE FUNCTION pg_toast.get_toast_1213_count() > > RETURNS bigint > > LANGUAGE sql > > AS $$ > > SELECT count(*) FROM pg_toast.pg_toast_1213; > > $$; > > CREATE FUNCTION > > > > I am not sure what the rationale is for permitting some object types > > while rejecting others. > > > > 3. Functions can be created, but triggers cannot: > > Although function creation succeeds, trigger creation on a conflict > > log table is rejected: > > CREATE TRIGGER conflict_audit > > AFTER INSERT > > ON pg_conflict.pg_conflict_log_16435 > > FOR EACH ROW > > EXECUTE FUNCTION get_conflict_count(); > > ERROR: permission denied: "pg_conflict_log_16435" is a conflict log ta= ble > > DETAIL: Conflict log tables are system-managed tables for logical > > replication conflicts. > > > > Again, I wasn't sure whether this distinction is intentional. > > > > I have given the reason for functions above and this restriction is okay. Functions are allowed while triggers are not. IMO, it is fine. Using triggers, one can simply skip or perform other actions for conflict insertions in CLT which should be strictly avoided. > > > 4. User-defined types and domains are allowed > > CREATE TYPE pg_conflict.conflict_status AS ENUM ('ACTIVE', 'INACTIVE'); > > CREATE TYPE > > > > CREATE DOMAIN pg_conflict.positive_int > > AS integer > > CHECK (VALUE > 0); > > CREATE DOMAIN > > > > I was also surprised that creating types and domains is permitted in > > the pg_conflict schema. > > > > Overall, I am having some difficulty understanding the rules governing > > which operations are allowed and which are disallowed in the > > pg_conflict namespace. If these behaviors are intentional, would it > > make sense to document the supported and unsupported operations more > > clearly? That would help avoid confusion for users. > > > > Ideally, we should allow only SELECT, DELETE, and TRUNCATE for > allowing users to view and maintain the CLT tables. We are also > planning to allow the LOCK command for the purpose of pg_dump. Again, returning to my previous statement: table creations, view creations, and type creations=E2=80=94either all should be allowed or none.= I do not see a strong use case for allowing tables inside the pg_conflict schema. But since we allow these things inside other system schemas, we can allow it here as well. I don't have a strong case for or against these. postgres=3D# create table pg_catalog.pg_conflict_log_16502(i int); CREATE TABLE postgres=3D# create view pg_catalog.v1 as select * from pg_conflict_log_165= 02 postgres-# ; CREATE VIEW postgres=3D# CREATE TYPE pg_catalog.conflict_status AS ENUM ('ACTIVE', 'INACTIVE'); CREATE TYPE thanks Shveta