> 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers