Date: Fri, 22 May 2026 17:40:12 +0900
From: Yugo Nagata <nagata@sraoss.co.jp>
To: Zhang Mingli <zmlpostgres@gmail.com>
Cc: Kirill Reshke <reshkekirill@gmail.com>, Peter Smith
 <smithpb2250@gmail.com>, jian he <jian.universality@gmail.com>, Tatsuo
 Ishii <ishii@sraoss.co.jp>, pgsql-hackers@postgresql.org
Subject: Re: Incremental View Maintenance, take 2
Message-Id: <20260522174012.1640555cc2e4736e6a9d0490@sraoss.co.jp>
In-Reply-To: <f73bd7c7-5188-4658-9749-6e4b4453fb7b@Spark>
References: <20230828115252.c1b018605b9a0756a30c3382@sraoss.co.jp>
	<CAHut+PsDpBTxZ7bLhko7_E-C7khMhoNJcriNQ_p_gWjADn01vg@mail.gmail.com>
	<20240123162327.c2803162619dd7634cca0b6c@sraoss.co.jp>
	<20240304115846.2275fb44fd904e8789d43590@sraoss.co.jp>
	<20240329234700.73ff2e28c9248d29f8fa6a66@sraoss.co.jp>
	<20240331225931.712683cecb26862b73b2b822@sraoss.co.jp>
	<20240702170311.1ddb417759a48ff12c555b92@sranhm.sraoss.co.jp.sranhm>
	<20240711132357.fe3f78c184cfa99159208178@sranhm.sraoss.co.jp>
	<CALdSSPhj1H1NS7QiYkSQNCksPCwjtLcyt3==evgkBX1SrKyVdQ@mail.gmail.com>
	<CALdSSPip9ruUoQMmsD_hQ0xY72qB=_jB-ayHeUWUH-dd0MB60A@mail.gmail.com>
	<20240730142420.34a9ad7c249aecde88cd45fb@sraoss.co.jp>
	<CALdSSPiBBBCHxVtg+X6OdBkJPGYOvLf1hST4MgBgRKZh0Xddyw@mail.gmail.com>
	<CALdSSPg1mPiZRXjFEsz3h_5Jue0rq0w9BJzBrcVtwz5J29d_3w@mail.gmail.com>
	<20250830050403.73fd450cd2712acca11494a8@sraoss.co.jp>
	<f73bd7c7-5188-4658-9749-6e4b4453fb7b@Spark>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 2
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?utf-8?B?MUF4NnFpSURpR3pUMklzNVhudXpocUl5RUlTWFZ5NzMrMDRjb01DNnRDV0Zu?=
 =?utf-8?B?R1llWkg1Ym8vZXg0dmZHa0xFR0xQT2VmOGpiaGpZditKN1U2NVFhU1krYkRl?=
 =?utf-8?B?TVc5VW93NGdMRWw4WWFFbTVzU1BUTldJYWJHaml5M1Y3U1lMSW9DdnpjWlly?=
 =?utf-8?B?dGcvV2RYNTVpRFFxSzZteUUxUWVzc01qazROWnBFb0c3SWpxQnY3MjhQNUl2?=
 =?utf-8?B?SGNxUGZnNXh6NGx3MDBCSXZlb0lVRmFXeEJYV3h0SzRZVnoxMXZUVFk2alQ1?=
 =?utf-8?B?OVg2TmZRdEd6Nzdqc3BKMDFaQzJxdHdlanB4QlFFdGZ1Wmtmd1ArTit1WkFs?=
 =?utf-8?B?NGltYi9CVFRuNnNWZFVreU0rRGRZNkQwQzVCS0VVNXBsSXdhbzNmOUVHZjU4?=
 =?utf-8?B?WGhwVUlucnVYY2l6UVVROWF3MEkzMVhHa251UXVYZUdZdjBHZkJsUDNsZlQw?=
 =?utf-8?B?clRKak9TYXRSanBCVWJzRDl3UmJDK2FFSkVyRVRPNG1jaFlYM0tsQS92YjFX?=
 =?utf-8?B?Nyt6M0JlY3d2SS9Fcm83dHBTVkYvSlZ1bDNYd3ErYlRIV1g5d1dQMjZBY2xP?=
 =?utf-8?B?MEdydUh2SUxrZDlqMGNSejhLR1VNbkxPaWRVR2tCY3FFOXBuTmYyU1lMR1Vz?=
 =?utf-8?B?K1lqK3lMTFZkOUNqT0JHM05ZZHdSMVFKREtIS1g1RFNNMnEzKzREcG96MlFP?=
 =?utf-8?B?YmMzZnRLYmVzRTdsZ3pmZGV1V053V0hNdVBLaWxzNjRrVEhXK1BzUW1aVUhH?=
 =?utf-8?B?Q2RvNWsyWFlHaGZyMVlyTy93VnBZc01jMTJmUUZkOUltR2cvd0ZkZkR6YmFH?=
 =?utf-8?B?bkRHcGYyN2lqdEh4T1lxMUp0WG1iQktVcnNTV04xVzhXVzBFY3dMcjV5VUJm?=
 =?utf-8?B?b1ZIWTF4RG5VdCt3b3pqRXpQclpXWXlGVzdFMTBmdXh6NEg0dFM0NmZzQnRj?=
 =?utf-8?B?NUlpNGhSWWtxNnhCV3dnNWFqSmNtWnNHMEdYSndkNThPeEljNnRzQnZ0Rytw?=
 =?utf-8?B?d3FIVWxVd1R4dGEyaEZGbC83Wk9STC9FcmVzSU5sbTJ6eGFLQU8zNjN2S29D?=
 =?utf-8?B?a2pPWmFuT0tlb0NhM29FQ3ZwekJqQmY4T3d4bWpHV0FZYnVhcTVuUzhldDJ6?=
 =?utf-8?B?TC9yTEk1RGwvMTdHdnlaT0JWSXJ0RTdLLzhhMXQyNFg4WjVRWVAwVkk2ekdW?=
 =?utf-8?B?bEhlWDQrTm9RblJFY1FWeXJiQ2NFMjNRZUhUVXdFL3BjQ0NLdkU1UjZuaXBU?=
 =?utf-8?B?bnBUTmJtVTdOeVpxL2Y0OU9iUDdVc2hWb1dwWXlPdWpPKzFsMzk5OFp3MmxN?=
 =?utf-8?B?YjlOV0I5NlJCN1hxelpCZi9NQXN3WVl2RVNFN3pzb3B1eWxXczE1WXA1dHR4?=
 =?utf-8?B?QXEzUk85Q2hXRG5XaUFxVUt3N05INWpFQ2tCeFBKVndBZ1RueWFXZmcyVFh1?=
 =?utf-8?B?VTBxS2ROS2pCSHFvWTAvT2kvbnRaTnFYdXRUemJ2NjEvOTJiTHNmZzJCbzJi?=
 =?utf-8?B?ZGJXRXorQ3g3S0R3TWtaeUs2aWxmQjkyVGtKdStkK0VVMXJFdlVhZ29uQmpp?=
 =?utf-8?B?ZmhKc2ZtV0l6dTBKdEQ3V01BdnIva3hQZTh1a2ZhOW5URnRRcHJhMmNMZmxD?=
 =?utf-8?B?azFaeXBiT2tiL3hzYVRUdFRCNW50dmRlWnRtaU1TNTVyWitpVk9LK29QekRY?=
 =?utf-8?B?NnErUDdEYzFHK0srSG4reXpPczRkVnVIOTBjdTZ0blJxN3BSSzFxMUlYazFI?=
 =?utf-8?B?cHJYZW1yU3NvRi92ZStER0Y2aEN6WlZySUc2amIzckFxam1rU0M3M0tQRlVy?=
 =?utf-8?B?cWdYclo1ZURGK2tvVUlQS3ZNL040bDV6M3FFSU9IbWZKRU1sa2tyMTM2MDVn?=
 =?utf-8?B?RC9jWTNjWjBLcitOVmI0bm5walh2R2dENzY1Vnk2cFdCWFVTK1JkbGM0cmpD?=
 =?utf-8?B?UXpsdHFZeE9ucWNzZ3cvQllBMHZXc25iZHNxdXgwMkdGTmdyQjZJWWxuMy9s?=
 =?utf-8?B?a0xwam9yZk40WHpKdTc2a2NxMXc1R0RJL3BIV05NcmVTbFR3NFFhS1BSM3V3?=
 =?utf-8?B?NXgreWwzL3E5RFJIMnVuK1RKQXFKMG9GMXZNRFhLTGloU2c3ZFZ4Q1k4ajVN?=
 =?utf-8?B?Z3p1OFVzRk1tVDd4dXcrbWFLZ0hPZDNyUjVHUFdqQTdydjBua3lKVjk2bFBq?=
 =?utf-8?B?dVBDY0VwazRMSEtTQytJUVBxQnE4RGZLNnZpSUplY0NGRXdNSk5ZVnJiK2lF?=
 =?utf-8?B?WWZSSXo0YXowUzh1cXZNTUU2bW1DZHZQTTVVUVNmRTFrSlZZWWpxM25xdzk5?=
 =?utf-8?B?MUxSdmhJUERuMDRVMGszMHVJcUR1V0REVEFZUS82VS9uYzVMQ3E0VXZuYS9n?=
 =?utf-8?Q?U8C6EtPqbXFDUVSh30eLTGxTExUyKcfzocad75j9f2sA2?=
X-MS-Exchange-AntiSpam-MessageData-1: ok4++BRzfLIaUw==
X-OriginatorOrg: sraoss.co.jp
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 34ba79e2-22c2-47eb-f4a1-08deb7ddbd85
X-MS-Exchange-CrossTenant-AuthSource: OSZP286MB2160.JPNP286.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 22 May 2026 08:40:13.0244
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 75db6c32-352e-4063-ae79-cafb8623f6db
X-MS-Exchange-CrossTenant-MailboxType: HOSTED
X-MS-Exchange-CrossTenant-UserPrincipalName: 
 SKMzRhMa+jEt6t/AlqtLf7JAUBCtCZIxSk+WzNEGO+08ItnOP84qeLayOeH6p6de5XO+KHYXWoiQVbaQcRSbSA==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TYYP286MB5551
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: 
 <https://www.postgresql.org/message-id/20260522174012.1640555cc2e4736e6a9d0490%40sraoss.co.jp>
Precedence: bulk

On Fri, 12 Sep 2025 14:09:47 +0800
Zhang Mingli <zmlpostgres@gmail.com> wrote:

> Hi, all
> 
> On Aug 30, 2025 at 04:04 +0800, Yugo Nagata <nagata@sraoss.co.jp>, wrote:
> >
> > Anyway, I will start rebasing the patches, reorganizing the patch set,
> > and applying fixes made in pg_ivm [2].
> 
> First of all, we would like to express our sincere gratitude for your continuous efforts and contributions to the IVM feature.
> Our AQUMV (Answer Query Using Materialized Views) functionality in Apache Cloudberry is built directly upon your foundational work.
> 
> 
> We recently encountered an issue in one of our customer's production environments.
> The problem occurs in the function CreateIvmTriggersOnBaseTablesRecurse(), where a Bitmapset relidsis used to record rte->relid.
> 
> ```
> case T_RangeTblRef:
> {
>  int rti = ((RangeTblRef *) node)->rtindex;
>  RangeTblEntry *rte = rt_fetch(rti, qry->rtable);
> 
>  if (rte->rtekind == RTE_RELATION && !bms_is_member(rte->relid, *relids))
>  {
>  CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_INSERT, TRIGGER_TYPE_BEFORE, ex_lock);
>  CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_DELETE, TRIGGER_TYPE_BEFORE, ex_lock);
>  CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_UPDATE, TRIGGER_TYPE_BEFORE, ex_lock);
>  CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_TRUNCATE, TRIGGER_TYPE_BEFORE, true);
>  CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_INSERT, TRIGGER_TYPE_AFTER, ex_lock);
>  CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_DELETE, TRIGGER_TYPE_AFTER, ex_lock);
>  CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_UPDATE, TRIGGER_TYPE_AFTER, ex_lock);
>  CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_TRUNCATE, TRIGGER_TYPE_AFTER, true);
> 
>  *relids = bms_add_member(*relids, rte->relid);
>  }
> }
> ```
> However,  the Bitmapset structure is typically designed to store small integers (such as relation index), whereas rte->relidrepresents the Oid of a relation.
> For instance, when a table is created immediately after initializing a new cluster, its Oid might be 17019. Storing such a value in a Bitmapset consumes approximately 0.2MB of memory when creating an IVM.
> 
> (gdb) p bmsToString(((Bitmapset *) relids))
> $13 = 0x5643d7bbb070 "(b 17019)"
> (gdb) p *((Bitmapset *) relids)
> $14 = {nwords = 266, words = 0x5643d79d1bd8}
> 
> This memory usage becomes even more significant when dealing with larger Oids.
> Moreover, a more critical issue arises when the Oid exceeds 0x7FFFFFFF. Since Oid is an unsigned integer, but the Bitmapset expects signed integers, an assertion failure will occur in such cases.
> 
> 
> We have encountered similar requirements in our own scenarios and eventually switched to using a HTAB implementation. Hope this proves useful for your work.

Sorry for the late reply. While reviewing the discussion around reworking this, 
I noticed that I never replied to this comment.

That makes sense. I'll update the patch accordingly.

Regards,
Yugo Nagata

-- 
Yugo Nagata <nagata@sraoss.co.jp>