From: | Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> |
---|---|
To: | Nathan Bossart <nathandbossart(at)gmail(dot)com> |
Cc: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, Michael Paquier <michael(at)paquier(dot)xyz>, vignesh C <vignesh21(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: add PROCESS_MAIN to VACUUM |
Date: | 2023-03-02 03:58:32 |
Message-ID: | CAD21AoCho1inBEyKD9=ZsByxWCWSx3nS+AkXz8nBBVn_9YLWMA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thu, Mar 2, 2023 at 4:13 AM Nathan Bossart <nathandbossart(at)gmail(dot)com> wrote:
>
> On Wed, Mar 01, 2023 at 07:09:53PM +0100, Alvaro Herrera wrote:
> > On 2023-Mar-01, Michael Paquier wrote:
> >
> >> +-- PROCESS_MAIN option
> >> +VACUUM (PROCESS_MAIN FALSE) vactst;
> >> +VACUUM (PROCESS_MAIN FALSE, PROCESS_TOAST FALSE) vactst;
> >> +VACUUM (PROCESS_MAIN FALSE, FULL) vactst;
> >>
> >> Thinking a bit here. This set of tests does not make sure that the
> >> main relation and/or the toast relation have been actually processed.
> >> pg_stat_user_tables does not track what's happening on the toast
> >> relations. So... What about adding some tests in 100_vacuumdb.pl
> >> that rely on vacuumdb --verbose and check the logs produced? We
> >> should make sure that the toast or the main relation are processed,
> >> by tracking, for example, logs like vacuuming "schema.table". When
> >> FULL is involved, we may want to track the changes on relfilenodes
> >> depending on what's wanted.
> >
> > Maybe instead of reading the log, read values from pg_stat_all_tables.
>
> Here is an attempt at that. Thanks for the idea.
I've reviewed the v4 patch. Here is a minor comment:
+SELECT * FROM vactst_vacuum_counts;
+ left | vacuum_count
+----------+--------------
+ pg_toast | 1
+ vactst | 0
+(2 rows)
+CREATE VIEW vactst_vacuum_counts AS
+ SELECT left(s.relname, 8), s.vacuum_count
+ FROM pg_stat_all_tables s
+ LEFT JOIN pg_class c ON s.relid = c.reltoastrelid
+ WHERE c.relname = 'vactst' OR s.relname = 'vactst'
+ ORDER BY s.relname;
Cutting the toast relation name to 'pg_toast' is a bit confusing to me
as we have the pg_toast schema. How about using the following query
instead to improve the readability?
SELECT
CASE WHEN c.relname IS NULL THEN
s.relname
ELSE
'toast for ' || c.relname
END as relname,
s.vacuum_count
FROM pg_stat_all_tables s
LEFT JOIN pg_class c ON s.relid = c.reltoastrelid
WHERE c.relname = 'vactst' OR s.relname = 'vactst'
We will get like:
SELECT * FROM vactst_vacuum_counts;
relname | vacuum_count
------------------+--------------
toast for vactst | 0
vactst | 1
(2 rows)
The rest looks good to me.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Kapila | 2023-03-02 04:07:00 | Re: Time delayed LR (WAS Re: logical replication restrictions) |
Previous Message | Richard Guo | 2023-03-02 03:13:39 | Re: Making empty Bitmapsets always be NULL |