Re: ALTER TABLE ... NOREWRITE option

On 5 December 2012 00:16, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>> Sure, and the DevOps staff would be using the EXPLAIN feature (if we had
>> it). After which they could do little anyway except complain to the ORM
>> authors, who might or might not give a damn. I don't see that there's
>> enough value-added from what you suggest to justify the development
>> time.
>
> You're still thinking of a schema change as a SQL script. ORM-based
> applications usually do not run their schema changes as SQL scripts,
> thus there's nothing to EXPLAIN. Anything which assumes the presense of
> a distict, user-accessible SQL script is going to leave out a large
> class of our users.

And anything which assumes the *absence* of a manual script is also
leaving out a large class of users. ORMs are very important, but not
the only thing we serve.

Please assume that script meant a set of SQL statements that are
executed in a specific sequence to change a database model from one
version to another. Anything which requires editing of all (or worse,
just some) of the SQL statements is not a good solution. For ORMs,
this requires each ORM to make its own change to support that
functionality and to have a separate mode where it is used. For manual
scripts, this requires specific editing, which fails, as already
described. Either way EXPLAIN is bad, since editing/separate modes can
introduce bugs.

I think we need a parameter called

schema_change_reporting = off (default) | on [USERSET]

which displays relevant statistics/reports about the actions taken by
DDL statements. That will also highlight locks and the need to reduce
their lock levels.

That's best used as a function to turn it on and then a function to
produce the report.

> However, as I said, if we had the EXPLAIN ALTER, we could use
> auto-explain to log the ALTER plans (finally, a good use for
> auto-explain). So that's a workable workaround. And EXPLAIN ALTER would
> offer us more flexibility than any logging option, of course.

Auto explain executes things twice, which is not possible for DDL, so
it won't work.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services