public inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Zhang Mingli <zmlpostgres@gmail.com>
To: Kirill Reshke <reshkekirill@gmail.com>
To: Yugo Nagata <nagata@sraoss.co.jp>
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, 12 Sep 2025 14:09:47 +0800
Message-ID: <f73bd7c7-5188-4658-9749-6e4b4453fb7b@Spark> (raw)
In-Reply-To: <20250830050403.73fd450cd2712acca11494a8@sraoss.co.jp>
References: <20230828115252.c1b018605b9a0756a30c3382@sraoss.co.jp>
<20230902.204634.955758704959569058.t-ishii@sranhm.sra.co.jp>
<CACJufxFjankFQDNppOfqCTpY=zW4Q0+2WCmKjT95kggiT978Lw@mail.gmail.com>
<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>
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.
--
Zhang Mingli
HashData
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: zmlpostgres@gmail.com, reshkekirill@gmail.com, nagata@sraoss.co.jp, smithpb2250@gmail.com, jian.universality@gmail.com, ishii@sraoss.co.jp
Subject: Re: Incremental View Maintenance, take 2
In-Reply-To: <f73bd7c7-5188-4658-9749-6e4b4453fb7b@Spark>
* 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