Skip to content

Commit 15639d5

Browse files
michaelpqmichaelpq
committed
Add some basic tests for progress reporting of COPY
This tests some basic features for progress reporting of COPY, relying on an INSERT trigger that gets fired when doing COPY FROM with a file or stdin, checking for sizes, number of tuples processed, and number of tuples excluded by a WHERE clause. Author: Josef Šimánek, Matthias van de Meent Reviewed-by: Michael Paquier, Justin Pryzby, Bharath Rupireddy, Tomas Vondra Discussion: https://postgr.es/m/CAEze2WiOcgdH4aQA8NtZq-4dgvnJzp8PohdeKchPkhMY-jWZXA@mail.gmail.com
1 parent 9aa491a commit 15639d5

File tree

2 files changed

+116
-0
lines changed

2 files changed

+116
-0
lines changed

‎src/test/regress/input/copy.source

Lines changed: 62 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -201,3 +201,65 @@ select * from parted_copytest where b = 1;
201201
select * from parted_copytest where b = 2;
202202

203203
drop table parted_copytest;
204+
205+
--
206+
-- Progress reporting for COPY
207+
--
208+
create table tab_progress_reporting (
209+
name text,
210+
age int4,
211+
location point,
212+
salary int4,
213+
manager name
214+
);
215+
216+
-- Add a trigger to catch and print the contents of the catalog view
217+
-- pg_stat_progress_copy during data insertion. This allows to test
218+
-- the validation of some progress reports for COPY FROM where the trigger
219+
-- would fire.
220+
create function notice_after_tab_progress_reporting() returns trigger AS
221+
$$
222+
declare report record;
223+
begin
224+
-- The fields ignored here are the ones that may not remain
225+
-- consistent across multiple runs. The sizes reported may differ
226+
-- across platforms, so just check if these are strictly positive.
227+
with progress_data as (
228+
select
229+
relid::regclass::text as relname,
230+
command,
231+
type,
232+
bytes_processed > 0 as has_bytes_processed,
233+
bytes_total > 0 as has_bytes_total,
234+
tuples_processed,
235+
tuples_excluded
236+
from pg_stat_progress_copy
237+
where pid = pg_backend_pid())
238+
select into report (to_jsonb(r)) as value
239+
from progress_data r;
240+
241+
raise info 'progress: %', report.value::text;
242+
return new;
243+
end;
244+
$$ language plpgsql;
245+
246+
create trigger check_after_tab_progress_reporting
247+
after insert on tab_progress_reporting
248+
for each statement
249+
execute function notice_after_tab_progress_reporting();
250+
251+
-- Generate COPY FROM report with PIPE.
252+
copy tab_progress_reporting from stdin;
253+
sharon 25 (15,12) 1000 sam
254+
sam 30 (10,5) 2000 bill
255+
bill 20 (11,10) 1000 sharon
256+
\.
257+
258+
-- Generate COPY FROM report with FILE, with some excluded tuples.
259+
truncate tab_progress_reporting;
260+
copy tab_progress_reporting from '@abs_srcdir@/data/emp.data'
261+
where (salary < 2000);
262+
263+
drop trigger check_after_tab_progress_reporting on tab_progress_reporting;
264+
drop function notice_after_tab_progress_reporting();
265+
drop table tab_progress_reporting;

‎src/test/regress/output/copy.source

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -165,3 +165,57 @@ select * from parted_copytest where b = 2;
165165
(1 row)
166166

167167
drop table parted_copytest;
168+
--
169+
-- Progress reporting for COPY
170+
--
171+
create table tab_progress_reporting (
172+
name text,
173+
age int4,
174+
location point,
175+
salary int4,
176+
manager name
177+
);
178+
-- Add a trigger to catch and print the contents of the catalog view
179+
-- pg_stat_progress_copy during data insertion. This allows to test
180+
-- the validation of some progress reports for COPY FROM where the trigger
181+
-- would fire.
182+
create function notice_after_tab_progress_reporting() returns trigger AS
183+
$$
184+
declare report record;
185+
begin
186+
-- The fields ignored here are the ones that may not remain
187+
-- consistent across multiple runs. The sizes reported may differ
188+
-- across platforms, so just check if these are strictly positive.
189+
with progress_data as (
190+
select
191+
relid::regclass::text as relname,
192+
command,
193+
type,
194+
bytes_processed > 0 as has_bytes_processed,
195+
bytes_total > 0 as has_bytes_total,
196+
tuples_processed,
197+
tuples_excluded
198+
from pg_stat_progress_copy
199+
where pid = pg_backend_pid())
200+
select into report (to_jsonb(r)) as value
201+
from progress_data r;
202+
203+
raise info 'progress: %', report.value::text;
204+
return new;
205+
end;
206+
$$ language plpgsql;
207+
create trigger check_after_tab_progress_reporting
208+
after insert on tab_progress_reporting
209+
for each statement
210+
execute function notice_after_tab_progress_reporting();
211+
-- Generate COPY FROM report with PIPE.
212+
copy tab_progress_reporting from stdin;
213+
INFO: progress: {"type": "PIPE", "command": "COPY FROM", "relname": "tab_progress_reporting", "has_bytes_total": false, "tuples_excluded": 0, "tuples_processed": 3, "has_bytes_processed": true}
214+
-- Generate COPY FROM report with FILE, with some excluded tuples.
215+
truncate tab_progress_reporting;
216+
copy tab_progress_reporting from '@abs_srcdir@/data/emp.data'
217+
where (salary < 2000);
218+
INFO: progress: {"type": "FILE", "command": "COPY FROM", "relname": "tab_progress_reporting", "has_bytes_total": true, "tuples_excluded": 1, "tuples_processed": 2, "has_bytes_processed": true}
219+
drop trigger check_after_tab_progress_reporting on tab_progress_reporting;
220+
drop function notice_after_tab_progress_reporting();
221+
drop table tab_progress_reporting;

0 commit comments

Comments
 (0)