Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ijeJG-00066l-L5 for pgsql-hackers@arkaria.postgresql.org; Tue, 24 Dec 2019 07:08:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ijeIG-0008Ob-Or for pgsql-hackers@arkaria.postgresql.org; Tue, 24 Dec 2019 07:07:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ijeIG-0008OO-G0 for pgsql-hackers@lists.postgresql.org; Tue, 24 Dec 2019 07:07:48 +0000 Received: from esa12.fujitsucc.c3s2.iphmx.com ([216.71.156.125]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ijeI9-0000TD-J5 for pgsql-hackers@postgresql.org; Tue, 24 Dec 2019 07:07:47 +0000 IronPort-SDR: c02cScrar46v0fMjA/NHc3GCdXi1q8+FeFWabw3WvEzPF0gdb1VO/Bk8ig0zFo0pBnra9CVZWp 2Y5iqwAcAyVsoWO6vAR8saE9rI2JklBSKcKWk5wLzc48qgMeSA8EonSXtKaz5mPgblbxKPJTSS 9UT5s1VGged4TuCAxhkHbfEZQMxymHeUKaPcPnBzw7hfnwOKU0hORnIrgCNhuXbJQv4+RHDy7a ATyrphTyInN7bod1gbzt82tDkULva4z8HI/PJwGLudAz17QskNFEnLSdqWdFG3Zhwf9QxYQbNZ iPg= X-IronPort-AV: E=McAfee;i="6000,8403,9480"; a="8889499" X-IronPort-AV: E=Sophos;i="5.69,350,1571670000"; d="scan'208";a="8889499" Received: from mail-os2jpn01lp2057.outbound.protection.outlook.com (HELO JPN01-OS2-obe.outbound.protection.outlook.com) ([104.47.92.57]) by ob1.fujitsucc.c3s2.iphmx.com with ESMTP/TLS/ECDHE-RSA-AES256-GCM-SHA384; 24 Dec 2019 16:07:39 +0900 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=KDK0reCQcGcig5AbV2P4T8y/ei1No8gk9rns2H7J9h9q5KnWaoG4YsmRbC4e9W+5zejy/bFMp5sjPr44H4WAj3ndDQII51gBjJB5xtMkD9dlucLnNVm5S0e3L7LOqVY3QeFtoEPX9dP1QcnztT5x62BZhQLx+zIXnF7mGv1pYZ1qqsYhTb2shVHkzga/cwujkPNXrf1csNpTXcgUEi43TaoGwb2nspJNEiBwKzT+sehy5iaDfRWQbm1ux1PiQxZGNLfFr2OdDirvVeh5QfpDBNA9MPQmxOGcJvIXumL7ObX+hKjAKLfZgUwe5L+LL6PNtSl119UT4rIL1TIjO5XibA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=VYPNgf4hhHqWbKYWauU37nyorfAjuTcqxX2oihQoSAU=; b=R54LnreOhdbq9HvzI7VE3VI8jOthwym1gqm29iEHb5IEIA4DUoiCZ6hMzftxOuPebQdBpuu+APhYkhEXuGPxH+8xLeHomivRAsDOFG6TaASh8tFelDkm8bLgX3dT1RMlv23C/ylBSTnyJWyDmMmj+G/L4rpxqFGgzuknzsEC1D/eG4M5YS1sG0BN1QNHnDLcvkbIkLKnByc1YIHrreasNnjWAIONhmPFItMmHCPe/MNqg4LZ5pMWrbQroB8tK8pb6DHbqby5trS/cZgWj/4L+lvcAgkKa7amQOMUl65NdW6ZpYmUa3GtOSpqMSsMDrqGeJj1Z9vG48y7MxsVaPzfVg== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=fujitsu.com; dmarc=pass action=none header.from=fujitsu.com; dkim=pass header.d=fujitsu.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=fujitsu.onmicrosoft.com; s=selector2-fujitsu-onmicrosoft-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=VYPNgf4hhHqWbKYWauU37nyorfAjuTcqxX2oihQoSAU=; b=hucrPXiTKH/RkKne9yfBh10K5anFYYaO4BPP9W2FVJElUlIoEe6ZzJ0KzfqU0UheM9mvU1pUgW/EM2epQrLkVSld+1yLbI/LhJD404Y8fAYRaaR3a3S35Ny3p2tR4nYCVLA5v7CL0M1Z2/xt7lGuW5YZP0LugsWlUBNVgUcVfnQ= Received: from OSAPR01MB5073.jpnprd01.prod.outlook.com (20.179.179.15) by OSAPR01MB1505.jpnprd01.prod.outlook.com (52.134.229.22) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.2559.16; Tue, 24 Dec 2019 07:07:35 +0000 Received: from OSAPR01MB5073.jpnprd01.prod.outlook.com ([fe80::c7:cff3:1b9b:bfc6]) by OSAPR01MB5073.jpnprd01.prod.outlook.com ([fe80::c7:cff3:1b9b:bfc6%3]) with mapi id 15.20.2559.017; Tue, 24 Dec 2019 07:07:35 +0000 From: "tsunakawa.takay@fujitsu.com" To: 'Yugo Nagata' CC: 'legrand legrand' , "pgsql-hackers@postgresql.org" Subject: RE: Implementing Incremental View Maintenance Thread-Topic: Implementing Incremental View Maintenance Thread-Index: AQHVoP5MW2HR/XuY5Ei0uAznagbD+KedDO2AgAADVYCAAtRJAIAAYCsAgADtLACAAF9RgIAAKwYAgAAtBQCAAAEXAIACuTAAgAFygACAABH5AIAce2QAgAQlywCAAGUIgIAASncAgAAVsQCAAByNgIABZKLQ Date: Tue, 24 Dec 2019 07:07:35 +0000 Message-ID: References: <20191128171052.ee3948fad09eb9337fcbbfc9@sraoss.co.jp> <20191129.071944.530831741282571640.t-ishii@sraoss.co.jp> <20191129130053.b2cdf2303ff35e437dd39571@sraoss.co.jp> <20191129181600.7b5dd4167e1753536af33937@sraoss.co.jp> <20191129181954.208da0d63c1ff9e556008e80@sraoss.co.jp> <20191201025514.GY2355@paquier.xyz> <20191202.100118.1160224336488931090.t-ishii@sraoss.co.jp> <20191202110538.a6ff2cd84f488f42eab621eb@sraoss.co.jp> <20191220140232.57b43c4f6296f793d42946b2@sraoss.co.jp> <1577046170225-0.post@n3.nabble.com> <20191223155058.80ab940a318a72d740be59e8@sraoss.co.jp> <20191223185047.b7af596a09e0d09385b1ba1e@sraoss.co.jp> In-Reply-To: <20191223185047.b7af596a09e0d09385b1ba1e@sraoss.co.jp> Accept-Language: ja-JP, en-US Content-Language: ja-JP X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: spf=none (sender IP is ) smtp.mailfrom=tsunakawa.takay@fujitsu.com; x-originating-ip: [210.170.118.172] x-ms-publictraffictype: Email x-ms-office365-filtering-correlation-id: 909506a0-64e3-457d-cb38-08d7883ff44d x-ms-traffictypediagnostic: OSAPR01MB1505: x-microsoft-antispam-prvs: x-ms-oob-tlc-oobclassifiers: OLM:9508; x-forefront-prvs: 0261CCEEDF x-forefront-antispam-report: SFV:NSPM;SFS:(10009020)(4636009)(366004)(376002)(346002)(39860400002)(136003)(396003)(199004)(189003)(85182001)(71200400001)(66476007)(76116006)(54906003)(5660300002)(52536014)(478600001)(66446008)(66946007)(4326008)(64756008)(66556008)(86362001)(7696005)(6916009)(2906002)(3480700005)(6506007)(186003)(55016002)(33656002)(8936002)(9686003)(26005)(81166006)(316002)(81156014)(8676002)(777600001);DIR:OUT;SFP:1101;SCL:1;SRVR:OSAPR01MB1505;H:OSAPR01MB5073.jpnprd01.prod.outlook.com;FPR:;SPF:None;LANG:en;PTR:InfoNoRecords;A:1;MX:1; x-ms-exchange-senderadcheck: 1 x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: h2HlaCyV/xdwFvFRPIw7hP7o36MeSEV/czD/0IbkS8kcmt8JcZ0/0Kr9D41Vs04wSam4mmjwLCUNF5RlYON/osvU+NPBB9n1coLFGz4aWNoqPuHn0k6dqSzvDfTyEir0epl3uGdnq4bGwfvTIPc/J2jv4rGdgIHZrwQdRiruDUztdlY006M9NM/4ZY1p1OQDHlD/m3yeji8IrosQIXR8cskXiRJcXgP/QCz4uBgGUFKG+OVeMHTDzhWe1SUyclYERr67bZyWaPc+JYZcEJWoR3GmtMrly6joDqlhUIYvTS9m5Ov9eq7vFp7++WqFSxQl6wU2IrWGtWnG3ToMd4GA2Sp6JSC4E3rwxksoNnXK0bsAMtDVfhYGY61OQyug0wf5bqOzxXZr0HBkn/TjaOgaOmU4nZVpIen+Kd+2K/dPB1dgANt5X3hNXEgHnEeiEfqX/knbYa+zSmi3EpgvOICRM6kTJVHglFjs1AiEKxUwgfuhp2UNAcEcIOrgtEWZEfdf x-ms-exchange-transport-forked: True Content-Type: text/plain; charset="iso-2022-jp" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-OriginatorOrg: fujitsu.com X-MS-Exchange-CrossTenant-Network-Message-Id: 909506a0-64e3-457d-cb38-08d7883ff44d X-MS-Exchange-CrossTenant-originalarrivaltime: 24 Dec 2019 07:07:35.8900 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: a19f121d-81e1-4858-a9d8-736e267fd4c7 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: EI8VhGM6z2uevaPVlLLloJhtOuiu6Ld7gnddA5JxpHeED1/CWkhVAMM66gYbkll6ZpA+4ZTZCtqI+alow39kKWM4evwtW/GsShA97psIMNM= X-MS-Exchange-Transport-CrossTenantHeadersStamped: OSAPR01MB1505 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk From: Yugo Nagata > On Mon, 23 Dec 2019 08:08:53 +0000 > "tsunakawa.takay@fujitsu.com" wrote: > > How about unlogged tables ? I thought the point of using a temp table = is to > avoid WAL overhead. >=20 > 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 t= ype similarly create sequences behind the scenes. * Make the work tables depend on the materialized view by recording the dep= endency in pg_depend, so that Dropping the materialized view will also drop= its work tables. Regards Takayuki Tsunakawa