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

[PGPRO-8358] Added the progress value to the pg_query_state message#58

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to ourterms of service andprivacy statement. We’ll occasionally send you account related emails.

Already on GitHub?Sign in to your account

Open
arseny114 wants to merge4 commits intopostgrespro:master
base:master
Choose a base branch
Loading
fromarseny114:PGPRO-8358
Open
Show file tree
Hide file tree
Changes fromall commits
Commits
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
5 changes: 3 additions & 2 deletionsMakefile
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -4,8 +4,9 @@
MODULE_big = pg_query_state
OBJS = pg_query_state.o signal_handler.o $(WIN32RES)
EXTENSION = pg_query_state
EXTVERSION = 1.1
DATA = pg_query_state--1.0--1.1.sql
EXTVERSION = 1.2
DATA = pg_query_state--1.0--1.1.sql \
pg_query_state--1.1--1.2.sql
DATA_built = $(EXTENSION)--$(EXTVERSION).sql
PGFILEDESC = "pg_query_state - facility to track progress of plan execution"

Expand Down
114 changes: 114 additions & 0 deletionsREADME.md
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -321,3 +321,117 @@ Do not hesitate to post your issues, questions and new ideas at the [issues](htt
## Authors
[Maksim Milyutin](https://github.com/maksm90)
Alexey Kondratov <a.kondratov@postgrespro.ru> Postgres Professional Ltd., Russia

## Function progress\_bar
```plpgsql
pg_progress_bar(
integer pid
) returns FLOAT
```
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.

## Function progress\_bar\_visual
```plpgsql
pg_progress_bar_visual(
integer pid,
integer delay
) returns VOID
```
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.

**_Warning_**: Calling role have to be superuser or member of the role whose backend is being called. Otherwise function prints ERROR message `permission denied`.

## Examples
Assume first backend executes some function:
```sql
postgres=# insert into table_name select generate_series(1,10000000);
```
Other backend can get the follow output:
```sql
postgres=# SELECT pid FROM pg_stat_activity where query like 'insert%';
pid
-------
23877
(1 row)

postgres=# SELECT pg_progress_bar(23877);
pg_progress_bar
-----------------
0.6087927
(1 row)
```
Or continuous version:
```sql
postgres=# SELECT pg_progress_bar_visual(23877, 1);
Progress = 0.043510
Progress = 0.085242
Progress = 0.124921
Progress = 0.168168
Progress = 0.213803
Progress = 0.250362
Progress = 0.292632
Progress = 0.331454
Progress = 0.367509
Progress = 0.407450
Progress = 0.448646
Progress = 0.488171
Progress = 0.530559
Progress = 0.565558
Progress = 0.608039
Progress = 0.645778
Progress = 0.654842
Progress = 0.699006
Progress = 0.735760
Progress = 0.787641
Progress = 0.832160
Progress = 0.871077
Progress = 0.911858
Progress = 0.956362
Progress = 0.995097
Progress = 1.000000
pg_progress_bar_visual
------------------------
1
(1 row)
```
Also uncountable queries exist. Assume first backend executes some function:
```sql
DELETE from table_name;
```
Other backend can get the follow output:
```sql
postgres=# SELECT pid FROM pg_stat_activity where query like 'delete%';
pid
-------
23877
(1 row)

postgres=# SELECT pg_progress_bar(23877);
INFO: Counting Progress doesn't available
pg_progress_bar
-----------------
-1
(1 row)

postgres=# SELECT pg_progress_bar_visual(23877, 5);
INFO: Counting Progress doesn't available
pg_progress_bar_visual
------------------------
-1
(1 row)
```

## Reinstallation
If you already have a module 'pg_query_state' without progress bar functions installed, execute this in the module's directory:
```
make install USE_PGXS=1
```
It is essential to restart the PostgreSQL instance. After that, execute the following queries in psql:
```sql
DROP EXTENSION IF EXISTS pg_query_state;
CREATE EXTENSION pg_query_state;
```

## Authors
Ekaterina Sokolova <e.sokolova@postgrespro.ru> Postgres Professional Ltd., Russia
Vyacheslav Makarov <v.makarov@postgrespro.ru> Postgres Professional Ltd., Russia
11 changes: 11 additions & 0 deletionsinit.sql
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -15,3 +15,14 @@ CREATE FUNCTION pg_query_state(pid integer
, leader_pid integer)
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT VOLATILE;

CREATE FUNCTION pg_progress_bar(pid integer)
RETURNS FLOAT
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT VOLATILE;

CREATE FUNCTION pg_progress_bar_visual(pidinteger
, delayinteger = 1)
RETURNS FLOAT
AS 'MODULE_PATHNAME', 'pg_progress_bar'
LANGUAGE C STRICT VOLATILE;
14 changes: 14 additions & 0 deletionspg_query_state--1.1--1.2.sql
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "ALTER EXTENSION pg_query_state UPDATE TO '1.2'" to load this file. \quit

CREATE FUNCTION pg_progress_bar(pid integer)
RETURNS FLOAT
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT VOLATILE;

CREATE FUNCTION pg_progress_bar_visual(pidinteger
, delayinteger = 1)
RETURNS FLOAT
AS 'MODULE_PATHNAME', 'pg_progress_bar'
LANGUAGE C STRICT VOLATILE;

Loading
[8]ページ先頭
©2009-2025 Movatter.jp