Skip to content

Commit 8a4f618

Browse files
tvondratvondra
committed
Report progress of COPY commands
This commit introduces a view pg_stat_progress_copy, reporting progress of COPY commands. This allows rough estimates how far a running COPY progressed, with the caveat that the total number of bytes may not be available in some cases (e.g. when the input comes from the client). Author: Josef Šimánek Reviewed-by: Fujii Masao, Bharath Rupireddy, Vignesh C, Matthias van de Meent Discussion: https://postgr.es/m/CAFp7QwqMGEi4OyyaLEK9DR0+E+oK3UtA4bEjDVCa4bNkwUY2PQ@mail.gmail.com Discussion: https://postgr.es/m/CAFp7Qwr6_FmRM6pCO0x_a0mymOfX_Gg+FEKet4XaTGSW=LitKQ@mail.gmail.com
1 parent ca8217c commit 8a4f618

File tree

10 files changed

+176
-5
lines changed

10 files changed

+176
-5
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 107 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -399,6 +399,12 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
399399
</entry>
400400
</row>
401401

402+
<row>
403+
<entry><structname>pg_stat_progress_copy</structname><indexterm><primary>pg_stat_progress_copy</primary></indexterm></entry>
404+
<entry>One row for each backend running <command>COPY</command>, showing current progress.
405+
See <xref linkend='copy-progress-reporting'/>.
406+
</entry>
407+
</row>
402408
</tbody>
403409
</tgroup>
404410
</table>
@@ -5247,6 +5253,7 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
52475253
which support progress reporting are <command>ANALYZE</command>,
52485254
<command>CLUSTER</command>,
52495255
<command>CREATE INDEX</command>, <command>VACUUM</command>,
5256+
<command>COPY</command>,
52505257
and <xref linkend="protocol-replication-base-backup"/> (i.e., replication
52515258
command that <xref linkend="app-pgbasebackup"/> issues to take
52525259
a base backup).
@@ -6396,6 +6403,106 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
63966403
</table>
63976404

63986405
</sect2>
6406+
6407+
<sect2 id="copy-progress-reporting">
6408+
<title>COPY Progress Reporting</title>
6409+
6410+
<indexterm>
6411+
<primary>pg_stat_progress_copy</primary>
6412+
</indexterm>
6413+
6414+
<para>
6415+
Whenever <command>COPY</command> is running, the
6416+
<structname>pg_stat_progress_copy</structname> view will contain one row
6417+
for each backend that is currently running <command>COPY</command> command.
6418+
The table bellow describes the information that will be reported and provide
6419+
information how to interpret it.
6420+
</para>
6421+
6422+
<table id="pg-stat-progress-copy-view" xreflabel="pg_stat_progress_copy">
6423+
<title><structname>pg_stat_progress_copy</structname> View</title>
6424+
<tgroup cols="1">
6425+
<thead>
6426+
<row>
6427+
<entry role="catalog_table_entry"><para role="column_definition">
6428+
Column Type
6429+
</para>
6430+
<para>
6431+
Description
6432+
</para></entry>
6433+
</row>
6434+
</thead>
6435+
6436+
<tbody>
6437+
<row>
6438+
<entry role="catalog_table_entry"><para role="column_definition">
6439+
<structfield>pid</structfield> <type>integer</type>
6440+
</para>
6441+
<para>
6442+
Process ID of backend.
6443+
</para></entry>
6444+
</row>
6445+
6446+
<row>
6447+
<entry role="catalog_table_entry"><para role="column_definition">
6448+
<structfield>datid</structfield> <type>text</type>
6449+
</para>
6450+
<para>
6451+
OID of the database to which this backend is connected.
6452+
</para></entry>
6453+
</row>
6454+
6455+
<row>
6456+
<entry role="catalog_table_entry"><para role="column_definition">
6457+
<structfield>datname</structfield> <type>name</type>
6458+
</para>
6459+
<para>
6460+
Name of the database to which this backend is connected.
6461+
</para></entry>
6462+
</row>
6463+
6464+
<row>
6465+
<entry role="catalog_table_entry"><para role="column_definition">
6466+
<structfield>relid</structfield> <type>oid</type>
6467+
</para>
6468+
<para>
6469+
OID of the table on which the <command>COPY</command> command is executed.
6470+
It is set to 0 if <command>SELECT</command> query is provided.
6471+
</para></entry>
6472+
</row>
6473+
6474+
<row>
6475+
<entry role="catalog_table_entry"><para role="column_definition">
6476+
<structfield>bytes_processed</structfield> <type>bigint</type>
6477+
</para>
6478+
<para>
6479+
Number of bytes already processed by <command>COPY</command> command.
6480+
</para></entry>
6481+
</row>
6482+
6483+
<row>
6484+
<entry role="catalog_table_entry"><para role="column_definition">
6485+
<structfield>bytes_total</structfield> <type>bigint</type>
6486+
</para>
6487+
<para>
6488+
Size of source file for <command>COPY FROM</command> command in bytes.
6489+
It is set to 0 if not available.
6490+
</para></entry>
6491+
</row>
6492+
6493+
<row>
6494+
<entry role="catalog_table_entry"><para role="column_definition">
6495+
<structfield>lines_processed</structfield> <type>bigint</type>
6496+
</para>
6497+
<para>
6498+
Number of lines already processed by <command>COPY</command> command.
6499+
</para></entry>
6500+
</row>
6501+
</tbody>
6502+
</tgroup>
6503+
</table>
6504+
</sect2>
6505+
63996506
</sect1>
64006507

64016508
<sect1 id="dynamic-trace">

‎src/backend/catalog/system_views.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1117,6 +1117,17 @@ CREATE VIEW pg_stat_progress_basebackup AS
11171117
S.param5 AS tablespaces_streamed
11181118
FROM pg_stat_get_progress_info('BASEBACKUP') AS S;
11191119

1120+
1121+
CREATE VIEW pg_stat_progress_copy AS
1122+
SELECT
1123+
S.pid AS pid, S.datid AS datid, D.datname AS datname,
1124+
S.relid AS relid,
1125+
S.param1 AS bytes_processed,
1126+
S.param2 AS bytes_total,
1127+
S.param3 AS lines_processed
1128+
FROM pg_stat_get_progress_info('COPY') AS S
1129+
LEFT JOIN pg_database D ON S.datid = D.oid;
1130+
11201131
CREATE VIEW pg_user_mappings AS
11211132
SELECT
11221133
U.oid AS umid,

‎src/backend/commands/copyfrom.c

Lines changed: 15 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,7 @@
2525
#include "access/xlog.h"
2626
#include "commands/copy.h"
2727
#include "commands/copyfrom_internal.h"
28+
#include "commands/progress.h"
2829
#include "commands/trigger.h"
2930
#include "executor/execPartition.h"
3031
#include "executor/executor.h"
@@ -35,6 +36,7 @@
3536
#include "libpq/pqformat.h"
3637
#include "miscadmin.h"
3738
#include "optimizer/optimizer.h"
39+
#include "pgstat.h"
3840
#include "rewrite/rewriteHandler.h"
3941
#include "storage/fd.h"
4042
#include "tcop/tcopprot.h"
@@ -1100,9 +1102,10 @@ CopyFrom(CopyFromState cstate)
11001102
/*
11011103
* We count only tuples not suppressed by a BEFORE INSERT trigger
11021104
* or FDW; this is the same definition used by nodeModifyTable.c
1103-
* for counting tuples inserted by an INSERT command.
1105+
* for counting tuples inserted by an INSERT command. Update
1106+
* progress of the COPY command as well.
11041107
*/
1105-
processed++;
1108+
pgstat_progress_update_param(PROGRESS_COPY_LINES_PROCESSED, ++processed);
11061109
}
11071110
}
11081111

@@ -1415,6 +1418,12 @@ BeginCopyFrom(ParseState *pstate,
14151418
}
14161419
}
14171420

1421+
1422+
/* initialize progress */
1423+
pgstat_progress_start_command(PROGRESS_COMMAND_COPY,
1424+
cstate->rel ? RelationGetRelid(cstate->rel) : InvalidOid);
1425+
cstate->bytes_processed = 0;
1426+
14181427
/* We keep those variables in cstate. */
14191428
cstate->in_functions = in_functions;
14201429
cstate->typioparams = typioparams;
@@ -1479,6 +1488,8 @@ BeginCopyFrom(ParseState *pstate,
14791488
ereport(ERROR,
14801489
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
14811490
errmsg("\"%s\" is a directory", cstate->filename)));
1491+
1492+
pgstat_progress_update_param(PROGRESS_COPY_BYTES_TOTAL, st.st_size);
14821493
}
14831494
}
14841495

@@ -1522,6 +1533,8 @@ EndCopyFrom(CopyFromState cstate)
15221533
cstate->filename)));
15231534
}
15241535

1536+
pgstat_progress_end_command();
1537+
15251538
MemoryContextDelete(cstate->copycontext);
15261539
pfree(cstate);
15271540
}

‎src/backend/commands/copyfromparse.c

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,11 +20,13 @@
2020

2121
#include "commands/copy.h"
2222
#include "commands/copyfrom_internal.h"
23+
#include "commands/progress.h"
2324
#include "executor/executor.h"
2425
#include "libpq/libpq.h"
2526
#include "libpq/pqformat.h"
2627
#include "mb/pg_wchar.h"
2728
#include "miscadmin.h"
29+
#include "pgstat.h"
2830
#include "port/pg_bswap.h"
2931
#include "utils/memutils.h"
3032
#include "utils/rel.h"
@@ -384,6 +386,8 @@ CopyLoadRawBuf(CopyFromState cstate)
384386
cstate->raw_buf[nbytes] = '\0';
385387
cstate->raw_buf_index = 0;
386388
cstate->raw_buf_len = nbytes;
389+
cstate->bytes_processed += nbytes;
390+
pgstat_progress_update_param(PROGRESS_COPY_BYTES_PROCESSED, cstate->bytes_processed);
387391
return (inbytes > 0);
388392
}
389393

‎src/backend/commands/copyto.c

Lines changed: 20 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,7 @@
2424
#include "access/xact.h"
2525
#include "access/xlog.h"
2626
#include "commands/copy.h"
27+
#include "commands/progress.h"
2728
#include "executor/execdesc.h"
2829
#include "executor/executor.h"
2930
#include "executor/tuptable.h"
@@ -32,6 +33,7 @@
3233
#include "mb/pg_wchar.h"
3334
#include "miscadmin.h"
3435
#include "optimizer/optimizer.h"
36+
#include "pgstat.h"
3537
#include "rewrite/rewriteHandler.h"
3638
#include "storage/fd.h"
3739
#include "tcop/tcopprot.h"
@@ -95,6 +97,7 @@ typedef struct CopyToStateData
9597

9698
FmgrInfo *out_functions; /* lookup info for output functions */
9799
MemoryContext rowcontext; /* per-row evaluation context */
100+
uint64 bytes_processed; /* number of bytes processed so far */
98101

99102
} CopyToStateData;
100103

@@ -288,6 +291,10 @@ CopySendEndOfRow(CopyToState cstate)
288291
break;
289292
}
290293

294+
/* Update the progress */
295+
cstate->bytes_processed += fe_msgbuf->len;
296+
pgstat_progress_update_param(PROGRESS_COPY_BYTES_PROCESSED, cstate->bytes_processed);
297+
291298
resetStringInfo(fe_msgbuf);
292299
}
293300

@@ -363,6 +370,8 @@ EndCopy(CopyToState cstate)
363370
cstate->filename)));
364371
}
365372

373+
pgstat_progress_end_command();
374+
366375
MemoryContextDelete(cstate->copycontext);
367376
pfree(cstate);
368377
}
@@ -760,6 +769,11 @@ BeginCopyTo(ParseState *pstate,
760769
}
761770
}
762771

772+
/* initialize progress */
773+
pgstat_progress_start_command(PROGRESS_COMMAND_COPY,
774+
cstate->rel ? RelationGetRelid(cstate->rel) : InvalidOid);
775+
cstate->bytes_processed = 0;
776+
763777
MemoryContextSwitchTo(oldcontext);
764778

765779
return cstate;
@@ -938,7 +952,9 @@ CopyTo(CopyToState cstate)
938952

939953
/* Format and send the data */
940954
CopyOneRowTo(cstate, slot);
941-
processed++;
955+
956+
/* Increment amount of processed tuples and update the progress */
957+
pgstat_progress_update_param(PROGRESS_COPY_LINES_PROCESSED, ++processed);
942958
}
943959

944960
ExecDropSingleTupleTableSlot(slot);
@@ -1303,7 +1319,9 @@ copy_dest_receive(TupleTableSlot *slot, DestReceiver *self)
13031319

13041320
/* Send the data */
13051321
CopyOneRowTo(cstate, slot);
1306-
myState->processed++;
1322+
1323+
/* Increment amount of processed tuples and update the progress */
1324+
pgstat_progress_update_param(PROGRESS_COPY_LINES_PROCESSED, ++myState->processed);
13071325

13081326
return true;
13091327
}

‎src/backend/utils/adt/pgstatfuncs.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -494,6 +494,8 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
494494
cmdtype = PROGRESS_COMMAND_CREATE_INDEX;
495495
else if (pg_strcasecmp(cmd, "BASEBACKUP") == 0)
496496
cmdtype = PROGRESS_COMMAND_BASEBACKUP;
497+
else if (pg_strcasecmp(cmd, "COPY") == 0)
498+
cmdtype = PROGRESS_COMMAND_COPY;
497499
else
498500
ereport(ERROR,
499501
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),

‎src/include/commands/copyfrom_internal.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -154,6 +154,7 @@ typedef struct CopyFromStateData
154154
char *raw_buf;
155155
int raw_buf_index; /* next byte to process */
156156
int raw_buf_len; /* total # of bytes stored */
157+
uint64 bytes_processed;/* number of bytes processed so far */
157158
/* Shorthand for number of unconsumed bytes available in raw_buf */
158159
#define RAW_BUF_BYTES(cstate) ((cstate)->raw_buf_len - (cstate)->raw_buf_index)
159160
} CopyFromStateData;

‎src/include/commands/progress.h

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -133,4 +133,9 @@
133133
#define PROGRESS_BASEBACKUP_PHASE_WAIT_WAL_ARCHIVE 4
134134
#define PROGRESS_BASEBACKUP_PHASE_TRANSFER_WAL 5
135135

136+
/* Commands of PROGRESS_COPY */
137+
#define PROGRESS_COPY_BYTES_PROCESSED 0
138+
#define PROGRESS_COPY_BYTES_TOTAL 1
139+
#define PROGRESS_COPY_LINES_PROCESSED 2
140+
136141
#endif

‎src/include/pgstat.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1077,7 +1077,8 @@ typedef enum ProgressCommandType
10771077
PROGRESS_COMMAND_ANALYZE,
10781078
PROGRESS_COMMAND_CLUSTER,
10791079
PROGRESS_COMMAND_CREATE_INDEX,
1080-
PROGRESS_COMMAND_BASEBACKUP
1080+
PROGRESS_COMMAND_BASEBACKUP,
1081+
PROGRESS_COMMAND_COPY
10811082
} ProgressCommandType;
10821083

10831084
#define PGSTAT_NUM_PROGRESS_PARAM 20

‎src/test/regress/expected/rules.out

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1937,6 +1937,15 @@ pg_stat_progress_cluster| SELECT s.pid,
19371937
s.param8 AS index_rebuild_count
19381938
FROM (pg_stat_get_progress_info('CLUSTER'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)
19391939
LEFT JOIN pg_database d ON ((s.datid = d.oid)));
1940+
pg_stat_progress_copy| SELECT s.pid,
1941+
s.datid,
1942+
d.datname,
1943+
s.relid,
1944+
s.param1 AS bytes_processed,
1945+
s.param2 AS bytes_total,
1946+
s.param3 AS lines_processed
1947+
FROM (pg_stat_get_progress_info('COPY'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)
1948+
LEFT JOIN pg_database d ON ((s.datid = d.oid)));
19401949
pg_stat_progress_create_index| SELECT s.pid,
19411950
s.datid,
19421951
d.datname,

0 commit comments

Comments
 (0)