On Wed, Jun 11, 2014 at 3:26 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > If we didn't have mechanisms like this, we'd have far worse hazards from > ALTER TABLE than whether the planner made an incorrect join optimization. > Consider ALTER COLUMN TYPE for instance.
Obviously not general cases of ALTER COLUMN TYPE but dropping a NULL constraint seems like the kind of change targeted by Simon's "reduce lock strength" patch that I'm sure he's still interested in. I think that patch, while full of dragons to steer around, is something that will keep coming up again and again in the future. It's a huge operational risk that even these short exclusive locks can cause a huge production outage if they happen to get queued up behind a reporting query. I don't think it changes anything for this patch -- right now the world is arranged the way Tom described -- but it's something to keep in mind when we talk about lock strength reduction and the impact on existing queries. For example if there's an UPDATE query in repeatable read mode that has an IN clause like this and was optimized accordingly then any lock strength reduction patch would have to beware that an ALTER TABLE that dropped the NULL clause might impact the update query. Incidentally, Oracle has a feature for online schema changes that we might end up having to implement something similar. The good news is we have the infrastructure to maybe do it. The idea is to start capturing all the changes to the table using something like our logical changeset extraction. Then do the equivalent of "create newtable as select ... from oldtable" to create the new schema, then start replaying the accumulated changes to the new table. Eventually when the change queue drains then get an exclusive lock, drain any new changes, and swap in the new table with the new schema. -- greg -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers