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

Commit46d490a

Browse files
committed
Improve the regression tests of VACUUM (PROCESS_TOAST)
All the regression tests of VACUUM (PROCESS_TOAST) were only checking ifthe commands were able to run, without checking if VACUUM was reallyrunning on what it should. This expands this set of tests so as we nowlook at pg_stat_all_tables.vacuum_count to see how many vacuums havebeen run on a given table and its toast relation.Extracted from a larger patch by the same author, as this is useful onits own.Special thanks to Álvaro Herrera for the idea of usingpg_stat_all_tables to check the state of the toast relation.Author: Nathan BossartReviewed-by: Masahiko SawadaDiscussion:https://postgr.es/m/20221230000028.GA435655@nathanxps13
1 parent9effa55 commit46d490a

File tree

2 files changed

+52
-8
lines changed

2 files changed

+52
-8
lines changed

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

Lines 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

Lines 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-
ALTERTABLE vactst ADD COLUMN tTEXT;
236-
ALTERTABLE vactst ALTER COLUMN tSET STORAGE EXTERNAL;
237-
VACUUM (PROCESS_TOAST FALSE) vactst;
238-
VACUUM (PROCESS_TOAST FALSE, FULL) vactst;
235+
CREATETABLEvac_option_tab (aINT, tTEXT);
236+
INSERT INTO vac_option_tabSELECT a,'t'|| aFROM generate_series(1,10)AS a;
237+
ALTERTABLE vac_option_tab ALTER COLUMN tSET 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+
CREATEVIEWvac_option_tab_countsAS
241+
SELECT CASE WHENc.relname ISNULL
242+
THEN'main' ELSE'toast' ENDas rel,
243+
s.vacuum_count
244+
FROM pg_stat_all_tables s
245+
LEFT JOIN pg_class cONs.relid=c.reltoastrelid
246+
WHEREc.relname='vac_option_tab'ORs.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+
DROPVIEW vac_option_tab_counts;
262+
DROPTABLE vac_option_tab;
247263
DROPTABLE vaccluster;
248264
DROPTABLE vactst;
249265
DROPTABLE vacparted;

0 commit comments

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