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 1vfzE3-006mKx-2R for pgsql-hackers@arkaria.postgresql.org; Wed, 14 Jan 2026 11:39:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vfzE2-00A6J5-3B for pgsql-hackers@arkaria.postgresql.org; Wed, 14 Jan 2026 11:39:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vfzE2-00A6Ix-25 for pgsql-hackers@lists.postgresql.org; Wed, 14 Jan 2026 11:39:46 +0000 Received: from mail-lf1-x12a.google.com ([2a00:1450:4864:20::12a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vfzE0-000Pyd-2u for pgsql-hackers@lists.postgresql.org; Wed, 14 Jan 2026 11:39:46 +0000 Received: by mail-lf1-x12a.google.com with SMTP id 2adb3069b0e04-59b855a4700so3402998e87.0 for ; Wed, 14 Jan 2026 03:39:44 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1768390784; x=1768995584; 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=tR2UyLE5AvhA9aitDXcsV9rse01kFX3A5EbkCBvtSjw=; b=ZxFkpAi+TydmHZyZ+fDcERPUkj5yA/k+4qZSGelQhIvvE4ZXhF53cL5Y2KWf6qea9G ICenJjdlk07GWqQj9svPZnB3v8zizdlmhoeKR3woLMn6fA4/AeC5xJx8ZULuejVPu82h lB3yJOy+YnDh7+ENSSnXtCmeTX9GI5URPcELXTYZkazHQfhKz3RBD5Gbe6jrO0drcXbb VdVWUdoheG2001NqrOqRnkPZHKDHQI6WDvYRDi3OUcHr2YH/t0UDBArDGgGjGAy0EgUX csTTsqZ6npGHpg/r8shrSvEt/X2SlfMmQuRaEBUB05Aw6ge7d8n6RwzJWO/+STOcAp8v gr1g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1768390784; x=1768995584; 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=tR2UyLE5AvhA9aitDXcsV9rse01kFX3A5EbkCBvtSjw=; b=p+qD9PnbeEOhapiSHrX4pP1ExqTgoXYYNC2oREdvWrdmHoTvIi3YxX70PZR34O73VE rhaBHxl9LmBU/a15DIXp7+mp5WpkOW3SiCR3qK/+1OoVyXsvPiDluvdiNd29v1qDS65L I+oxtQfEIlj0wccI4yZIrMq83Brd1G4AKGqQZS36K/eqo9L0pvOm3sFT3EgceOQHk/XI HA1jepR9OtsnLAS5yB+zX82zF/AN7hgOUidX9q9GnPW9/IHwRS+HyA2l0v6pMGmajX8d ZE0G0moWz24zz59qqx52DbbJj4b0D8MhuRr0hgXC1zdQhB794MAoBsMry+GgeKvDQZ7g 1TXA== X-Forwarded-Encrypted: i=1; AJvYcCU41193pbqLfZlmatjqsAg79KoD1NzbxQvfEGFxYMt3xzYtDO51uPcD/JMArvDZw9I8sz5VDByg5hO/M/io@lists.postgresql.org X-Gm-Message-State: AOJu0Yw4SuIyZd8w1U++mt7tn2/gnNlYj1/+rtv2q4nn+GrIc6SEHsXm YZOBpdGJWtaw52QKoFYKEhvaXpIXC9glDJWLvKe/oZlTG9tSkN99jzH1HxjPZqKnN7bkVo5Gwx4 htMBvSsx/RKz2nrvNu5npRWd6DIVsOs8= X-Gm-Gg: AY/fxX70U3ofNPcaqGFCdDGAAgLIkNppWXcRsGtTadVwMsZdz4IuXBWxhNgiJ4oM0Jn pD+mEpxdTXMhX6D6vB0d7xAPdbj+zulVGJ4sNOV3tn2vguYXe4ML9vFM67qs4UQC9xOWiUCN0C4 piBbygZG+ANSmMutXN/BO/HL/sKH2SkoZtetdeueL7zIcvJSz3Xw7MEKItc4bbpdMBE0gHjaA6F Ofas1eit4PNe/HDkW/Z3j++twqqXhe5E70i8xtqzbowKt/eVlO4by91DAwn9othegL9sOs= X-Received: by 2002:a05:6512:1150:b0:59b:715b:4fee with SMTP id 2adb3069b0e04-59ba151b48cmr622500e87.10.1768390783645; Wed, 14 Jan 2026 03:39:43 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Dilip Kumar Date: Wed, 14 Jan 2026 17:09:26 +0530 X-Gm-Features: AZwV_QhpkE1il-b7JhA6LSjFCp6kv4AgfB3LSM32mPmYHcjdEWC6CdjarX1d-xI Message-ID: Subject: Re: Proposal: Conflict log history table for Logical Replication To: Amit Kapila Cc: shveta malik , Peter Smith , vignesh C , Masahiko Sawada , Bharath Rupireddy , PostgreSQL Hackers 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 On Wed, Jan 14, 2026 at 3:59=E2=80=AFPM Amit Kapila wrote: > > On Tue, Jan 13, 2026 at 6:15=E2=80=AFPM Dilip Kumar wrote: > > > > On Tue, Jan 13, 2026 at 5:02=E2=80=AFPM shveta malik wrote: > > > > > > On Tue, Jan 13, 2026 at 4:09=E2=80=AFPM Dilip Kumar wrote: > > > > > > > > On Tue, Jan 13, 2026 at 3:59=E2=80=AFPM shveta malik wrote: > > > > > > > > > > On Sat, Jan 10, 2026 at 6:45=E2=80=AFPM Dilip Kumar wrote: > > > > > > > > > > > >> > > > > > > Here is the updated patch > > > > > > > > > > > > > > > > Thanks, I will review the code. Few concerns while experimenting = with 001 alone: > > > > > > > > > > 1) > > > > > I am able to insert and update rows in pg_conflict.pg_conflict_16= 394. > > > > > It should be restricted. > > > > > > > > > > 2) > > > > > I think we need to block 'SELECT for UPDATE' and similar operatio= ns on > > > > > CLT. Currently it is allowed on CLT. > > > > > See this: > > > > > > > > > > postgres=3D# SELECT * FROM pg_toast.pg_toast_3466 for UPDATE; > > > > > ERROR: cannot lock rows in TOAST relation "pg_toast_3466" > > > > > postgres=3D# SELECT * FROM pg_conflict.pg_conflict_16394 for UPDA= TE; > > > > > .... > > > > > > > > I sent an analysis on this a few days ago but realized it only went= to > > > > Amit. Resending to the full list: > > > > > > > > So the summary is, currently, all INSERT/UPDATE/DELETE operations a= re > > > > blocked for TOAST tables for safety. However, I have allowed these > > > > operations for the pg-conflict table. Unlike TOAST, the system does > > > > not internally reference conflict data, so user manipulation doesn'= t > > > > pose a safety risk to the system. If a user modifies or deletes thi= s > > > > data, they simply lose their logs without impacting system stabilit= y. > > > > What are your thoughts on this approach? > > > > > > > > > > I don=E2=80=99t see a strong reason for a user to manually perform IN= SERT or > > > UPDATE here. But on rethinking, I also agree that allowing it does no > > > harm. It simply gives the user flexibility to modify data they =E2=80= =9Cown=E2=80=9D, > > > i.e., data that the system does not internally reference. Personally, > > > I=E2=80=99m okay with leaving it unrestricted, but it will be good to= document > > > it as a NOTE/CAUTION, stating that these DML operations are allowed > > > and it is the user=E2=80=99s responsibility to manage any changes the= y make > > > manually. > > > > To clarify, I=E2=80=99m allowing INSERT and UPDATE alongside DELETE not > > because I anticipate a specific use case, but to avoid adding > > unnecessary code complexity. Since restricting these operations isn't > > a safety requirement, I felt it was better to keep the implementation > > simple rather than adding extra checks that don't provide real value. > > > > What kind of complexity you are anticipating, can you show by top-up > patch? I think allowing just DELETE and TRUNCATE to table owners would > be ideal. I mean by code complexity i.e. additional check for DELETE/TRUNCATE, but if we think that's ideal, I can update it. > > So let's get consensus on this decision and then we can change accordin= gly. > > > > > > > > > > >> I Wrote > > > > > I have been exploring the enforcement of these restrictions. > > > > > Currently, DML is validated via CheckValidResultRel(). If we do n= ot > > > > > explicitly check for the conflict log table in that function, DML > > > > > operations, including DELETE, will be permitted. While I am not o= verly > > > > > concerned about users attempting manual INSERT or UPDATE operatio= ns. > > > > > > > > > Now for TRUNCATE, the truncate_check_rel() currently relies on > > > > > IsSystemClass() to restrict truncations. Since the conflict names= pace > > > > > is included in IsSystemClass() to prevent unauthorized DDL, TRUNC= ATE > > > > > is blocked by default. We could allow it by adding an exception i= n > > > > > truncate_check_rel() (e.g., IsSystemClass() && > > > > > !IsConflictNamespace()), but I am unsure if this is necessary. Do= we > > > > > really need to permit TRUNCATE, or is allowing DELETE sufficient = for > > > > > user-driven cleanup? > > > > > > > > > > I am okay if we allow DELETE alone. > > > > Thanks for your opinion. > > > > > > > > > > > > > > > > > 3) > > > > > We currently skip ANALYZE on TOAST tables, but I=E2=80=99m not su= re whether > > > > > the same restriction should apply to CLT. Since users are expecte= d to > > > > > query CLT frequently, collecting statistics could be beneficial. = Even > > > > > though there are currently no indexes or other structures to enab= le > > > > > more optimal plans, having statistics should not harm. Thoughts? > > > > > > > > > > postgres=3D# analyze pg_toast.pg_toast_16399; > > > > > WARNING: skipping "pg_toast_16399" --- cannot analyze non-tables= or > > > > > special system tables > > > > > > > > > > postgres=3D# analyze pg_conflict.pg_conflict_16394; > > > > > ANALYZE > > > > > > > > I think its good to ANALYZE CLT data because user logically never n= eed > > > > to query the toast data, but they would have to query the conflict > > > > data. > > > > > > Right. Do you think we shall allow index creation as well on this > > > table? It may help if the table is huge. As an example, a user can > > > create an index on relid to query it faster. Currently it is not > > > allowed. > > > > That=E2=80=99s an interesting point. I initially considered creating in= ternal > > indexes to simplify management and avoid requiring users to have DDL > > permissions on the pg_conflict schema. However, I realized that > > predefined indexes might be too restrictive for diverse search > > requirements. Perhaps we should omit indexes from the initial > > version? We can then decide whether to add predefined indexes or allow > > external ones based on actual usage patterns and feedback. Thoughts? > > > > I agree that the indexes could be considered later unless required by > internal code to search the conflict table. Thanks. --=20 Regards, Dilip Kumar Google