Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5578a38

Browse files
author
Ekaterina Sokolova
committed
Add progress bar during query execution to track progress.
Progress bar is run-time SQL-query progress indicator.Function pg_progress_bar(pid) extracts the current query state from backend with specified 'pid'. Then gets thenumerical values of the actual rows and total rows and count progress for the whole query tree. Function returnsnumeric value from 0 to 1 describing the measure of query fulfillment. This function can be used to be embeddedin the PostgreSQL GUI.To intuitively track progress without using a graphical client, you can use the additionally implemented functionpg_progress_bar_visual(pid, delay). It prints state every period specified by 'delay' (in seconds).Increase version to 1.2 due to init.sql change.
1 parentee0d4a8 commit5578a38

File tree

10 files changed

+584
-8
lines changed

10 files changed

+584
-8
lines changed

‎Makefile‎

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -4,18 +4,20 @@
44
MODULE_big = pg_query_state
55
OBJS = pg_query_state.o signal_handler.o$(WIN32RES)
66
EXTENSION = pg_query_state
7-
EXTVERSION = 1.1
8-
DATA = pg_query_state--1.0--1.1.sql
7+
EXTVERSION = 1.2
8+
DATA = pg_query_state--1.0--1.1.sql\
9+
pg_query_state--1.1--1.2.sql
910
DATA_built =$(EXTENSION)--$(EXTVERSION).sql
1011
PGFILEDESC = "pg_query_state - facility to track progress of plan execution"
1112

1213
EXTRA_CLEAN = ./isolation_output$(EXTENSION)--$(EXTVERSION).sql\
1314
Dockerfile ./tests/*.pyc ./tmp_stress
1415

1516
ISOLATION = corner_cases
16-
1717
ISOLATION_OPTS = --load-extension=pg_query_state
1818

19+
TAP_TESTS = 1
20+
1921
ifdefUSE_PGXS
2022
PG_CONFIG ?= pg_config
2123
PGXS :=$(shell$(PG_CONFIG) --pgxs)

‎README.md‎

Lines changed: 114 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -321,3 +321,117 @@ Do not hesitate to post your issues, questions and new ideas at the [issues](htt
321321
##Authors
322322
[Maksim Milyutin](https://github.com/maksm90)
323323
Alexey Kondratov <a.kondratov@postgrespro.ru> Postgres Professional Ltd., Russia
324+
325+
##Function progress\_bar
326+
```plpgsql
327+
pg_progress_bar(
328+
integer pid
329+
) returns FLOAT
330+
```
331+
extracts the current query state from backend with specified 'pid'. Then gets the numerical values of the actual rows and total rows and count progress for the whole query tree. Function returns numeric value from 0 to 1 describing the measure of query fulfillment. If there is no information about current state of the query, or the impossibility of counting, the corresponding messages will be displayed.
332+
333+
##Function progress\_bar\_visual
334+
```plpgsql
335+
pg_progress_bar_visual(
336+
integer pid,
337+
integer delay
338+
) returns VOID
339+
```
340+
cyclically extracts and print the current query state in numeric value from backend with specified 'pid' every period specified by 'delay' in seconds. This is the looping version of the progress\_bar function that returns void value.
341+
342+
**_Warning_**: Calling role have to be superuser or member of the role whose backend is being called. Otherwise function prints ERROR message`permission denied`.
343+
344+
##Examples
345+
Assume first backend executes some function:
346+
```sql
347+
postgres=# insert into table_name select generate_series(1,10000000);
348+
```
349+
Other backend can get the follow output:
350+
```sql
351+
postgres=# SELECT pid FROM pg_stat_activity where query like 'insert%';
352+
pid
353+
-------
354+
23877
355+
(1 row)
356+
357+
postgres=# SELECT pg_progress_bar(23877);
358+
pg_progress_bar
359+
-----------------
360+
0.6087927
361+
(1 row)
362+
```
363+
Or continuous version:
364+
```sql
365+
postgres=# SELECT pg_progress_bar_visual(23877, 1);
366+
Progress=0.043510
367+
Progress=0.085242
368+
Progress=0.124921
369+
Progress=0.168168
370+
Progress=0.213803
371+
Progress=0.250362
372+
Progress=0.292632
373+
Progress=0.331454
374+
Progress=0.367509
375+
Progress=0.407450
376+
Progress=0.448646
377+
Progress=0.488171
378+
Progress=0.530559
379+
Progress=0.565558
380+
Progress=0.608039
381+
Progress=0.645778
382+
Progress=0.654842
383+
Progress=0.699006
384+
Progress=0.735760
385+
Progress=0.787641
386+
Progress=0.832160
387+
Progress=0.871077
388+
Progress=0.911858
389+
Progress=0.956362
390+
Progress=0.995097
391+
Progress=1.000000
392+
pg_progress_bar_visual
393+
------------------------
394+
1
395+
(1 row)
396+
```
397+
Also uncountable queries exist. Assume first backend executes some function:
398+
```sql
399+
DELETEfrom table_name;
400+
```
401+
Other backend can get the follow output:
402+
```sql
403+
postgres=# SELECT pid FROM pg_stat_activity where query like 'delete%';
404+
pid
405+
-------
406+
23877
407+
(1 row)
408+
409+
postgres=# SELECT pg_progress_bar(23877);
410+
INFO: Counting Progress doesn't available
411+
pg_progress_bar
412+
-----------------
413+
-1
414+
(1 row)
415+
416+
postgres=# SELECT pg_progress_bar_visual(23877, 5);
417+
INFO: Counting Progress doesn't available
418+
pg_progress_bar_visual
419+
------------------------
420+
-1
421+
(1 row)
422+
```
423+
424+
##Reinstallation
425+
If you already have a module 'pg_query_state' without progress bar functions installed, execute this in the module's directory:
426+
```
427+
make install USE_PGXS=1
428+
```
429+
It is essential to restart the PostgreSQL instance. After that, execute the following queries in psql:
430+
```sql
431+
DROP EXTENSION IF EXISTS pg_query_state;
432+
CREATE EXTENSION pg_query_state;
433+
```
434+
435+
##Authors
436+
Ekaterina Sokolova <e.sokolova@postgrespro.ru> Postgres Professional Ltd., Russia
437+
Vyacheslav Makarov <v.makarov@postgrespro.ru> Postgres Professional Ltd., Russia

‎init.sql‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15,3 +15,14 @@ CREATE FUNCTION pg_query_state(pid integer
1515
, leader_pidinteger)
1616
AS'MODULE_PATHNAME'
1717
LANGUAGE C STRICT VOLATILE;
18+
19+
CREATEFUNCTIONpg_progress_bar(pidinteger)
20+
RETURNS FLOAT
21+
AS'MODULE_PATHNAME'
22+
LANGUAGE C STRICT VOLATILE;
23+
24+
CREATEFUNCTIONpg_progress_bar_visual(pidinteger
25+
, delayinteger=1)
26+
RETURNS FLOAT
27+
AS'MODULE_PATHNAME','pg_progress_bar'
28+
LANGUAGE C STRICT VOLATILE;

‎pg_query_state--1.1--1.2.sql‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
1+
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
2+
\echo Use"ALTER EXTENSION pg_query_state UPDATE TO '1.2'" to load this file. \quit
3+
4+
CREATEFUNCTIONpg_progress_bar(pidinteger)
5+
RETURNS FLOAT
6+
AS'MODULE_PATHNAME'
7+
LANGUAGE C STRICT VOLATILE;
8+
9+
CREATEFUNCTIONpg_progress_bar_visual(pidinteger
10+
, delayinteger=1)
11+
RETURNS FLOAT
12+
AS'MODULE_PATHNAME','pg_progress_bar'
13+
LANGUAGE C STRICT VOLATILE;
14+

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp