Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-28 Thread Oskari Saarenmaa
21.12.2014, 18:48, Fabrízio de Royes Mello kirjoitti: > I work with some customer that have databases with a lot of schemas and > sometimes we need to run manual VACUUM in one schema, and would be nice > to have a new option to run vacuum in relations from a specific schema. > > The new syntax cou

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-24 Thread Jim Nasby
On 12/23/14, 8:49 PM, Fabrízio de Royes Mello wrote: Em terça-feira, 23 de dezembro de 2014, Jim Nasby mailto:jim.na...@bluetreble.com>> escreveu: On 12/23/14, 8:54 AM, Fabrízio de Royes Mello wrote: > Right now a lot of people just work around this with things like DO blocks, bu

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-23 Thread Fabrízio de Royes Mello
Em terça-feira, 23 de dezembro de 2014, Jim Nasby escreveu: > On 12/23/14, 8:54 AM, Fabrízio de Royes Mello wrote: > >> > Right now a lot of people just work around this with things like DO >> blocks, but as mentioned elsewhere in the thread that fails for commands >> that can't be in a transact

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-23 Thread Jim Nasby
On 12/23/14, 7:44 AM, Robert Haas wrote: On Mon, Dec 22, 2014 at 5:00 PM, Jim Nasby wrote: I would MUCH rather that we find a way to special-case executing non-transactional commands dynamically, because VACUUM isn't the only one that suffers from this problem. Is pg_background a solution to

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-23 Thread Jim Nasby
On 12/23/14, 8:54 AM, Fabrízio de Royes Mello wrote: > Right now a lot of people just work around this with things like DO blocks, but as mentioned elsewhere in the thread that fails for commands that can't be in a transaction. > I use "dblink" to solve it. :-) So... how about instead of s

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-23 Thread Alvaro Herrera
Robert Haas wrote: > On Mon, Dec 22, 2014 at 11:51 AM, Alvaro Herrera > wrote: > > Multi-table CLUSTER uses multiple transactions, so this should not be an > > issue. That said, I don't think there's much point in CLUSTER SCHEMA, > > much less TRUNCATE SCHEMA. Do you normally organize your schem

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-23 Thread Fabrízio de Royes Mello
On Mon, Dec 22, 2014 at 8:02 PM, Jim Nasby wrote: > > On 12/21/14, 8:55 PM, Fabrízio de Royes Mello wrote: >> >> > And why that, but not >> > say schema-wide ANALYZE, CLUSTER, TRUNCATE, ... >> > >> >> +1. I can write patches for each of this maintenance statem

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-23 Thread Robert Haas
On Mon, Dec 22, 2014 at 11:51 AM, Alvaro Herrera wrote: > Multi-table CLUSTER uses multiple transactions, so this should not be an > issue. That said, I don't think there's much point in CLUSTER SCHEMA, > much less TRUNCATE SCHEMA. Do you normally organize your schemas so > that there are some t

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-23 Thread Robert Haas
On Mon, Dec 22, 2014 at 5:00 PM, Jim Nasby wrote: > I would MUCH rather that we find a way to special-case executing > non-transactional commands dynamically, because VACUUM isn't the only one > that suffers from this problem. Is pg_background a solution to this problem? -- Robert Haas Enterpri

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-22 Thread Jim Nasby
On 12/21/14, 8:55 PM, Fabrízio de Royes Mello wrote: > And why that, but not > say schema-wide ANALYZE, CLUSTER, TRUNCATE, ... > +1. I can write patches for each of this maintenance statement too. If we're going to go that route, then perhaps it would

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-22 Thread Jim Nasby
On 12/22/14, 10:05 AM, Andres Freund wrote: While the feature itself might be fairly innocuous, I'm just wondering >why we need to encourage manual vacuuming. And why that, but not >say schema-wide ANALYZE, CLUSTER, TRUNCATE, ... There's one argument for supporting more for VACUUM than the rest

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-22 Thread Andrew Dunstan
On 12/21/2014 02:18 PM, Tom Lane wrote: =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= writes: I work with some customer that have databases with a lot of schemas and sometimes we need to run manual VACUUM in one schema, and would be nice to have a new option to run vacuum in relations from a specif

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-22 Thread Fabrízio de Royes Mello
On Mon, Dec 22, 2014 at 3:17 PM, Alvaro Herrera wrote: > > Stephen Frost wrote: > > * Andres Freund (and...@2ndquadrant.com) wrote: > > > > There's one argument for supporting more for VACUUM than the rest - it > > > can't be executed directly as the result of a query as the others > > > can... I

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-22 Thread Stephen Frost
* Andres Freund (and...@2ndquadrant.com) wrote: > On 2014-12-22 12:12:12 -0500, Stephen Frost wrote: > > We might end up turning the autovacuum process into a generalized > > scheduler/cron-like entity that way though. > > I'm not talking about autovacuum, just plain vacuumdb. Oh, right, clearly

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-22 Thread Alvaro Herrera
Stephen Frost wrote: > * Andres Freund (and...@2ndquadrant.com) wrote: > > There's one argument for supporting more for VACUUM than the rest - it > > can't be executed directly as the result of a query as the others > > can... I wonder if that'd not better be answered by adding a feature to > > va

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-22 Thread Andres Freund
On 2014-12-22 12:12:12 -0500, Stephen Frost wrote: > * Andres Freund (and...@2ndquadrant.com) wrote: > > On 2014-12-21 14:18:33 -0500, Tom Lane wrote: > > > While the feature itself might be fairly innocuous, I'm just wondering > > > why we need to encourage manual vacuuming. And why that, but not

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-22 Thread Stephen Frost
* Andres Freund (and...@2ndquadrant.com) wrote: > On 2014-12-21 14:18:33 -0500, Tom Lane wrote: > > While the feature itself might be fairly innocuous, I'm just wondering > > why we need to encourage manual vacuuming. And why that, but not > > say schema-wide ANALYZE, CLUSTER, TRUNCATE, ... > > T

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-22 Thread Alvaro Herrera
Stephen Frost wrote: > * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: > > Overall, this whole line of development seems like bloating the parse > > tables for little gain. > > Still, I see this point also. I do think it'd be really great if we > could figure out a way to segregate these kind

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-22 Thread Stephen Frost
* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: > Multi-table CLUSTER uses multiple transactions, so this should not be an > issue. That said, I don't think there's much point in CLUSTER SCHEMA, > much less TRUNCATE SCHEMA. Do you normally organize your schemas so > that there are some that co

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-22 Thread Christoph Berg
Re: Alvaro Herrera 2014-12-22 <20141222165157.gd1...@alvh.no-ip.org> > Multi-table CLUSTER uses multiple transactions, so this should not be an > issue. That said, I don't think there's much point in CLUSTER SCHEMA, > much less TRUNCATE SCHEMA. Do you normally organize your schemas so > that ther

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-22 Thread Alvaro Herrera
José Luis Tallón wrote: > On 12/21/2014 10:30 PM, Fabrízio de Royes Mello wrote: > >[snip] > > I do agree that "vacuum schema" might very well be useful (I'll probably use > it myself from time to time, too). > ANALYZE SCHEMA (specially coupled with some transaction-wide "SET > statistics_target"

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-22 Thread Andres Freund
On 2014-12-21 14:18:33 -0500, Tom Lane wrote: > =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= writes: > > I work with some customer that have databases with a lot of schemas and > > sometimes we need to run manual VACUUM in one schema, and would be nice to > > have a new option to run vacuum in relatio

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-22 Thread José Luis Tallón
On 12/21/2014 10:30 PM, Fabrízio de Royes Mello wrote: [snip] I do agree that "vacuum schema" might very well be useful (I'll probably use it myself from time to time, too). ANALYZE SCHEMA (specially coupled with some transaction-wide "SET statistics_target" could be beneficial) > And why

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-21 Thread Fabrízio de Royes Mello
Em segunda-feira, 22 de dezembro de 2014, Jim Nasby < jim.na...@bluetreble.com> escreveu: > On 12/21/14, 3:30 PM, Fabrízio de Royes Mello wrote: > >> >> On Sun, Dec 21, 2014 at 5:18 PM, Tom Lane > t...@sss.pgh.pa.us>> wrote: >> > >> > =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= >

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-21 Thread Jim Nasby
On 12/21/14, 3:30 PM, Fabrízio de Royes Mello wrote: On Sun, Dec 21, 2014 at 5:18 PM, Tom Lane mailto:t...@sss.pgh.pa.us>> wrote: > > =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= mailto:fabriziome...@gmail.com>> writes: > > I work with some customer that have databases with a lot of schemas and

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-21 Thread Fabrízio de Royes Mello
On Sun, Dec 21, 2014 at 5:18 PM, Tom Lane wrote: > > =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= writes: > > I work with some customer that have databases with a lot of schemas and > > sometimes we need to run manual VACUUM in one schema, and would be nice to > > have a new option to run vacuum in r

Re: [HACKERS] Proposal "VACUUM SCHEMA"

2014-12-21 Thread Tom Lane
=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= writes: > I work with some customer that have databases with a lot of schemas and > sometimes we need to run manual VACUUM in one schema, and would be nice to > have a new option to run vacuum in relations from a specific schema. I'm pretty skeptical of th