Matthias van de Meent <boekewurm+postg...@gmail.com> writes: > I don't quite get what the hard problem is that we haven't already > solved for other systems: > We already can add additional constraints to domains (e.g. VALUE::int > <> 4), which (according to docs) scan existing data columns for > violations.
That's "solved" only for rather small values of "solved". While the code does try to look for violations of the new constraint, there is no interlock against race conditions (ie, concurrent insertions of a conflicting value). It doesn't check temp tables belonging to other backends, because it can't. And IIRC it doesn't look for instances in metadata, such as stored views. The reason we've considered that Good Enough(TM) for domain constraints is that if something does sneak through those loopholes, nothing terribly surprising happens. You've got a value there that shouldn't be there, but that's mostly your own fault, and the system continues to behave sanely. Also you don't have any problem introspecting what you've got, because such values will still print normally. Plus, we can't really guarantee that users won't get into such a state in other ways, for example if their constraint isn't really immutable. The problem with dropping an enum value is that surprising things might very well happen, because removal of the pg_enum row risks comparisons failing if they involve the dropped value. Thus for example you might find yourself with a broken index that fails all insertion and search attempts, even if you'd carefully removed every user-visible instance of the doomed value: an instance of it high up in the index tree will break most index accesses. Even for values in user-visible places like views, the fact that enum_out will fail doesn't make it any easier to figure out what is wrong. We might be able to get to a place where the surprise factor is low enough to tolerate, but the domain-constraint precedent isn't good enough for that IMO. Andrew's idea of DISABLE rather than full DROP is one way of ameliorating these problems: comparisons would still work, and we can still print a value that perhaps shouldn't have been there. But it's not without other problems. > We already drop columns without rewriting the table to > remove the column's data, and reject new data insertions for those > still-in-the-catalogs-but-inaccessible columns. Those cases don't seem to have a lot of connection to the enum problem. > The only real issue that I can think of is making sure that concurrent > backends don't modify this data, but that shouldn't be very different > from the other locks we already have to take in e.g. ALTER TYPE ... > DROP ATTRIBUTE. I'd bet a good deal of money that those cases aren't too bulletproof. We do not lock types simply because somebody has a value of the type in flight somewhere in a query, and the cost of doing so would be quite discouraging I fear. On the whole, I'd rather accept the idea that the DROP might not be completely watertight; but then we have to work out the details of coping with orphaned values in an acceptable way. regards, tom lane