Re: ALTER TABLE ... NOREWRITE option

Simon,

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

Yes. In the long run, we'll probably need two solutions. An
interactive EXPLAIN, and something which logs or aborts for the ORM folks.

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

Exactly. And only the ORMs which are very close to PostgreSQL would be
willing to do this. Most would not.

> I think we need a parameter called
>
> schema_change_reporting = off (default) | on [USERSET]

The problem with anything which reports back to the session is that even
when DBAs are running SQL scripts, migrations are seldom run in an
interactive session. For example, I manage all migrations for large
projects using Python and YAML files, and SQLitch uses Perl and JSON
wrappers for the SQL. Doing migrations via "psql -f filename -q" is
also very common. So anything reported back in an interactive session
would be lost.

That's why we need a mechanism which either logs, or aborts on specific
actions. From the perspective of the DevOps staff, abort is possibly
the better option, but there may be issues with it on our end. That was
the attraction of the original NOREWRITE patch, although as I said that
suffers from new keywords and a total lack of extensibility.

What about adding something like:

ddl_action = [ none, log, warn, abort ]
ddl_events = [ all, rewrite, exclusive, access_exclusive ]

I realize I'm getting out into the weeds here, but I'm thinking "as a
contract DBA, what would *really* help me?" and something like the above
would do it. This would allow me to do something like:

"I wanna test this Rails migration, and have it die if it tries to do a
full table rewrite or take an access_exclusive lock. And I'll check the
logs afterwards if it blows up."

ddl_action = 'log,abort'
ddl_events = 'rewrite,access_exclusive'

This would make it very easy to set some rules for the organization, and
enforce them with automated testing.

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

I keep trying to find a use for auto-explain.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com