ALTER TABLE ... NOREWRITE option

Simon Riggs <simon@2ndQuadrant.com> writes:

It's hard to know whether your tables will be locked for long periods
when implementing DDL changes.

The NOREWRITE option would cause an ERROR if the table would be
rewritten by the command.

This would allow testing to highlight long running statements before
code hits production.

I'm not thrilled about inventing YA keyword for this. If you have a
problem with that sort of scenario, why aren't you testing your DDL
on a test server before you do it on production?

Or even more to the point, you can always cancel the statement once
you realize it's taking too long.

Also, I don't really like the idea of exposing syntax knobs for
what ought to be purely an internal optimization. If someday the
optimization becomes unnecessary or radically different in behavior,
you're stuck with dead syntax. Sometimes the knob is sufficiently
important to take that risk, but it doesn't seem to be so here.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 1 December 2012 16:38, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

It's hard to know whether your tables will be locked for long periods
when implementing DDL changes.

The NOREWRITE option would cause an ERROR if the table would be
rewritten by the command.

This would allow testing to highlight long running statements before
code hits production.

I'm not thrilled about inventing YA keyword for this. If you have a
problem with that sort of scenario, why aren't you testing your DDL
on a test server before you do it on production?

That's the point. You run it on a test server first, and you can
conclusively see that it will/will not run for a long time on
production server.

Greg Sabine Mullane wrote an interesting blog about a way of solving
the problem in userspace.

Or even more to the point, you can always cancel the statement once
you realize it's taking too long.

Which means you have to watch it, which is not always possible.

Also, I don't really like the idea of exposing syntax knobs for
what ought to be purely an internal optimization. If someday the
optimization becomes unnecessary or radically different in behavior,
you're stuck with dead syntax. Sometimes the knob is sufficiently
important to take that risk, but it doesn't seem to be so here.

I think it was an interesting idea, but I agree with comments about
weird syntax.

We need something better and more general for impact assessment.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2012-12-01 18:27:08 +0000, Simon Riggs wrote:

On 1 December 2012 16:38, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

It's hard to know whether your tables will be locked for long periods
when implementing DDL changes.

The NOREWRITE option would cause an ERROR if the table would be
rewritten by the command.

This would allow testing to highlight long running statements before
code hits production.

I'm not thrilled about inventing YA keyword for this. If you have a
problem with that sort of scenario, why aren't you testing your DDL
on a test server before you do it on production?

That's the point. You run it on a test server first, and you can
conclusively see that it will/will not run for a long time on
production server.

Greg Sabine Mullane wrote an interesting blog about a way of solving
the problem in userspace.

Or even more to the point, you can always cancel the statement once
you realize it's taking too long.

Which means you have to watch it, which is not always possible.

Also, I don't really like the idea of exposing syntax knobs for
what ought to be purely an internal optimization. If someday the
optimization becomes unnecessary or radically different in behavior,
you're stuck with dead syntax. Sometimes the knob is sufficiently
important to take that risk, but it doesn't seem to be so here.

I think it was an interesting idea, but I agree with comments about
weird syntax.

We need something better and more general for impact assessment.

My first thought is to add more detailed EXPLAIN support for
DDL... Although that unfortunately broadens the scope of this a tiny
bit.

Greetings,

Andres Freund

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

I'm not thrilled about inventing YA keyword for this. If you have a
problem with that sort of scenario, why aren't you testing your DDL
on a test server before you do it on production?

*I* do test my DDL. However, there are literally hundreds of thousands
of Rails, Django and Hibernate developers who "test" their DDL by
running it using a 5-row dataset on their laptops before pushing it to
production. As far as these folks are concerned, "rewrite if there's a
default" is a completely unintuitive booby-trap.

I agree that adding "NOREWRITE" is a bad solution, though. Personally,
I'd rather have a system function which tests whether a series of DDL
statements involves a rewrite anywhere. e.g.:

SELECT pg_test_for_rewrite('ALTER TABLE josh ADD COLUMN haircolor')

Hmmmm. Actually, that wouldn't work with migrations tools, especially
Rails. Better, what about a GUC?

SET message_on_rewrite=WARNING;

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2012-12-01 12:24:57 -0800, Josh Berkus wrote:

I'm not thrilled about inventing YA keyword for this. If you have a
problem with that sort of scenario, why aren't you testing your DDL
on a test server before you do it on production?

*I* do test my DDL. However, there are literally hundreds of thousands
of Rails, Django and Hibernate developers who "test" their DDL by
running it using a 5-row dataset on their laptops before pushing it to
production. As far as these folks are concerned, "rewrite if there's a
default" is a completely unintuitive booby-trap.

I agree that adding "NOREWRITE" is a bad solution, though. Personally,
I'd rather have a system function which tests whether a series of DDL
statements involves a rewrite anywhere. e.g.:

SELECT pg_test_for_rewrite('ALTER TABLE josh ADD COLUMN haircolor')

Hmmmm. Actually, that wouldn't work with migrations tools, especially
Rails. Better, what about a GUC?

SET message_on_rewrite=WARNING;

If you have a framework and you want to test for this you can just
compare relfilenodes before/after.

Greetings,

Andres Freund

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

If you have a framework and you want to test for this you can just
compare relfilenodes before/after.

You're targeting the wrong users. This feature isn't for helping you or
me. It's for helping the Rails users.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2012-12-01 12:35:15 -0800, Josh Berkus wrote:

If you have a framework and you want to test for this you can just
compare relfilenodes before/after.

You're targeting the wrong users. This feature isn't for helping you or
me. It's for helping the Rails users.

I am not targeting anything. All I am saying is that if some framework
(not the framework's users!) wants to build something like that today
its not all that complicated.

Greetings,

Andres Freund

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
owner@postgresql.org] On Behalf Of Andres Freund
Sent: 01 December 2012 21:40
To: Josh Berkus
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] ALTER TABLE ... NOREWRITE option

On 2012-12-01 12:35:15 -0800, Josh Berkus wrote:

If you have a framework and you want to test for this you can just
compare relfilenodes before/after.

You're targeting the wrong users. This feature isn't for helping you
or me. It's for helping the Rails users.

I am not targeting anything. All I am saying is that if some framework

(not the

framework's users!) wants to build something like that today its not all

that

complicated.

I would prefer to be able to detect this *before* the rewrite happens though
when writing tooling.

Regards
Petr Jelinek

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sat, Dec 01, 2012 at 07:34:51PM +0100, Andres Freund wrote:

On 2012-12-01 18:27:08 +0000, Simon Riggs wrote:

On 1 December 2012 16:38, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

It's hard to know whether your tables will be locked for long periods
when implementing DDL changes.

The NOREWRITE option would cause an ERROR if the table would be
rewritten by the command.

This would allow testing to highlight long running statements before
code hits production.

I'm not thrilled about inventing YA keyword for this. If you have a
problem with that sort of scenario, why aren't you testing your DDL
on a test server before you do it on production?

That's the point. You run it on a test server first, and you can
conclusively see that it will/will not run for a long time on
production server.

Acquiring the lock could still take an unpredictable amount of time.

Greg Sabine Mullane wrote an interesting blog about a way of solving
the problem in userspace.

I currently recommend using the DEBUG1 messages for this purpose:

[local] test=# set client_min_messages = debug1;
SET
[local] test=# create table t (c int8 primary key, c1 text);
DEBUG: building index "pg_toast_109381_index" on table "pg_toast_109381"
DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"
DEBUG: building index "t_pkey" on table "t"
CREATE TABLE
[local] test=# alter table t alter c type int4;
DEBUG: building index "pg_toast_109391_index" on table "pg_toast_109391"
DEBUG: rewriting table "t"
DEBUG: building index "t_pkey" on table "t"
ALTER TABLE
[local] test=# alter table t alter c type oid;
DEBUG: building index "t_pkey" on table "t"
ALTER TABLE

Observe that some changes rewrite the table and all indexes, while others skip
rewriting the table but rebuild one or more indexes. I've threatened to
optimize type changes like (base type) -> (domain with CHECK constraint) by
merely scanning the table for violations. If we do add syntax such as you
have proposed, I recommend using a different name and defining it to reject
any operation with complexity O(n) or worse relative to table size. That
being said, I share Tom's doubts. The DEBUG1 messages are a sorry excuse for
a UI, but I'm not seeing a clear improvement in NOREWRITE.

Or even more to the point, you can always cancel the statement once
you realize it's taking too long.

Which means you have to watch it, which is not always possible.

There's statement_timeout.

My first thought is to add more detailed EXPLAIN support for
DDL... Although that unfortunately broadens the scope of this a tiny
bit.

That would be ideal.

Thanks,
nm

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Noah Misch <noah@leadboat.com> writes:

Acquiring the lock could still take an unpredictable amount of time.

I think there's a new GUC brewing about setting the lock timeout
separately from the statement timeout, right?

being said, I share Tom's doubts. The DEBUG1 messages are a sorry excuse for
a UI, but I'm not seeing a clear improvement in NOREWRITE.

EXPLAIN ALTER TABLE would be the next step I guess. I discovered plenty
of magic tricks when working on the rewriting support for that command,
and I think exposing them in the EXPLAIN output would go a long way
towards reducing some POLA violations.

Ideally the EXPLAIN command would include names of new objects created
by the command, such as constraints and indexes.

My first thought is to add more detailed EXPLAIN support for
DDL... Although that unfortunately broadens the scope of this a tiny
bit.

That would be ideal.

+1

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

EXPLAIN ALTER TABLE would be the next step I guess. I discovered plenty
of magic tricks when working on the rewriting support for that command,
and I think exposing them in the EXPLAIN output would go a long way
towards reducing some POLA violations.

I think this would be cool on its own merits.

However, for a very large user group -- users with ORMs which do their
own DDL migrations -- we could also use a way to log or WARN for table
rewrites. Since the ORMs are not gonna do an EXPLAIN.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Josh Berkus <josh@agliodbs.com> writes:

EXPLAIN ALTER TABLE would be the next step I guess. I discovered plenty
of magic tricks when working on the rewriting support for that command,
and I think exposing them in the EXPLAIN output would go a long way
towards reducing some POLA violations.

I think this would be cool on its own merits.

However, for a very large user group -- users with ORMs which do their
own DDL migrations -- we could also use a way to log or WARN for table
rewrites. Since the ORMs are not gonna do an EXPLAIN.

An ORM is also quite unlikely to pay attention to a warning, much less a
postmaster log entry ... so your argument seems unfounded from here.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Dec 03, 2012 at 11:37:17AM +0100, Dimitri Fontaine wrote:

Noah Misch <noah@leadboat.com> writes:

Acquiring the lock could still take an unpredictable amount of time.

I think there's a new GUC brewing about setting the lock timeout
separately from the statement timeout, right?

Yes.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/02/2012 03:07 AM, Noah Misch wrote:

On Sat, Dec 01, 2012 at 07:34:51PM +0100, Andres Freund wrote:

On 2012-12-01 18:27:08 +0000, Simon Riggs wrote:

On 1 December 2012 16:38, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

It's hard to know whether your tables will be locked for long periods
when implementing DDL changes.
The NOREWRITE option would cause an ERROR if the table would be
rewritten by the command.
This would allow testing to highlight long running statements before
code hits production.

I'm not thrilled about inventing YA keyword for this. If you have a
problem with that sort of scenario, why aren't you testing your DDL
on a test server before you do it on production?

That's the point. You run it on a test server first, and you can
conclusively see that it will/will not run for a long time on
production server.

Acquiring the lock could still take an unpredictable amount of time.

Greg Sabine Mullane wrote an interesting blog about a way of solving
the problem in userspace.

I currently recommend using the DEBUG1 messages for this purpose:

[local] test=# set client_min_messages = debug1;
SET
[local] test=# create table t (c int8 primary key, c1 text);
DEBUG: building index "pg_toast_109381_index" on table "pg_toast_109381"
DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"
DEBUG: building index "t_pkey" on table "t"
CREATE TABLE
[local] test=# alter table t alter c type int4;
DEBUG: building index "pg_toast_109391_index" on table "pg_toast_109391"
DEBUG: rewriting table "t"
DEBUG: building index "t_pkey" on table "t"
ALTER TABLE
[local] test=# alter table t alter c type oid;
DEBUG: building index "t_pkey" on table "t"
ALTER TABLE

Observe that some changes rewrite the table and all indexes, while others skip
rewriting the table but rebuild one or more indexes. I've threatened to
optimize type changes like (base type) -> (domain with CHECK constraint) by
merely scanning the table for violations. If we do add syntax such as you
have proposed, I recommend using a different name and defining it to reject
any operation with complexity O(n) or worse relative to table size. That
being said, I share Tom's doubts. The DEBUG1 messages are a sorry excuse for
a UI, but I'm not seeing a clear improvement in NOREWRITE.

Or even more to the point, you can always cancel the statement once
you realize it's taking too long.

Which means you have to watch it, which is not always possible.

There's statement_timeout.

I think the point was in catching the rewrite behaviour in testing.

for statement_timeout to kick in you may need to have both
production size and production load which is not always easy
to achieve in testing.

My first thought is to add more detailed EXPLAIN support for
DDL... Although that unfortunately broadens the scope of this a tiny
bit.

That would be ideal.

It would also be nice to add a "dry run" support, which switches to EXPLAIN
for all SQL instead of executing.

SET explain_only TO TRUE;

----------------
Hannu

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

However, for a very large user group -- users with ORMs which do their
own DDL migrations -- we could also use a way to log or WARN for table
rewrites. Since the ORMs are not gonna do an EXPLAIN.

An ORM is also quite unlikely to pay attention to a warning, much less a
postmaster log entry ... so your argument seems unfounded from here.

But the DevOps staff *is*.

There's the workflow:

1. developer writes migrations in Rails/Django/Hibernate
2. DevOps staff tests migrations on test machine.
3. DevOps staff looks at logs for rewrites.

The problem with an EXPLAIN path is that you're requiring the developers
to extract the DDL generated by Rails or whatever from the migration,
something to which the framework is not very friendly. So we need a
path for identifying rewrites which doesn't require extracting DDL in
SQL form.

EXCEPT: I just realized, if we have "explain ALTER" then we can just log
explains, no? In which case, problem solved.

Come to think of it, it would be good to warn about ACCESS EXCLUSIVE
locks as well. That's another big booby trap for developers.

Note that "writing stuff to the log" is far from an ideal solution for
this user base. I think they'd rather have "ERROR on REWRITE". It
would be good to have one of the Heroku folks speak up here ...

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Josh Berkus <josh@agliodbs.com> writes:

However, for a very large user group -- users with ORMs which do their
own DDL migrations -- we could also use a way to log or WARN for table
rewrites. Since the ORMs are not gonna do an EXPLAIN.

An ORM is also quite unlikely to pay attention to a warning, much less a
postmaster log entry ... so your argument seems unfounded from here.

But the DevOps staff *is*.

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.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 4 December 2012 22:30, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Josh Berkus <josh@agliodbs.com> writes:

However, for a very large user group -- users with ORMs which do their
own DDL migrations -- we could also use a way to log or WARN for table
rewrites. Since the ORMs are not gonna do an EXPLAIN.

An ORM is also quite unlikely to pay attention to a warning, much less a
postmaster log entry ... so your argument seems unfounded from here.

But the DevOps staff *is*.

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.

I've never had my POLA violated, so its hard to comment on so many buzzphrases.

But normal people I've worked with do struggle with knowing for
certain what will happen when they run a DDL change script on a
production system, which is the heart of the problem. Other software
lags behind the services we provide, but if we never provide it they
definitely will never use it.

Eyeballs work well, but something automated else would work even better.

Adding EXPLAIN in front of everything doesn't work at all because many
actions depend upon the results of earlier actions, so the test must
actually perform the action in order for the whole script to work.
Plus, if we have to edit a script to add EXPLAIN in front of
everything, you can't put it live without editing out the EXPLAINs,
which leaves you open to the failure caused by editing immediately
prior to go live.

So my NOREWRITE option fails those criteria and should be discarded.

On reflection, what is needed is a way to ask "what just happened"
when a script is tested. The best way to do that is a special stats
collection mode that can be enabled just for that run and then the
stats analyzed afterwards. Noah's contribution comes closest to what
we need, but its not complete enough, yet.

I'll have a play with this idea some more.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

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.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 5 December 2012 00:16, Josh Berkus <josh@agliodbs.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers