pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements

legrand legrand
Hello,
it seems that column "rows" is not updated after CREATE TABLE AS SELECT
statements.

pg13devel (snapshot 2020-03-14)
postgres=# select name,setting from pg_settings where name like 'pg_stat%';
               name               | setting
----------------------------------+---------
 pg_stat_statements.max           | 5000
 pg_stat_statements.save          | on
 pg_stat_statements.track         | all
 pg_stat_statements.track_utility | on
(4 rows)

postgres=# select pg_stat_statements_reset();
 pg_stat_statements_reset
--------------------------

(1 row)


postgres=# create table ctas as select * from pg_class;
SELECT 386
postgres=# select query,calls,rows from pg_stat_statements where query like
'create table ctas%';
                    query                    | calls | rows
---------------------------------------------+-------+------
 create table ctas as select * from pg_class |     1 |    0
(1 row)

after modifying the following line in pg_stat_statements.c

rows = (qc && qc->commandTag == CMDTAG_COPY) ? qc->nprocessed : 0;
into
rows = (qc && (qc->commandTag == CMDTAG_COPY
                      || qc->commandTag == CMDTAG_SELECT)
           ) ? qc->nprocessed : 0;

column rows seems properly updated.

What do you think about that fix ?
Thanks in advance
Regards
PAscal




--
Sent from: https://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html


Reply | Threaded
Open this post in threaded view
|

Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements

legrand legrand
Same remark for syntax

CREATE MATERIALIZED VIEW

as well.

Regards
PAscal



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html


Reply | Threaded
Open this post in threaded view
|

Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements

Fujii Masao-4
In reply to this post by legrand legrand


On 2020/03/16 2:35, legrand legrand wrote:

> Hello,
> it seems that column "rows" is not updated after CREATE TABLE AS SELECT
> statements.
>
> pg13devel (snapshot 2020-03-14)
> postgres=# select name,setting from pg_settings where name like 'pg_stat%';
>                 name               | setting
> ----------------------------------+---------
>   pg_stat_statements.max           | 5000
>   pg_stat_statements.save          | on
>   pg_stat_statements.track         | all
>   pg_stat_statements.track_utility | on
> (4 rows)
>
> postgres=# select pg_stat_statements_reset();
>   pg_stat_statements_reset
> --------------------------
>
> (1 row)
>
>
> postgres=# create table ctas as select * from pg_class;
> SELECT 386
> postgres=# select query,calls,rows from pg_stat_statements where query like
> 'create table ctas%';
>                      query                    | calls | rows
> ---------------------------------------------+-------+------
>   create table ctas as select * from pg_class |     1 |    0
> (1 row)

Thanks for the report! Yeah, it seems worth improving this.

> after modifying the following line in pg_stat_statements.c
>
> rows = (qc && qc->commandTag == CMDTAG_COPY) ? qc->nprocessed : 0;
> into
> rows = (qc && (qc->commandTag == CMDTAG_COPY
>                        || qc->commandTag == CMDTAG_SELECT)
>             ) ? qc->nprocessed : 0;
>
> column rows seems properly updated.
>
> What do you think about that fix ?

The utility commands that return CMDTAG_SELECT are
only CREATE TABLE AS SELECT and CREATE MATERIALIZED VIEW?
I'd just like to confirm that there is no case where "rows" must not
be counted when CMDTAG_SELECT is returned.

BTW, "rows" should be updated when FETCH or MOVE is executed
because each command returns or affects the rows?

Regards,

--
Fujii Masao
NTT DATA CORPORATION
Advanced Platform Technology Group
Research and Development Headquarters


Reply | Threaded
Open this post in threaded view
|

Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements

legrand legrand
Thank you for those answers !

> The utility commands that return CMDTAG_SELECT are
> only CREATE TABLE AS SELECT and CREATE MATERIALIZED VIEW?
> I'd just like to confirm that there is no case where "rows" must not
> be counted when CMDTAG_SELECT is returned.

I don't have any in mind ...

> BTW, "rows" should be updated when FETCH or MOVE is executed
> because each command returns or affects the rows?

Yes they should, but they aren't yet (event with CMDTAG_SELECT added)

Note that implicit cursors behave the same way ;o(

postgres=# do $$ declare i integer; begin for i in (select 1 ) loop null;
end loop;end; $$;
DO
postgres=# select calls,query,rows from pg_stat_statements;
 calls |                                      query                                    
| rows
-------+---------------------------------------------------------------------------------+------
     1 | select pg_stat_statements_reset()                                              
|    1
     1 | (select $1 )                                                                  
|    0
     1 | do $$ declare i integer; begin for i in (select 1 ) loop null; end
loop;end; $$ |    0
(3 rows)

Regards
PAscal



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html