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

Commitad88ee9

Browse files
committed
Implementation of function progress_bar.
Additional tools for tracking the progress of the running query:* function progress_bar(pid), which returns a numeric from 0 to 1,characterizing the number of processed rows in relation to their planned number.* function progress_bar_visual(pid, delay), which displaysthis numeric with a delay of several seconds.Developers:Ekaterina SokolovaVyacheslav Makarov
1 parentaa9fbed commitad88ee9

File tree

3 files changed

+372
-0
lines changed

3 files changed

+372
-0
lines changed

‎README.md

Lines changed: 114 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -308,3 +308,117 @@ Do not hesitate to post your issues, questions and new ideas at the [issues](htt
308308
##Authors
309309
[Maksim Milyutin](https://github.com/maksm90)
310310
Alexey Kondratov <a.kondratov@postgrespro.ru> Postgres Professional Ltd., Russia
311+
312+
##Function progress\_bar
313+
```plpgsql
314+
progress_bar(
315+
integer pid
316+
) returns FLOAT
317+
```
318+
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.
319+
320+
##Function progress\_bar\_visual
321+
```plpgsql
322+
progress_bar(
323+
integer pid,
324+
integer delay
325+
) returns VOID
326+
```
327+
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.
328+
329+
**_Warning_**: Calling role have to be superuser or member of the role whose backend is being called. Otherwise function prints ERROR message`permission denied`.
330+
331+
##Examples
332+
Assume first backend executes some function:
333+
```sql
334+
postgres=# insert into table_name select generate_series(1,10000000);
335+
```
336+
Other backend can get the follow output:
337+
```sql
338+
postgres=# SELECT pid FROM pg_stat_activity where query like 'insert%';
339+
pid
340+
-------
341+
23877
342+
(1 row)
343+
344+
postgres=# SELECT progress_bar(23877);
345+
progress_bar
346+
--------------
347+
0.6087927
348+
(1 row)
349+
```
350+
Or continuous version:
351+
```sql
352+
postgres=# SELECT progress_bar_visual(23877, 1);
353+
Progress=0.043510
354+
Progress=0.085242
355+
Progress=0.124921
356+
Progress=0.168168
357+
Progress=0.213803
358+
Progress=0.250362
359+
Progress=0.292632
360+
Progress=0.331454
361+
Progress=0.367509
362+
Progress=0.407450
363+
Progress=0.448646
364+
Progress=0.488171
365+
Progress=0.530559
366+
Progress=0.565558
367+
Progress=0.608039
368+
Progress=0.645778
369+
Progress=0.654842
370+
Progress=0.699006
371+
Progress=0.735760
372+
Progress=0.787641
373+
Progress=0.832160
374+
Progress=0.871077
375+
Progress=0.911858
376+
Progress=0.956362
377+
Progress=0.995097
378+
Progress=1.000000
379+
progress_bar_visual
380+
---------------------
381+
1
382+
(1 row)
383+
```
384+
Also uncountable queries exist. Assume first backend executes some function:
385+
```sql
386+
DELETEfrom table_name;
387+
```
388+
Other backend can get the follow output:
389+
```sql
390+
postgres=# SELECT pid FROM pg_stat_activity where query like 'delete%';
391+
pid
392+
-------
393+
23877
394+
(1 row)
395+
396+
postgres=# SELECT progress_bar(23877);
397+
INFO: Counting Progress doesn't available
398+
progress_bar
399+
--------------
400+
-1
401+
(1 row)
402+
403+
postgres=# SELECT progress_bar_visual(23877, 5);
404+
INFO: Counting Progress doesn't available
405+
progress_bar_visual
406+
---------------------
407+
-1
408+
(1 row)
409+
```
410+
411+
##Reinstallation
412+
If you already have a module 'pg_query_state' without progress bar functions installed, execute this in the module's directory:
413+
```
414+
make install USE_PGXS=1
415+
```
416+
It is essential to restart the PostgreSQL instance. After that, execute the following queries in psql:
417+
```sql
418+
DROP EXTENSION IF EXISTS pg_query_state;
419+
CREATE EXTENSION pg_query_state;
420+
```
421+
422+
##Authors
423+
Ekaterina Sokolova <e.sokolova@postgrespro.ru> Postgres Professional Ltd., Russia
424+
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+
CREATEFUNCTIONprogress_bar(pidinteger)
20+
RETURNS FLOAT
21+
AS'MODULE_PATHNAME'
22+
LANGUAGE C STRICT VOLATILE;
23+
24+
CREATEFUNCTIONprogress_bar_visual(pidinteger
25+
, delayinteger=1)
26+
RETURNS FLOAT
27+
AS'MODULE_PATHNAME','progress_bar'
28+
LANGUAGE C STRICT VOLATILE;

‎pg_query_state.c

Lines changed: 247 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1182,3 +1182,250 @@ DetachPeer(void)
11821182
ereport(LOG, (errcode(ERRCODE_INTERNAL_ERROR),
11831183
errmsg("pg_query_state peer is not responding")));
11841184
}
1185+
1186+
/*
1187+
* Count progress of query execution like ratio of
1188+
* number of received to planned rows in persent.
1189+
* Changes of this function can lead to more plausible results.
1190+
*/
1191+
staticdouble
1192+
CountProgress(char*plan_text)
1193+
{
1194+
char*plan;/* Copy of plan_text */
1195+
char*node;/* Part of plan with information about single node */
1196+
char*rows;/* Pointer to rows */
1197+
char*actual_rows_str;/* Actual rows in string format */
1198+
char*plan_rows_str;/* Planned rows in string format */
1199+
intlen;/* Length of rows in string format */
1200+
doubleactual_rows;/* Actual rows */
1201+
doubleplan_rows;/* Planned rows */
1202+
doubleprogress=0;/* Summary progress on nodes */
1203+
intnode_amount=0;/* Amount of plantree nodes using in counting progress */
1204+
1205+
plan=palloc(sizeof(char)* (strlen(plan_text)+1));
1206+
strcpy(plan,plan_text);
1207+
node=strtok(plan,"[");/* Get information about upper node */
1208+
while (node!=NULL)
1209+
{
1210+
if (strstr(node,"Seq Scan")==NULL)
1211+
{
1212+
if (strstr(node,"ModifyTable")==NULL)
1213+
{
1214+
if (strstr(node,"Result")==NULL)
1215+
{
1216+
if ((rows=strstr(node,"Rows Removed by Filter"))!=NULL)
1217+
{
1218+
/*
1219+
* Filter node have 2 conditions:
1220+
* 1) Was not filtered (current progress = 0)
1221+
* 2) Was filtered (current progress = 1)
1222+
*/
1223+
node_amount++;
1224+
plan_rows=1;
1225+
rows= (char*) (rows+strlen("Rows Removed by Filter\": ")*sizeof(char));
1226+
if (rows[0]=='0')
1227+
actual_rows=0;
1228+
else
1229+
actual_rows=1;
1230+
}elseif ((rows=strstr(node,"\"Actual Rows\": "))!=NULL)
1231+
{
1232+
node_amount++;
1233+
actual_rows=0;
1234+
plan_rows=0;
1235+
1236+
rows= (char*) (rows+strlen("\"Actual Rows\": ")*sizeof(char));
1237+
len=strstr(rows,"\n")-rows;
1238+
if ((strstr(rows,",")-rows)<len)
1239+
len=strstr(rows,",")-rows;
1240+
actual_rows_str=palloc(sizeof(char)* (len+1));
1241+
actual_rows_str[len]=0;
1242+
strncpy(actual_rows_str,rows,len);
1243+
actual_rows=strtod(actual_rows_str,NULL);
1244+
pfree(actual_rows_str);
1245+
1246+
rows=strstr(node,"\"Plan Rows\": ");
1247+
rows= (char*) (rows+strlen("\"Plan Rows\": ")*sizeof(char));
1248+
len=strstr(rows,",")-rows;
1249+
plan_rows_str=palloc(sizeof(char)* (len+1));
1250+
plan_rows_str[len]=0;
1251+
strncpy(plan_rows_str,rows,len);
1252+
plan_rows=strtod(plan_rows_str,NULL);
1253+
pfree(plan_rows_str);
1254+
1255+
if (plan_rows>actual_rows)
1256+
progress+=actual_rows /plan_rows;
1257+
else
1258+
progress+=1;
1259+
}
1260+
}
1261+
}
1262+
}
1263+
node=strtok(NULL,"[");
1264+
}
1265+
1266+
pfree(plan);
1267+
if (node_amount>0)
1268+
{
1269+
progress=progress /node_amount;
1270+
if (progress==1)
1271+
progress=0.999999;
1272+
}
1273+
else
1274+
return-1;
1275+
returnprogress;
1276+
}
1277+
1278+
staticdouble
1279+
GetCurrentNumericState(shm_mq_msg*msg)
1280+
{
1281+
typedefstruct
1282+
{
1283+
PGPROC*proc;
1284+
ListCell*frame_cursor;
1285+
intframe_index;
1286+
List*stack;
1287+
}proc_state;
1288+
1289+
/* multicall context type */
1290+
typedefstruct
1291+
{
1292+
ListCell*proc_cursor;
1293+
List*procs;
1294+
}pg_qs_fctx;
1295+
1296+
pg_qs_fctx*fctx;
1297+
List*qs_stack;
1298+
proc_state*p_state;
1299+
stack_frame*frame;
1300+
char*plan_text;
1301+
1302+
fctx= (pg_qs_fctx*)palloc(sizeof(pg_qs_fctx));
1303+
fctx->procs=NIL;
1304+
p_state= (proc_state*)palloc(sizeof(proc_state));
1305+
qs_stack=deserialize_stack(msg->stack,msg->stack_depth);
1306+
p_state->proc=msg->proc;
1307+
p_state->stack=qs_stack;
1308+
p_state->frame_index=0;
1309+
p_state->frame_cursor=list_head(qs_stack);
1310+
fctx->procs=lappend(fctx->procs,p_state);
1311+
fctx->proc_cursor=list_head(fctx->procs);
1312+
frame= (stack_frame*)lfirst(p_state->frame_cursor);
1313+
plan_text=frame->plan->vl_dat;
1314+
returnCountProgress(plan_text);
1315+
}
1316+
1317+
PG_FUNCTION_INFO_V1(progress_bar);
1318+
Datum
1319+
progress_bar(PG_FUNCTION_ARGS)
1320+
{
1321+
pid_tpid=PG_GETARG_INT32(0);
1322+
intdelay=0;
1323+
PGPROC*proc;
1324+
Oidcounterpart_user_id;
1325+
shm_mq_msg*msg;
1326+
List*bg_worker_procs=NIL;
1327+
List*msgs;
1328+
doubleprogress;
1329+
doubleold_progress;
1330+
1331+
if (PG_NARGS()==2)
1332+
{
1333+
/*
1334+
* This is continuous mode, function 'progress_bar_visual',
1335+
* we need to get delay value.
1336+
*/
1337+
delay=PG_GETARG_INT32(1);
1338+
if (delay<1)
1339+
ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1340+
errmsg("the value of \"delay\" must be positive integer")));
1341+
}
1342+
1343+
if (!module_initialized)
1344+
ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1345+
errmsg("pg_query_state wasn't initialized yet")));
1346+
1347+
if (pid==MyProcPid)
1348+
ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1349+
errmsg("attempt to extract state of current process")));
1350+
1351+
proc=BackendPidGetProc(pid);
1352+
if (!proc||proc->backendId==InvalidBackendId||proc->databaseId==InvalidOid||proc->roleId==InvalidOid)
1353+
ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1354+
errmsg("backend with pid=%d not found",pid)));
1355+
1356+
counterpart_user_id=GetRemoteBackendUserId(proc);
1357+
if (!(superuser()||GetUserId()==counterpart_user_id))
1358+
ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1359+
errmsg("permission denied")));
1360+
1361+
old_progress=0;
1362+
progress=0;
1363+
bg_worker_procs=GetRemoteBackendWorkers(proc);
1364+
msgs=GetRemoteBackendQueryStates(proc,
1365+
bg_worker_procs,
1366+
0,1,0,0,0,
1367+
EXPLAIN_FORMAT_JSON);
1368+
if (list_length(msgs)==0)
1369+
elog(WARNING,"backend does not reply");
1370+
msg= (shm_mq_msg*)linitial(msgs);
1371+
1372+
switch (msg->result_code)
1373+
{
1374+
caseQUERY_NOT_RUNNING:
1375+
elog(INFO,"query not runing");
1376+
PG_RETURN_FLOAT8((float8)-1);
1377+
break;
1378+
caseSTAT_DISABLED:
1379+
elog(INFO,"query execution statistics disabled");
1380+
PG_RETURN_FLOAT8((float8)-1);
1381+
default:
1382+
break;
1383+
}
1384+
if (msg->result_code==QS_RETURNED&&delay==0)
1385+
{
1386+
progress=GetCurrentNumericState(msg);
1387+
if (progress<0)
1388+
{
1389+
elog(INFO,"Counting Progress doesn't available");
1390+
PG_RETURN_FLOAT8((float8)-1);
1391+
}
1392+
else
1393+
PG_RETURN_FLOAT8((float8)progress);
1394+
}
1395+
elseif (msg->result_code==QS_RETURNED)
1396+
{
1397+
while (msg->result_code==QS_RETURNED)
1398+
{
1399+
progress=GetCurrentNumericState(msg);
1400+
if (progress>old_progress)
1401+
{
1402+
elog(INFO,"\rProgress = %f",progress);
1403+
old_progress=progress;
1404+
}
1405+
elseif (progress<0)
1406+
{
1407+
elog(INFO,"Counting Progress doesn't available");
1408+
break;
1409+
}
1410+
1411+
for (inti=0;i<delay;i++)
1412+
{
1413+
pg_usleep(1000000);
1414+
CHECK_FOR_INTERRUPTS();
1415+
}
1416+
1417+
bg_worker_procs=GetRemoteBackendWorkers(proc);
1418+
msgs=GetRemoteBackendQueryStates(proc,
1419+
bg_worker_procs,
1420+
0,1,0,0,0,
1421+
EXPLAIN_FORMAT_JSON);
1422+
if (list_length(msgs)==0)
1423+
elog(WARNING,"backend does not reply");
1424+
msg= (shm_mq_msg*)linitial(msgs);
1425+
}
1426+
if (progress>-1)
1427+
elog(INFO,"\rProgress = 1.000000");
1428+
PG_RETURN_FLOAT8((float8)1);
1429+
}
1430+
PG_RETURN_FLOAT8((float8)-1);
1431+
}

0 commit comments

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