Re: Implementing Incremental View Maintenance

From: Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>
To: "tsunakawa(dot)takay(at)fujitsu(dot)com" <tsunakawa(dot)takay(at)fujitsu(dot)com>
Cc: 'legrand legrand' <legrand_legrand(at)hotmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Implementing Incremental View Maintenance
Date: 2019-12-26 02:36:47
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Tue, 24 Dec 2019 07:07:35 +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>
> > On Mon, 23 Dec 2019 08:08:53 +0000
> > "tsunakawa(dot)takay(at)fujitsu(dot)com" <tsunakawa(dot)takay(at)fujitsu(dot)com> wrote:
> > > 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.
> Maybe we can create the work tables in the same schema as the materialized view, following:
> * Prefix the table name to indicate that the table is system-managed, thus alluding to the user that manually deleting the table would break something. This is like the system attribute __imv_count you are proposing.
> * Describe the above in the manual. Columns of serial and bigserial data type similarly create sequences behind the scenes.
> * Make the work tables depend on the materialized view by recording the dependency in pg_depend, so that Dropping the materialized view will also drop its work tables.

Maybe it works, but instead of using special names for work tables, we can also create
a schema whose name is special and place work tables in this. This will not annoy users
with information they are not interested in when, for example, psql meta-commands like
\d are used.

Anyway, I understood it is better to avoid creating and dropping temporary tables
during view maintenance per statement.

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro Horiguchi 2019-12-26 03:46:39 Re: [HACKERS] WAL logging problem in 9.4.3?
Previous Message Takuma Hoshiai 2019-12-26 02:03:02 Re: Implementing Incremental View Maintenance