pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements
8+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2020-07-29 6023b7ea71 pg_stat_statements: track number of rows processed by some utility commands.
* pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements
@ 2020-03-15 17:35 legrand legrand <legrand_legrand@hotmail.com>
2020-03-25 22:39 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements legrand legrand <legrand_legrand@hotmail.com>
2020-03-26 12:41 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements Fujii Masao <masao.fujii@oss.nttdata.com>
0 siblings, 2 replies; 8+ messages in thread
From: legrand legrand @ 2020-03-15 17:35 UTC (permalink / raw)
To: pgsql-bugs
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements
2020-03-15 17:35 pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements legrand legrand <legrand_legrand@hotmail.com>
@ 2020-03-25 22:39 ` legrand legrand <legrand_legrand@hotmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: legrand legrand @ 2020-03-25 22:39 UTC (permalink / raw)
To: pgsql-bugs
Same remark for syntax
CREATE MATERIALIZED VIEW
as well.
Regards
PAscal
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements
2020-03-15 17:35 pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements legrand legrand <legrand_legrand@hotmail.com>
@ 2020-03-26 12:41 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2020-03-27 17:43 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements legrand legrand <legrand_legrand@hotmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Fujii Masao @ 2020-03-26 12:41 UTC (permalink / raw)
To: legrand legrand <legrand_legrand@hotmail.com>; pgsql-bugs
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements
2020-03-15 17:35 pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements legrand legrand <legrand_legrand@hotmail.com>
2020-03-26 12:41 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements Fujii Masao <masao.fujii@oss.nttdata.com>
@ 2020-03-27 17:43 ` legrand legrand <legrand_legrand@hotmail.com>
2020-04-20 15:42 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements Fujii Masao <masao.fujii@oss.nttdata.com>
0 siblings, 1 reply; 8+ messages in thread
From: legrand legrand @ 2020-03-27 17:43 UTC (permalink / raw)
To: pgsql-bugs
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements
2020-03-15 17:35 pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements legrand legrand <legrand_legrand@hotmail.com>
2020-03-26 12:41 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements Fujii Masao <masao.fujii@oss.nttdata.com>
2020-03-27 17:43 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements legrand legrand <legrand_legrand@hotmail.com>
@ 2020-04-20 15:42 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2020-04-21 11:37 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements legrand legrand <legrand_legrand@hotmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Fujii Masao @ 2020-04-20 15:42 UTC (permalink / raw)
To: legrand legrand <legrand_legrand@hotmail.com>; pgsql-bugs
On 2020/03/28 2:43, legrand legrand wrote:
> 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 ...
I found that SELECT INTO also returns CMDTAG_SELECT.
>
>> 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(
Thanks for confirming this!
Attached is the patch that makes pgss track the total number of rows
retrieved or affected by CREATE TABLE AS, SELECT INTO,
CREATE MATERIALIZED VIEW and FETCH. I think this is new feature
rather than bug fix, so am planning to add this patch into next CommitFest
for v14. Thought?
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
Attachments:
pgss_track_rows_of_utility_v1.patch (text/plain, 5.7K) download
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index f615f8c2bf..5e5ee5e899 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -527,6 +527,69 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0
(9 rows)
+--
+-- Track the total number of rows retrieved or affected by the utility
+-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW
+-- and SELECT INTO
+--
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a;
+SELECT generate_series(1, 10) c INTO pgss_select_into;
+COPY pgss_ctas (a, b) FROM STDIN;
+CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas;
+BEGIN;
+DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv;
+FETCH NEXT pgss_cursor;
+ a | b
+---+------
+ 1 | ctas
+(1 row)
+
+FETCH FORWARD 5 pgss_cursor;
+ a | b
+---+------
+ 2 | ctas
+ 3 | ctas
+ 4 | ctas
+ 5 | ctas
+ 6 | ctas
+(5 rows)
+
+FETCH FORWARD ALL pgss_cursor;
+ a | b
+----+------
+ 7 | ctas
+ 8 | ctas
+ 9 | ctas
+ 10 | ctas
+ 11 | copy
+ 12 | copy
+ 13 | copy
+(7 rows)
+
+COMMIT;
+SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | plans | calls | rows
+-------------------------------------------------------------------------------------+-------+-------+------
+ BEGIN | 0 | 1 | 0
+ COMMIT | 0 | 1 | 0
+ COPY pgss_ctas (a, b) FROM STDIN | 0 | 1 | 3
+ CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas | 0 | 1 | 13
+ CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a | 0 | 1 | 10
+ DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv | 0 | 1 | 0
+ FETCH FORWARD 5 pgss_cursor | 0 | 1 | 5
+ FETCH FORWARD ALL pgss_cursor | 0 | 1 | 7
+ FETCH NEXT pgss_cursor | 0 | 1 | 1
+ SELECT generate_series(1, 10) c INTO pgss_select_into | 0 | 1 | 10
+ SELECT pg_stat_statements_reset() | 0 | 1 | 1
+ SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 | 0 | 0
+(12 rows)
+
--
-- Track user activity and reset them
--
@@ -727,6 +790,9 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
--
DROP ROLE regress_stats_user1;
DROP ROLE regress_stats_user2;
+DROP MATERIALIZED VIEW pgss_matv;
+DROP TABLE pgss_ctas;
+DROP TABLE pgss_select_into;
--
-- [re]plan counting
--
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 90bc6fd013..9c1689fd96 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -1170,7 +1170,15 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
INSTR_TIME_SET_CURRENT(duration);
INSTR_TIME_SUBTRACT(duration, start);
- rows = (qc && qc->commandTag == CMDTAG_COPY) ? qc->nprocessed : 0;
+ /*
+ * Track the total number of rows retrieved or affected by
+ * the utility statements of COPY, FETCH, CREATE TABLE AS,
+ * CREATE MATERIALIZED VIEW and SELECT INTO.
+ */
+ rows = (qc && (qc->commandTag == CMDTAG_COPY ||
+ qc->commandTag == CMDTAG_FETCH ||
+ qc->commandTag == CMDTAG_SELECT)) ?
+ qc->nprocessed : 0;
/* calc differences of buffer counters. */
memset(&bufusage, 0, sizeof(BufferUsage));
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index 75c10554a8..3cbaf45c9d 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -249,6 +249,30 @@ DROP FUNCTION PLUS_TWO(INTEGER);
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+--
+-- Track the total number of rows retrieved or affected by the utility
+-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW
+-- and SELECT INTO
+--
+SELECT pg_stat_statements_reset();
+
+CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a;
+SELECT generate_series(1, 10) c INTO pgss_select_into;
+COPY pgss_ctas (a, b) FROM STDIN;
+11 copy
+12 copy
+13 copy
+\.
+CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas;
+BEGIN;
+DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv;
+FETCH NEXT pgss_cursor;
+FETCH FORWARD 5 pgss_cursor;
+FETCH FORWARD ALL pgss_cursor;
+COMMIT;
+
+SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
--
-- Track user activity and reset them
--
@@ -312,6 +336,9 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
--
DROP ROLE regress_stats_user1;
DROP ROLE regress_stats_user2;
+DROP MATERIALIZED VIEW pgss_matv;
+DROP TABLE pgss_ctas;
+DROP TABLE pgss_select_into;
--
-- [re]plan counting
* Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements
2020-03-15 17:35 pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements legrand legrand <legrand_legrand@hotmail.com>
2020-03-26 12:41 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements Fujii Masao <masao.fujii@oss.nttdata.com>
2020-03-27 17:43 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements legrand legrand <legrand_legrand@hotmail.com>
2020-04-20 15:42 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements Fujii Masao <masao.fujii@oss.nttdata.com>
@ 2020-04-21 11:37 ` legrand legrand <legrand_legrand@hotmail.com>
2020-04-22 03:17 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements Fujii Masao <masao.fujii@oss.nttdata.com>
0 siblings, 1 reply; 8+ messages in thread
From: legrand legrand @ 2020-04-21 11:37 UTC (permalink / raw)
To: pgsql-bugs
Fujii Masao-4 wrote
> Attached is the patch that makes pgss track the total number of rows
> retrieved or affected by CREATE TABLE AS, SELECT INTO,
> CREATE MATERIALIZED VIEW and FETCH. I think this is new feature
> rather than bug fix, so am planning to add this patch into next CommitFest
> for v14. Thought?
Thanks !
maybe v13, if this was considered as a bug that don't need backport ?
Regards
PAscal
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements
2020-03-15 17:35 pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements legrand legrand <legrand_legrand@hotmail.com>
2020-03-26 12:41 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements Fujii Masao <masao.fujii@oss.nttdata.com>
2020-03-27 17:43 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements legrand legrand <legrand_legrand@hotmail.com>
2020-04-20 15:42 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements Fujii Masao <masao.fujii@oss.nttdata.com>
2020-04-21 11:37 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements legrand legrand <legrand_legrand@hotmail.com>
@ 2020-04-22 03:17 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2020-05-06 13:49 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements Asif Rehman <asifr.rehman@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Fujii Masao @ 2020-04-22 03:17 UTC (permalink / raw)
To: legrand legrand <legrand_legrand@hotmail.com>; pgsql-bugs
On 2020/04/21 20:37, legrand legrand wrote:
> Fujii Masao-4 wrote
>> Attached is the patch that makes pgss track the total number of rows
>> retrieved or affected by CREATE TABLE AS, SELECT INTO,
>> CREATE MATERIALIZED VIEW and FETCH. I think this is new feature
>> rather than bug fix, so am planning to add this patch into next CommitFest
>> for v14. Thought?
>
> Thanks !
> maybe v13, if this was considered as a bug that don't need backport ?
Yeah, if many people think this is a bug, we can get it into v13.
But at least for me it looks like an improvement of the capability
of pgss rather than a bug fix. If the document of pgss clearly
explained "pgss tracks the total number of rows affect by even
utility command ...", I think that we can treat this as a bug. But...
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements
2020-03-15 17:35 pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements legrand legrand <legrand_legrand@hotmail.com>
2020-03-26 12:41 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements Fujii Masao <masao.fujii@oss.nttdata.com>
2020-03-27 17:43 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements legrand legrand <legrand_legrand@hotmail.com>
2020-04-20 15:42 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements Fujii Masao <masao.fujii@oss.nttdata.com>
2020-04-21 11:37 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements legrand legrand <legrand_legrand@hotmail.com>
2020-04-22 03:17 ` Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements Fujii Masao <masao.fujii@oss.nttdata.com>
@ 2020-05-06 13:49 ` Asif Rehman <asifr.rehman@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Asif Rehman @ 2020-05-06 13:49 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org; +Cc: Fujii Masao <masao.fujii@gmail.com>
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: not tested
Documentation: not tested
The patch applies cleanly and works as expected.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2020-05-06 13:49 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-15 17:35 pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements legrand legrand <legrand_legrand@hotmail.com>
2020-03-25 22:39 ` legrand legrand <legrand_legrand@hotmail.com>
2020-03-26 12:41 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2020-03-27 17:43 ` legrand legrand <legrand_legrand@hotmail.com>
2020-04-20 15:42 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2020-04-21 11:37 ` legrand legrand <legrand_legrand@hotmail.com>
2020-04-22 03:17 ` Fujii Masao <masao.fujii@oss.nttdata.com>
2020-05-06 13:49 ` Asif Rehman <asifr.rehman@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox