On Wed, Dec 29, 2010 at 11:14:37PM -0500, Robert Haas wrote: > On Wed, Dec 29, 2010 at 6:46 PM, Noah Misch <n...@leadboat.com> wrote: > > Perhaps. ?A few kooky rows is indeed common, but we're talking about a > > specific > > breed of kookiness: 99.9% of the rows have identical bits after an ALTER > > TYPE > > transformation expression, and 0.1% have different bits. ?Is that common? > > I think it's common enough to be worth worrying about.
Okay. Could you give an example of a specific ALTER TABLE recipe worth worrying about and subject to degradation under my proposal? > > Adding a bpchar into the mix makes a negative verification scan possible, as > > does a USING clause having a truncating effect. ?Continuing the example, > > these > > can and would get a negative verification scan: > > ALTER TABLE t ALTER c TYPE character(6); > > ALTER TABLE> In case it was not obvious, I'll note that any error thrown by > > a transformation > t ALTER c TYPE varchar(5) USING c::varchar(5); > > Plenty of academic USING clause examples exist: > > ALTER TABLE t ALTER c TYPE varchar(8) USING CASE c WHEN '<de/>' THEN 'foo' > > ELSE c END; > > I am not really convinced that there's much value in optimizing these > cases. They're not likely to arise very often in practice, Just to make sure we're clear: those were examples of what I had intended to pessimize for the sake of simplicity. > and DBAs > like predictability. There's tangible value in being able to say > "this is going to scan your table at most once - it might rewrite it, > or it might just verify what's there, or it might decide no scan is > necessary, but the absolute worst case is one scan with rewrite". > That's simple to understand and simple to document and probably > simpler to code too, and I think it covers very nearly all of the > cases people are likely to care about in practice. > > 1. Have the user tell us: ALTER TABLE t ALTER c TYPE xml USING c::xml > > IMPLICIT > > 2. Mark the text->xml cast as "possibly no-rewrite" and look for that > > 3. Do a verification scan every time > > I think for any pair of types (T1, T2) we should first determine > whether we can skip the scan altogether. If yes, we're done. If no, > then we should have a way of determining whether a verify-only scan is > guaranteed to be sufficient (in your terminology, the verification > scan is guaranteed to return either positive or error, not negative). > If yes, then we do a verification scan. If no, we do a rewrite. How would we answer the second question in general? 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
