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 could

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 jim.na...@bluetreble.com 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

Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-23 Thread Robert Haas
On Mon, Dec 22, 2014 at 5:00 PM, Jim Nasby jim.na...@bluetreble.com 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? --

Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-23 Thread Robert Haas
On Mon, Dec 22, 2014 at 11:51 AM, 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

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 jim.na...@bluetreble.com 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

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

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

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 jim.na...@bluetreble.com 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

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 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, but as mentioned elsewhere in the thread that fails for commands that can't

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-22 Thread Andres Freund
On 2014-12-21 14:18:33 -0500, Tom Lane wrote: =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@gmail.com 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

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 could be

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 there are

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

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 kinds of

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, ... There's

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 say

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 vacuumdb

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 I was

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 alvhe...@2ndquadrant.com 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

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?= fabriziome...@gmail.com 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

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 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-21 Thread Tom Lane
=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@gmail.com 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

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 t...@sss.pgh.pa.us wrote: =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@gmail.com 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

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 t...@sss.pgh.pa.us mailto:t...@sss.pgh.pa.us wrote: =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@gmail.com mailto:fabriziome...@gmail.com writes: I work with some customer that have

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 mailto: t...@sss.pgh.pa.us wrote: =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?=