Skip to content

Navigation Menu

Sign in

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
Appearance settings

Commit 46d490a

Browse filesBrowse files
michaelpqmichaelpq
committed
Improve the regression tests of VACUUM (PROCESS_TOAST)
All the regression tests of VACUUM (PROCESS_TOAST) were only checking if the commands were able to run, without checking if VACUUM was really running on what it should. This expands this set of tests so as we now look at pg_stat_all_tables.vacuum_count to see how many vacuums have been run on a given table and its toast relation. Extracted from a larger patch by the same author, as this is useful on its own. Special thanks to Álvaro Herrera for the idea of using pg_stat_all_tables to check the state of the toast relation. Author: Nathan Bossart Reviewed-by: Masahiko Sawada Discussion: https://postgr.es/m/20221230000028.GA435655@nathanxps13
1 parent 9effa55 commit 46d490a
Copy full SHA for 46d490a

File tree

2 files changed

+52
-8
lines changed
Filter options

2 files changed

+52
-8
lines changed

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

Copy file name to clipboardExpand all lines: src/test/regress/expected/vacuum.out
+32-4Lines changed: 32 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -277,17 +277,45 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
277277
ANALYZE vactst;
278278
COMMIT;
279279
-- PROCESS_TOAST option
280-
ALTER TABLE vactst ADD COLUMN t TEXT;
281-
ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
282-
VACUUM (PROCESS_TOAST FALSE) vactst;
283-
VACUUM (PROCESS_TOAST FALSE, FULL) vactst;
280+
CREATE TABLE vac_option_tab (a INT, t TEXT);
281+
INSERT INTO vac_option_tab SELECT a, 't' || a FROM generate_series(1, 10) AS a;
282+
ALTER TABLE vac_option_tab ALTER COLUMN t SET STORAGE EXTERNAL;
283+
-- Check the number of vacuums done on table vac_option_tab and on its
284+
-- toast relation, to check that PROCESS_TOAST works on what it should.
285+
CREATE VIEW vac_option_tab_counts AS
286+
SELECT CASE WHEN c.relname IS NULL
287+
THEN 'main' ELSE 'toast' END as rel,
288+
s.vacuum_count
289+
FROM pg_stat_all_tables s
290+
LEFT JOIN pg_class c ON s.relid = c.reltoastrelid
291+
WHERE c.relname = 'vac_option_tab' OR s.relname = 'vac_option_tab'
292+
ORDER BY rel;
293+
VACUUM (PROCESS_TOAST TRUE) vac_option_tab;
294+
SELECT * FROM vac_option_tab_counts;
295+
rel | vacuum_count
296+
-------+--------------
297+
main | 1
298+
toast | 1
299+
(2 rows)
300+
301+
VACUUM (PROCESS_TOAST FALSE) vac_option_tab;
302+
SELECT * FROM vac_option_tab_counts;
303+
rel | vacuum_count
304+
-------+--------------
305+
main | 2
306+
toast | 1
307+
(2 rows)
308+
309+
VACUUM (PROCESS_TOAST FALSE, FULL) vac_option_tab; -- error
284310
ERROR: PROCESS_TOAST required with VACUUM FULL
285311
-- SKIP_DATABASE_STATS option
286312
VACUUM (SKIP_DATABASE_STATS) vactst;
287313
-- ONLY_DATABASE_STATS option
288314
VACUUM (ONLY_DATABASE_STATS);
289315
VACUUM (ONLY_DATABASE_STATS) vactst; -- error
290316
ERROR: ONLY_DATABASE_STATS cannot be specified with a list of tables
317+
DROP VIEW vac_option_tab_counts;
318+
DROP TABLE vac_option_tab;
291319
DROP TABLE vaccluster;
292320
DROP TABLE vactst;
293321
DROP TABLE vacparted;

‎src/test/regress/sql/vacuum.sql

Copy file name to clipboardExpand all lines: src/test/regress/sql/vacuum.sql
+20-4Lines changed: 20 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -232,10 +232,24 @@ ANALYZE vactst;
232232
COMMIT;
233233

234234
-- PROCESS_TOAST option
235-
ALTER TABLE vactst ADD COLUMN t TEXT;
236-
ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL;
237-
VACUUM (PROCESS_TOAST FALSE) vactst;
238-
VACUUM (PROCESS_TOAST FALSE, FULL) vactst;
235+
CREATE TABLE vac_option_tab (a INT, t TEXT);
236+
INSERT INTO vac_option_tab SELECT a, 't' || a FROM generate_series(1, 10) AS a;
237+
ALTER TABLE vac_option_tab ALTER COLUMN t SET STORAGE EXTERNAL;
238+
-- Check the number of vacuums done on table vac_option_tab and on its
239+
-- toast relation, to check that PROCESS_TOAST works on what it should.
240+
CREATE VIEW vac_option_tab_counts AS
241+
SELECT CASE WHEN c.relname IS NULL
242+
THEN 'main' ELSE 'toast' END as rel,
243+
s.vacuum_count
244+
FROM pg_stat_all_tables s
245+
LEFT JOIN pg_class c ON s.relid = c.reltoastrelid
246+
WHERE c.relname = 'vac_option_tab' OR s.relname = 'vac_option_tab'
247+
ORDER BY rel;
248+
VACUUM (PROCESS_TOAST TRUE) vac_option_tab;
249+
SELECT * FROM vac_option_tab_counts;
250+
VACUUM (PROCESS_TOAST FALSE) vac_option_tab;
251+
SELECT * FROM vac_option_tab_counts;
252+
VACUUM (PROCESS_TOAST FALSE, FULL) vac_option_tab; -- error
239253

240254
-- SKIP_DATABASE_STATS option
241255
VACUUM (SKIP_DATABASE_STATS) vactst;
@@ -244,6 +258,8 @@ VACUUM (SKIP_DATABASE_STATS) vactst;
244258
VACUUM (ONLY_DATABASE_STATS);
245259
VACUUM (ONLY_DATABASE_STATS) vactst; -- error
246260

261+
DROP VIEW vac_option_tab_counts;
262+
DROP TABLE vac_option_tab;
247263
DROP TABLE vaccluster;
248264
DROP TABLE vactst;
249265
DROP TABLE vacparted;

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.