Re: DDL Damage Assessment

> 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 others, I think EXPLAIN is a good way to do this without adding
a keyword. So you'd do:

EXPLAIN
ALTER TABLE ....

... and it would produce a bunch of actions, available in either text or
JSON formats. For example:

{ locks : [ { lock_type: relation,
relation: table1,
lock type: ACCESS EXCLUSIVE },
{ lock_type: transaction },
{ lock_type: catalog,
catalogs: [pg_class, pg_attribute, pg_statistic],
lock_type: EXCLUSIVE } ]
}
{ writes : [
{ object: relation files,
action: rewrite },
{ object: catalogs
action: update }
]

... etc. Would need a lot of refinement, but you get the idea.

> 3. Does it make sense to support the whole set of DDL commands from the
> get go (or ever) when most of them are only taking locks in their
> own pg_catalog entry anyway?

Well, eventually we'd want to support all of them just to avoid having
things be wierd for users. However, here's a priority order:

ALTER TABLE
CREATE TABLE
DROP TABLE
ALTER VIEW
CREATE VIEW
CREATE INDEX
DROP INDEX

... since all of the above can have unexpected secondary effects on
locking. For example, if you create a table with FKs it will take an
ACCESS EXCLUSIVE lock on the FK targets. And if you DROP a partition,
it takes an A.E. lock on the parent table.

> Provided that we are able to converge towards a common enough answer to
> those questions, I propose to hack my way around and send patches to
> have it (the common answer) available in the next PostgreSQL release.

Great!

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