Re: Implementing Incremental View Maintenance

On Mon, 23 Dec 2019 08:08:53 +0000
"tsunakawa(dot)takay(at)fujitsu(dot)com" <tsunakawa(dot)takay(at)fujitsu(dot)com> wrote:

> From: Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>
> > 1. Create a temporary table only once at the first view maintenance in
> > this session. This is possible if we store names or oid of temporary
> > tables used for each materialized view in memory. However, users may
> > access to these temptables whenever during the session.
> >
> > 2. Use tuplestores instead of temprary tables. Tuplestores can be
> > converted to Ephemeral Name Relation (ENR) and used in queries.
> > It doesn't need updating system catalogs, but indexes can not be
> > used to access.
>
> How about unlogged tables ? I thought the point of using a temp table is to avoid WAL overhead.

Hmm... this might be another option. However, if we use unlogged tables,
we will need to create them in a special schema similar to pg_toast
to split this from user tables. Otherwise, we need to create and drop
unlogged tables repeatedly for each session.

>
> One concern about the temp table is that it precludes the use of distributed transactions (PREPARE TRANSACTION fails if the transaction accessed a temp table.) This could become a headache when FDW has supported 2PC (which Sawada-san started and Horicuchi-san has taken over.) In the near future, PostgreSQL may evolve into a shared nothing database with distributed transactions like Postgres-XL.

This makes sense since you mean that PREPARE TRANSACTION can not be used
if any base table of incrementally maintainable materialized views is
modified in the transaction, at least in the immediate maintenance. Maybe,
this issue can be resolved if we implement the deferred maintenance planned
in future because materialized views can be updated in other transactions
in this way.

>
>
> Regards
> Takayuki Tsunakawa
>
>
>

--
Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>