Re: [Proposal] Allow users to specify multiple tables in VACUUM commands

From: "Bossart, Nathan" <bossartn(at)amazon(dot)com>
To: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, "Masahiko Sawada" <sawada(dot)mshk(at)gmail(dot)com>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>
Subject: Re: [Proposal] Allow users to specify multiple tables in VACUUM commands
Date: 2017-09-22 15:56:41
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-hackers

On 9/21/17, 9:55 PM, "Michael Paquier" <michael(dot)paquier(at)gmail(dot)com> wrote:
> I still think that ExecVacuum() should pass a list of VacuumRelation
> objects to vacuum(), and get_rel_oids() should take in input a list,
> and return a completed lists. This way the decision-making of doing
> everything in the same transaction should happens once in vacuum().
> And actually, if several relations are defined with VACUUM, your patch
> would not use one transaction per table as use_own_xacts would be set
> to false. I think that Tom meant that relations whose processed has
> finished have to be committed immediately. Per your patch, the commit
> happens once all relations are committed.

Sorry, I must have misunderstood. I've attached an updated patch that
looks more like what you described. I also cleaned up the test cases
a bit.

IIUC the only time use_own_xacts will be false is when we are only
doing ANALYZE and at least one of the following is true:

1. We are in a transaction block.
2. We are processing only one relation.

From the code, it appears that vacuum_rel() always starts and commits a
new transaction for each relation:

* vacuum_rel expects to be entered with no transaction active; it will
* start and commit its own transaction. But we are called by an SQL

So, by ensuring that get_rel_oids() returns a list whenever multiple
tables are specified, we are making sure that commands like

ANALYZE table1, table2, table3;

create transactions for each processed relation (as long as they are
not inside a transaction block). I suppose the alternative would be
to call vacuum() for each relation and to remove the restriction that
we must be processing more than one relation for use_own_xacts to be

Am I understanding this correctly?


Attachment Content-Type Size
vacuum_multiple_tables_v20.patch application/octet-stream 29.1 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message chenhj 2017-09-22 16:16:58 Re: [PATCH]make pg_rewind to not copy useless WAL files
Previous Message Peter Eisentraut 2017-09-22 15:50:48 Re: additional contrib test suites