public inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Yugo Nagata <nagata@sraoss.co.jp>
To: Zhang Mingli <zmlpostgres@gmail.com>
Cc: Kirill Reshke <reshkekirill@gmail.com>
Cc: Peter Smith <smithpb2250@gmail.com>
Cc: jian he <jian.universality@gmail.com>
Cc: Tatsuo Ishii <ishii@sraoss.co.jp>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Incremental View Maintenance, take 2
Date: Fri, 22 May 2026 17:40:12 +0900
Message-ID: <20260522174012.1640555cc2e4736e6a9d0490@sraoss.co.jp> (raw)
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>
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>
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nagata@sraoss.co.jp, zmlpostgres@gmail.com, reshkekirill@gmail.com, smithpb2250@gmail.com, jian.universality@gmail.com, ishii@sraoss.co.jp
Subject: Re: Incremental View Maintenance, take 2
In-Reply-To: <20260522174012.1640555cc2e4736e6a9d0490@sraoss.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox