Thread: REFRESH MATERIALIZED VIEW and completion tag output
Hi, The patch that makes pg_stat_statements track the number of rows that REFRESH MATERIALIZED VIEW command processes was proposed at [1]. When reviewing the patch, I just wondered why the completion tag output for REFRESH MATERIALIZED VIEW doesn't include the row count. Is this an intentional behavior? I've not found the past discussion about this yet. OTOH, CREATE MATERIALIZED VIEW reports something like "SELECT NNN" (NNN is the row count) as the completion tag output. If WITH NO DATA is specified, "CREATE MATERIALIZED VIEW" is reported instead. Is it better to make REFRESH MATERIALIZED VIEW do the similar thing? Or we should not do that, for example, since changing the completion tag output may break the client app? [1] https://postgr.es/m/71f6bc72f8bbaa06e701f8bd2562c347@oss.nttdata.com Regards, -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION
> On Nov 5, 2020, at 8:20 AM, Fujii Masao <masao.fujii@oss.nttdata.com> wrote: > > The patch that makes pg_stat_statements track the number of rows that > REFRESH MATERIALIZED VIEW command processes was proposed at [1]. > When reviewing the patch, I just wondered why the completion tag output > for REFRESH MATERIALIZED VIEW doesn't include the row count. Is this > an intentional behavior? I've not found the past discussion about this yet. Of 191 command tags, only 7 return the number of rows. See src/include/tcop/cmdtaglist.h. REFRESH MATERIALIZED VIEW issimilar to CREATE MATERIALIZED VIEW and also CREATE TABLE AS, which also do not return the row count. I think this behavioris historical, and preserved for compatibility. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 2020/11/06 1:56, Mark Dilger wrote: > > >> On Nov 5, 2020, at 8:20 AM, Fujii Masao <masao.fujii@oss.nttdata.com> wrote: >> >> The patch that makes pg_stat_statements track the number of rows that >> REFRESH MATERIALIZED VIEW command processes was proposed at [1]. >> When reviewing the patch, I just wondered why the completion tag output >> for REFRESH MATERIALIZED VIEW doesn't include the row count. Is this >> an intentional behavior? I've not found the past discussion about this yet. > > Of 191 command tags, only 7 return the number of rows. See src/include/tcop/cmdtaglist.h. REFRESH MATERIALIZED VIEW issimilar to CREATE MATERIALIZED VIEW and also CREATE TABLE AS, which also do not return the row count. Yes, so I was wondering if REFRESH MATERIALIZED VIEW also should use CMDTAG_SELECT like CREATE MATERIALIZED VIEW does. > I think this behavior is historical, and preserved for compatibility. Maybe true. The patch that I mentioned upthread tries to change REFRESH MATERIALIZED VIEW so that it register the rowcount into its query completion so that pg_stat_statements can track the number of processed rows. But display_rowcount for CMDTAG_REFRESH_MATERIALIZED_VIEW still should be false in cmdtaglist.h to avoid the change of the completion tag output. Regards, -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION
> On Nov 5, 2020, at 4:45 PM, Fujii Masao <masao.fujii@oss.nttdata.com> wrote: > > > > On 2020/11/06 1:56, Mark Dilger wrote: >>> On Nov 5, 2020, at 8:20 AM, Fujii Masao <masao.fujii@oss.nttdata.com> wrote: >>> >>> The patch that makes pg_stat_statements track the number of rows that >>> REFRESH MATERIALIZED VIEW command processes was proposed at [1]. >>> When reviewing the patch, I just wondered why the completion tag output >>> for REFRESH MATERIALIZED VIEW doesn't include the row count. Is this >>> an intentional behavior? I've not found the past discussion about this yet. >> Of 191 command tags, only 7 return the number of rows. See src/include/tcop/cmdtaglist.h. REFRESH MATERIALIZED VIEWis similar to CREATE MATERIALIZED VIEW and also CREATE TABLE AS, which also do not return the row count. > > Yes, so I was wondering if REFRESH MATERIALIZED VIEW also should use > CMDTAG_SELECT like CREATE MATERIALIZED VIEW does. For both CREATE MATERIALIZED VIEW and CREATE TABLE AS, ExecCreateTableAs() does: SetQueryCompletion(qc, CMDTAG_SELECT, queryDesc->estate->es_processed); but ExecRefreshMatView() does nothing with it's QueryCompletion parameter. I don't know why this case should behave differentlythan the other two. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company