Re: [HACKERS] DDL Damage Assessment

2014-10-03 Thread Dimitri Fontaine
Jim Nasby jim.na...@bluetreble.com writes: EXPLAIN ALTER TABLE I'm thinking it would be better to have something you could set at a session level, so you don't have to stick EXPLAIN in front of all your DDL. Yeah I'm coming into that camp too, and I think the Event Trigger idea gets us

Re: [HACKERS] DDL Damage Assessment

2014-10-03 Thread Robert Haas
On Thu, Oct 2, 2014 at 5:22 PM, Stephen Frost sfr...@snowman.net wrote: * Andres Freund (and...@2ndquadrant.com) wrote: On 2014-10-02 17:03:59 -0400, Stephen Frost wrote: That sounds extremely complex. You'd have to implement the fake columns, foreign keys, indexes, etc on most execution

Re: [HACKERS] DDL Damage Assessment

2014-10-03 Thread Stephen Frost
* Jim Nasby (jim.na...@bluetreble.com) wrote: I'm thinking it would be better to have something you could set at a session level, so you don't have to stick EXPLAIN in front of all your DDL. Right, I'm agreed there. As for the dry-run idea, I don't think that's really necessary. I've never

Re: [HACKERS] DDL Damage Assessment

2014-10-03 Thread José Luis Tallón
On 10/03/2014 11:02 AM, Dimitri Fontaine wrote: Jim Nasby jim.na...@bluetreble.com writes: EXPLAIN ALTER TABLE I'm thinking it would be better to have something you could set at a session level, so you don't have to stick EXPLAIN in front of all your DDL. We were considering the

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Fabrízio de Royes Mello
On Thu, Oct 2, 2014 at 1:30 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Hi fellow hackers, I would like to work on a new feature allowing our users to assess the amount of trouble they will run into when running a DDL script on their production setups, *before* actually getting their

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Claudio Freire
On Thu, Oct 2, 2014 at 1:46 PM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: On Thu, Oct 2, 2014 at 1:30 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Hi fellow hackers, I would like to work on a new feature allowing our users to assess the amount of trouble they will run

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Harold Giménez
I think the main issue is when a table rewrite is triggered on a DDL command on a large table, as this is what frequently leads to unavailability. The idea of introducing a NOREWRITE keyword to DDL commands then came up (credit: Peter Geoghegan). When the NOREWRITE keyword is used and the DDL

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Joe Conway
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/02/2014 11:30 AM, Dimitri Fontaine wrote: Questions: 1. Do you agree that a systematic way to report what a DDL command (or script, or transaction) is going to do on your production database is a feature we should provide to our growing

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Joshua D. Drake
On 10/02/2014 09:30 AM, Dimitri Fontaine wrote: Questions: 1. Do you agree that a systematic way to report what a DDL command (or script, or transaction) is going to do on your production database is a feature we should provide to our growing user base? I would say it is late to

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Stephen Frost
* Dimitri Fontaine (dimi...@2ndquadrant.fr) wrote: 1. Do you agree that a systematic way to report what a DDL command (or script, or transaction) is going to do on your production database is a feature we should provide to our growing user base? I definitely like the idea of such a

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Stephen Frost
* Harold Giménez (har...@heroku.com) wrote: I think the main issue is when a table rewrite is triggered on a DDL command on a large table, as this is what frequently leads to unavailability. The idea of introducing a NOREWRITE keyword to DDL commands then came up (credit: Peter Geoghegan).

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Stephen Frost
* Joshua D. Drake (j...@commandprompt.com) wrote: 2. What do you think such a feature should look like? I liked the other post that said: EXPLAIN ALTER TABLE or whatever. Heck it could even be useful to have EXPLAIN ANALZYE ALTER TABLE in case people want to run it on staging/test/dev

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Josh Berkus
Questions: 1. Do you agree that a systematic way to report what a DDL command (or script, or transaction) is going to do on your production database is a feature we should provide to our growing user base? Yes. 2. What do you think such a feature should look like? As with

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread José Luis Tallón
On 10/02/2014 06:30 PM, Dimitri Fontaine wrote: Hi fellow hackers, [snip] Questions: 1. Do you agree that a systematic way to report what a DDL command (or script, or transaction) is going to do on your production database is a feature we should provide to our growing user base?

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Steven Lembark
EXPLAIN ALTER TABLE ? Good thing: People recognize it. Bad thing: People might not be able to tell the difference between a DDL and DML result. What about EXPLAIN DDL ...? The extra keyword (DDL) makes it a bit more explicit that the results are not comparable to the standard

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Claudio Freire
On Thu, Oct 2, 2014 at 4:40 PM, Stephen Frost sfr...@snowman.net wrote: * Joshua D. Drake (j...@commandprompt.com) wrote: 2. What do you think such a feature should look like? I liked the other post that said: EXPLAIN ALTER TABLE or whatever. Heck it could even be useful to have EXPLAIN

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Peter Geoghegan
On Thu, Oct 2, 2014 at 12:40 PM, Stephen Frost sfr...@snowman.net wrote: The downside of the 'explain' approach is that the script then has to be modified to put 'explain' in front of everything and then you have to go through each statement and consider it. Having a 'dry-run' transaction

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Peter Geoghegan
On Thu, Oct 2, 2014 at 1:37 PM, Peter Geoghegan p...@heroku.com wrote: And yet, in theory session 2's impact on production should not be minimal, if we consider something like EXPLAIN output. Should have been minimal, I mean. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Claudio Freire
On Thu, Oct 2, 2014 at 5:37 PM, Peter Geoghegan p...@heroku.com wrote: Session 3 is an innocent bystander. It goes to query the same table in an ordinary, routine way - a SELECT statement. Even though session 2's lock is not granted yet, session 3 is not at liberty to skip the queue and get

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Claudio Freire
On Thu, Oct 2, 2014 at 6:00 PM, Peter Geoghegan p...@heroku.com wrote: Granted, it's something that's not easily automatable, whereas a nowait is. However, rather than nowait, I'd prefer cancellable semantics, that would cancel voluntarily if any other transaction requests a conflicting lock,

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Stephen Frost
* Claudio Freire (klaussfre...@gmail.com) wrote: On Thu, Oct 2, 2014 at 4:40 PM, Stephen Frost sfr...@snowman.net wrote: The downside of the 'explain' approach is that the script then has to be modified to put 'explain' in front of everything and then you have to go through each statement

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Peter Geoghegan
On Thu, Oct 2, 2014 at 1:52 PM, Claudio Freire klaussfre...@gmail.com wrote: The explain would show the AccessExclusiveLock, so it would be enough for a heads-up to kill all idle-in-transaction holding locks on the target relation (if killable, or just wait). I think that there are very few

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Claudio Freire
On Thu, Oct 2, 2014 at 6:03 PM, Stephen Frost sfr...@snowman.net wrote: * Claudio Freire (klaussfre...@gmail.com) wrote: On Thu, Oct 2, 2014 at 4:40 PM, Stephen Frost sfr...@snowman.net wrote: The downside of the 'explain' approach is that the script then has to be modified to put 'explain'

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Peter Geoghegan
On Thu, Oct 2, 2014 at 2:04 PM, Claudio Freire klaussfre...@gmail.com wrote: I've done that manually (throw the DDL, and cancel if it takes more than a couple of seconds) on modest but relatively busy servers with quite some success. Fair enough, but that isn't the same as NOWAIT. It's

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Stephen Frost
* Peter Geoghegan (p...@heroku.com) wrote: On Thu, Oct 2, 2014 at 12:40 PM, Stephen Frost sfr...@snowman.net wrote: The downside of the 'explain' approach is that the script then has to be modified to put 'explain' in front of everything and then you have to go through each statement and

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Andres Freund
On 2014-10-02 17:03:59 -0400, Stephen Frost wrote: * Claudio Freire (klaussfre...@gmail.com) wrote: On Thu, Oct 2, 2014 at 4:40 PM, Stephen Frost sfr...@snowman.net wrote: The downside of the 'explain' approach is that the script then has to be modified to put 'explain' in front of

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Andres Freund
On 2014-10-02 13:49:36 -0300, Claudio Freire wrote: EXPLAIN ALTER TABLE ? I don't think that'll work - there's already EXPLAIN for some CREATE. At least CREATE TABLE ... AS, CREATE VIEW ... AS and SELECT INTO. Greetings, Andres Freund -- Andres Freund

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Stephen Frost
* Claudio Freire (klaussfre...@gmail.com) wrote: On Thu, Oct 2, 2014 at 6:03 PM, Stephen Frost sfr...@snowman.net wrote: That sounds extremely complex. You'd have to implement the fake columns, foreign keys, indexes, etc on most execution nodes, the planner, and even system views. Eh?

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Stephen Frost
* Andres Freund (and...@2ndquadrant.com) wrote: On 2014-10-02 17:03:59 -0400, Stephen Frost wrote: That sounds extremely complex. You'd have to implement the fake columns, foreign keys, indexes, etc on most execution nodes, the planner, and even system views. Eh? We have MVCC

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Claudio Freire
On Thu, Oct 2, 2014 at 6:19 PM, Stephen Frost sfr...@snowman.net wrote: * Claudio Freire (klaussfre...@gmail.com) wrote: On Thu, Oct 2, 2014 at 6:03 PM, Stephen Frost sfr...@snowman.net wrote: That sounds extremely complex. You'd have to implement the fake columns, foreign keys, indexes, etc

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Dimitri Fontaine
Alvaro Herrera alvhe...@2ndquadrant.com writes: - will the table have to be rewritten? the indexes? Please give my DDL deparsing patch a look. There is a portion there about deparsing ALTER TABLE specifically; what it does is save a list of subcommands, and for each of them we either

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Jan Wieck
On 10/02/2014 01:15 PM, Joe Conway wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/02/2014 11:30 AM, Dimitri Fontaine wrote: Questions: 1. Do you agree that a systematic way to report what a DDL command (or script, or transaction) is going to do on your production database is a

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Joe Conway
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/02/2014 06:43 PM, Jan Wieck wrote: The real question is at what level of information, returned to the user, does this feature become user friendly? It is one thing to provide information of the kind of TAKE ACCECSS EXCLUSIVE LOCK ON

Re: [HACKERS] DDL Damage Assessment

2014-10-02 Thread Jim Nasby
On 10/2/14, 2:43 PM, Josh Berkus wrote: Questions: 1. Do you agree that a systematic way to report what a DDL command (or script, or transaction) is going to do on your production database is a feature we should provide to our growing user base? Yes. +1 2. What do you think