Thread: Imperfect solutions
I was remembering tonight some of the strange fixes we made in the early days of PostgreSQL. I particularly remember the LIKE optimization I did in gram.y to allow queries that are anchored to the beginning of a string to use an index. It was a crazy patch, and everyone who saw it grumbled. The problem was that no one could think of a better solution. Finally the proper fix was made to the optimizer and the code removed. I was glad to see it go, if only so I didn't have to hear complaints about it. :-) The good thing about the patch is that it gave us a feature for two years while we gained experience to make the right fix, and it was easy to remove once the time came. I am now wondering if we are agonizing too much about changes to PostgreSQL. We are much more successful, and much more reliable than we used to be, but I wonder whether we are limiting improvements because they are not the _right_ fix. I am not advocating that we start throwing all sorts of stuff into the backend. This certainly would leave us with a big mess. I am just noticing that we are hitting road blocks where we can't find the perfect solution, so we do nothing, even when users are complaining they need a certain feature. I think we can all remember recent discussions or TODO items where this happened. Seem like it would be a good idea sometimes add feature, even an imperfect one, until we can make a better fix, because sometimes, the perfect fix is years away. I never expected my gram.y hack to last as long as it did. Let me make a suggestion. Next time we have a partial fix for something, but don't want to add it, let's add the item on the TODO list under the heading "Imperfect Fixes," where we list items we have fixed but need more work. This way, we will be able to give users a feature, but we will not forget to revisit the item and make a _perfect_ fix later. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Let me make a suggestion. Next time we have a partial fix for > something, but don't want to add it, let's add the item on the TODO list > under the heading "Imperfect Fixes," where we list items we have fixed > but need more work. This way, we will be able to give users a feature, > but we will not forget to revisit the item and make a _perfect_ fix > later. The first thing you should add to that list is 'Inheritance of constraints'. At the moment myself and Stephan are beavering away making it so that constraints are recusively added and removed - however if ever we make a pg_constraints catalog, and a one-to-many constraint->table mapping catalog, all our code will need to be (minimally) changed. Also, what about foreign keys? At the moment it is incredibly complicated to determine all the foreign keys on a table, what column(s) they're defined over, what column(s) they reference and what their behaviour is. And just try writing code (like I am) that tries to drop them by name, let alone list them!!! Lastly - pg_dump can happily dump foreign keys as raw triggers, but the perfect solution (methinks) would be to dump them as alter table add constraints. Makes it easier to move between different database products. My 2c. Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Also, what about foreign keys? At the moment it is incredibly complicated
> to determine all the foreign keys on a table, what column(s) they're defined
> over, what column(s) they reference and what their behaviour is. And just
> try writing code (like I am) that tries to drop them by name, let alone list
> them!!!
Indeed. You're looking at the aftermath of an "imperfect fix" to add
foreign keys. With all due respect to Jan and Stephan, who did a great
job adding the feature at all, there are still a lot of things that need
to be fixed in that area. The trouble with imperfect fixes is that they
tend to get institutionalized if they're left in the code for any length
of time --- people write more code that depends on the hack, or works
around some of its shortcomings, or whatever, and so it gets harder and
harder to rip out the hack and replace it with something better.
Especially if the original author moves on to other challenges instead
of continuing to work on improving his first try. Other people are
likely to have less understanding of the code's shortcomings.
I don't object to imperfect fixes when they buy us a useful amount of
functionality in a critical area (as indeed the current foreign-key code
does). But I have more of a problem with doing things that way for
marginal feature additions. I think that in the long run the downside
outweighs the upside in cases like that.
regards, tom lane
> I don't object to imperfect fixes when they buy us a useful amount of > functionality in a critical area (as indeed the current foreign-key code > does). But I have more of a problem with doing things that way for > marginal feature additions. I think that in the long run the downside > outweighs the upside in cases like that. What got me thinking about this is that I don't think my gram.y fix would be accepted given the current review process, and that is bad because we would have to live with no LIKE optimization for 1-2 years until we learned how to do it right. I think there are a few rules we can use to decide how to deal with imperfect solutions: Are the fixes easy to add _and_ easy to rip out later?Do the fixes affect all queries, or only queries that use the feature?Dothe fixes adversely affect any older queries?Do the fixes make the system more unstable? Foreign key is a good example of a fix that is hard to rip out. My gram.y fix is an example of a fix that affects all queries. Fixes that cause older queries or dumps to fail affect all users. I don't think we have accepted fixes that adversely affect older queries or make the system unstable because they are just too much trouble. Let's look at the %TYPE fix as an example. It is easy to add and easy to rip out. It doesn't affect all queries, just queries that use the feature. It doesn't affect older queries. I think the only argument against it is that it makes the system appear more unstable because people may think that %TYPE is tracking table changes. I am slightly concerned we are waiting for perfect solutions and overlooking useful solutions. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> > Let me make a suggestion. Next time we have a partial fix for > > something, but don't want to add it, let's add the item on the TODO list > > under the heading "Imperfect Fixes," where we list items we have fixed > > but need more work. This way, we will be able to give users a feature, > > but we will not forget to revisit the item and make a _perfect_ fix > > later. > > The first thing you should add to that list is 'Inheritance of constraints'. > At the moment myself and Stephan are beavering away making it so that > constraints are recusively added and removed - however if ever we make a > pg_constraints catalog, and a one-to-many constraint->table mapping catalog, > all our code will need to be (minimally) changed. > > Also, what about foreign keys? At the moment it is incredibly complicated > to determine all the foreign keys on a table, what column(s) they're defined > over, what column(s) they reference and what their behaviour is. And just > try writing code (like I am) that tries to drop them by name, let alone list > them!!! > > Lastly - pg_dump can happily dump foreign keys as raw triggers, but the > perfect solution (methinks) would be to dump them as alter table add > constraints. Makes it easier to move between different database products. I already have on the TODO list: * Make constraints clearer in dump file In fact I have a whole referential integrity section of the TODO list. Please let me know what needs to be added. Let me add: * Make foreign keys easier to identify -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> What got me thinking about this is that I don't think my gram.y fix
> would be accepted given the current review process,
Not to put too fine a point on it: the project has advanced a long way
since you did that code. Our standards *should* be higher than they
were then.
> and that is bad
> because we would have to live with no LIKE optimization for 1-2 years
> until we learned how to do it right.
We still haven't learned how to do it right, actually. I think the
history of the LIKE indexing problem is a perfect example of why fixes
that work for some people but not others don't survive long. We put out
several attempts at making it work reliably in non-ASCII locales, but
none of them have withstood the test of actual usage.
> I think there are a few rules we can use to decide how to deal with
> imperfect solutions:
You forgot
* will the fix institutionalize user-visible behavior that will in the long run be considered the wrong thing?
* will the fix contort new code that is written in the same vicinity, thereby making it harder and harder to replace as
timegoes on?
The first of these is the core of my concern about %TYPE.
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > > What got me thinking about this is that I don't think my gram.y fix > > would be accepted given the current review process, > > Not to put too fine a point on it: the project has advanced a long way > since you did that code. Our standards *should* be higher than they > were then. Yes, agreed. But at the time that was the best we could do. My question is whether we should be less willing to accept partial fixes now than in the past. Probably yes, but have we gone too far? Look at some of the imperfect solutions we have rejected recently, all from the TODO list: * Improve control over user privileges, including table creation and lock use [privileges] (Karel, others) * Remove unused files during database vacuum or postmaster startup * Add table name mapping for numeric file names * Add ALTER TABLE DROP COLUMN feature [drop] * Cache most recent query plan(s) (Karel) [prepare] Now that I look at it, the list is pretty short, so we may be fine. > > and that is bad > > because we would have to live with no LIKE optimization for 1-2 years > > until we learned how to do it right. > > We still haven't learned how to do it right, actually. I think the > history of the LIKE indexing problem is a perfect example of why fixes > that work for some people but not others don't survive long. We put out > several attempts at making it work reliably in non-ASCII locales, but > none of them have withstood the test of actual usage. Agreed. But what options do we have? If we do nothing, there is no optimization at all. > > I think there are a few rules we can use to decide how to deal with > > imperfect solutions: > > You forgot > > * will the fix institutionalize user-visible behavior that will in the > long run be considered the wrong thing? Yes, good point. User-visible changes are a big deal and have to be studied carefully. > * will the fix contort new code that is written in the same vicinity, > thereby making it harder and harder to replace as time goes on? Again, a good point, related to rip-out-ability. > The first of these is the core of my concern about %TYPE. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Thu, 31 May 2001, Tom Lane wrote: > Indeed. You're looking at the aftermath of an "imperfect fix" to add > foreign keys. With all due respect to Jan and Stephan, who did a great > job adding the feature at all, there are still a lot of things that need > to be fixed in that area. The trouble with imperfect fixes is that they Ugh yes. Actually all of the constraints seem to have this problem to some degree. Unique doesn't quite work right for updates where rows may "temporarily" be of the same value, check constraints using user functions can be violated if those functions do sql statements and column renames cause dump/restore to fail. Fk has at least the following (in no order and probably incomplete due to just waking up): Temp tables can shadow pk/fk tables- If we have schemas and temp tables are in their own, we can probably fix this justwith fully qualifying.- Otherwise, we'd probably want to refer to the table by oid, but that would require having someway to do that in SPI or to replace the SPI calls. (Getting the name from the oid isn't sufficient, obviously) Inheritance- Plenty of discussion about this already- An additional wrinkle comes in if we allow/are going to allow users to rename base table columns in inherited tables. Alter Table Rename- Either we need to store oids or follow name changes. I'd actually prefer the latter if possible, butthat requires a dependency system. (Especially if we were to go with only storing the text of check constraints.) General- For update locks are too strong? Do we need a self conflicting lock on the pk table rows? Is there some generallybetter way to handle this? How does this tie into the problem Jan noted before?- We probably need a way to checkthe entire table at once rather than per row checks. This would make alter table more reasonable for dump/restore(right now on large tables it would try each row's check separately - ugh)- Deferred constraints are brokenin a few cases. Update/insert trigger on fk needs to make sure the row is still there at check time, no actiontrigger needs to make sure there hasn't been another row with the key values inserted. Other actions are questionable,has anyone actually figured out what the correct behavior is? I think that running actual actions immediatelymay be the correct thing, but in any case, they'd probably need checks like the no action trigger (what happensif the delete/insert is done within one statement due to triggers or whatever)- Match partial - Complicated. Todo this completely means almost a separate implementation since stuff like the above checks wouldn't work in this caseand means that we have to recognize things where the user has updated two pk rows referenced by a single fk row to distinct key values, since that's an error condition. Storage/Reporting- We really need something that stores the fk information better than what we have (we may want to seeif we can generalize more constraints into the system as well, but we'd have to see)- We'll want to make dump/restoresshow the constraint in a better fashion. This may need the above, and we'd still need to have backwardcompatibility (one of the reasons switching to storing oids would be interesting)
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thursday 31 May 2001 10:07, Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > We still haven't learned how to do it right, actually. I think the > history of the LIKE indexing problem is a perfect example of why fixes > that work for some people but not others don't survive long. We put out > several attempts at making it work reliably in non-ASCII locales, but > none of them have withstood the test of actual usage. While this subject is fresh, let me ask the obvious questions: 1.) What locales do we know are problematic? 2.) What will happen to user queries and data in those locales? 3.) What has been fixed for this (last I remember there was an index corruption issue, and multiple collation problems)? The 7.1 HISTORY has the blanket statement 'Many multi-byte/Unicode/locale fixes (Tatsuo and others)' instead of a list of the actual bugs fixed. Looking through the archives Ifind some details, such as the function locale_is_like_safe() , and I see other details -- but a concise picture of what one can expect operating in a non-locale_is_like_safe() (which currently includes ONLY the C and POSIX locales) locale would be, IMHO, useful information that people wouldn't have to dredge around for -- and should probably go into the current locale docs under the Problems heading. - -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7FnLa5kGGI8vV9eERAhaaAKDQjz0l+3JWnEv4Gc6HDvKFWjIXnQCdE3V7 XdWmIpkzQ8syjU7KrkzEwcM= =mZ7Q -----END PGP SIGNATURE-----
Lamar Owen <lamar.owen@wgcr.org> writes:
> Looking through the archives Ifind some details, such as the function
> locale_is_like_safe() , and I see other details -- but a concise picture of
> what one can expect operating in a non-locale_is_like_safe() (which
> currently includes ONLY the C and POSIX locales) locale would be,
As of 7.1, LIKE will always work correctly in non-C locales, because it
will never try to use an index. Concise enough?
What we need, and don't have, is reliable information about which
locales the pre-7.1 indexing hack was actually safe in. A complicating
factor is that non-C locale definitions are probably platform-specific.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thursday 31 May 2001 12:50, Tom Lane wrote: > As of 7.1, LIKE will always work correctly in non-C locales, because it > will never try to use an index. Concise enough? Yes, thank you. - -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7Fnzq5kGGI8vV9eERAgPJAJ9eHNedUAS4VTHkjwbg3oxt9c8cCACeMmEH HQPugYw+AZbD1v6cd2dycN4= =zCvc -----END PGP SIGNATURE-----
On Thu, May 31, 2001 at 10:07:36AM -0400, Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > What got me thinking about this is that I don't think my gram.y fix > > would be accepted given the current review process, > > Not to put too fine a point on it: the project has advanced a long way > since you did that code. Our standards *should* be higher than they > were then. > > > and that is bad > > because we would have to live with no LIKE optimization for 1-2 years > > until we learned how to do it right. > > We still haven't learned how to do it right, actually. I think the > history of the LIKE indexing problem is a perfect example of why fixes > that work for some people but not others don't survive long. We put out > several attempts at making it work reliably in non-ASCII locales, but > none of them have withstood the test of actual usage. > > > I think there are a few rules we can use to decide how to deal with > > imperfect solutions: > > You forgot > > * will the fix institutionalize user-visible behavior that will in the > long run be considered the wrong thing? > > * will the fix contort new code that is written in the same vicinity, > thereby making it harder and harder to replace as time goes on? > > The first of these is the core of my concern about %TYPE. This list points up a problem that needs a better solution than a list: you have to put in questionable features now to get the usage experience you need to do it right later. The set of prospective features that meet that description does not resemble the set that would pass all the criteria in the list. This is really a familiar problem, with a familiar solution. When a feature is added that is "wrong", make sure it's "marked" somehow -- at worst, in the documentation, but ideally with a NOTICE or something when it's used -- as experimental. If anybody complains later that when you ripped it out and redid it correctly, you broke his code, you can just laugh, and add, if you're feeling charitable, "experimental features are not to be depended on". -- Nathan Myers ncm@zembu.com
> > I think there are a few rules we can use to decide how to deal with > > imperfect solutions: > > You forgot > > * will the fix institutionalize user-visible behavior that will in the > long run be considered the wrong thing? > > * will the fix contort new code that is written in the same vicinity, > thereby making it harder and harder to replace as time goes on? > > The first of these is the core of my concern about %TYPE. I was thinking about this. Seems if we want to emulate Oracle, we have to make %TYPE visible the way it is implemented in the patch. We can make it track table changes or not, but it doesn't seem we have much latitude in how we make it visible to users. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote: > > > > I think there are a few rules we can use to decide how to deal with > > > imperfect solutions: > > > > You forgot > > > > * will the fix institutionalize user-visible behavior that will in the > > long run be considered the wrong thing? > > > > * will the fix contort new code that is written in the same vicinity, > > thereby making it harder and harder to replace as time goes on? > > > > The first of these is the core of my concern about %TYPE. > > I was thinking about this. Seems if we want to emulate Oracle, we have > to make %TYPE visible the way it is implemented in the patch. We can > make it track table changes or not, but it doesn't seem we have much > latitude in how we make it visible to users. I think Tom's argument was that just making it visisble will tie us up to also keep the semantics, which will be subtly different in PostgreSQL and Oracle and which can't be exactly emulated without emulating _everything_ in Oracle and thereby throwing away unique strengths of PostgreSQL. Fortunately I've not heard very much support for making empty string and NULL to be the same ;) ----------------- Hannu
Hi Bruce, I was just looking at the TODO list and noticed my name in it - cool! (You spelled it wrong - but hey :) ) Just thought you might like to add * ALTER TABLE ADD PRIMARY KEY * ALTER TABLE ADD UNIQUE I thought they were there before, but they're not there any more. I am currently about 90% finished on a patch that will add the functionality listed above. Chris
On Tue, Jun 05, 2001 at 04:16:06PM +0800, Christopher Kings-Lynne wrote:
> Hi Bruce,
>
> I was just looking at the TODO list and noticed my name in it - cool! (You
> spelled it wrong - but hey :) )
>
> Just thought you might like to add
>
> * ALTER TABLE ADD PRIMARY KEY
> * ALTER TABLE ADD UNIQUE
And what
ALTER TABLE DROP PRIMARY KEY ALTER TABLE DROP UNIQUE
BTW, it's a little cosmetic feature if we have CREATE/DROP INDEX :-)
Karel
-- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz
> > Just thought you might like to add > > > > * ALTER TABLE ADD PRIMARY KEY > > * ALTER TABLE ADD UNIQUE > > And what > > ALTER TABLE DROP PRIMARY KEY > ALTER TABLE DROP UNIQUE > > BTW, it's a little cosmetic feature if we have CREATE/DROP INDEX :-) Those two points are already mentioned - I have another 90% patch ready to go that will add that functionality as well... Chris
On Tue, 5 Jun 2001, Christopher Kings-Lynne wrote: > > > Just thought you might like to add > > > > > > * ALTER TABLE ADD PRIMARY KEY > > > * ALTER TABLE ADD UNIQUE > > > > And what > > > > ALTER TABLE DROP PRIMARY KEY > > ALTER TABLE DROP UNIQUE > > > > BTW, it's a little cosmetic feature if we have CREATE/DROP INDEX :-) > > Those two points are already mentioned - I have another 90% patch ready to > go that will add that functionality as well... As a question, are you doing anything to handle dropping referenced unique constraints or are we just waiting on that until a referencing system is built?
> > Those two points are already mentioned - I have another 90% > patch ready to > > go that will add that functionality as well... > > As a question, are you doing anything to handle dropping referenced unique > constraints or are we just waiting on that until a referencing system > is built? By that do you mean: what happens when you drop a primary key that is referenced by a foreign key? My answer: Forgot about that ;) I'll see what I can do but anytime investigation of foreign keys is required it's a real pain. Foreign keys are kinda next on my list for work, so I might look at it then if it's too difficult right now. (I've got a query that can find all foreign keys on a relation, and what they relate to, that I'm going to add to psql). My other questions then are: Does anything else (other than fk's) ever reference a primary key? What can reference a unique key? Chris
On Wed, 6 Jun 2001, Christopher Kings-Lynne wrote: > > > Those two points are already mentioned - I have another 90% > > patch ready to > > > go that will add that functionality as well... > > > > As a question, are you doing anything to handle dropping referenced unique > > constraints or are we just waiting on that until a referencing system > > is built? > > By that do you mean: what happens when you drop a primary key that is > referenced by a foreign key? > > My answer: Forgot about that ;) I'll see what I can do but anytime > investigation of foreign keys is required it's a real pain. Foreign keys > are kinda next on my list for work, so I might look at it then if it's too > difficult right now. (I've got a query that can find all foreign keys on a > relation, and what they relate to, that I'm going to add to psql). I wouldn't worry all that much about it since you could still break it with drop index, but I wanted to know if you'd done anything with it and if so how general it was. How'd you do the splitting of the arguments to get the columns referenced? That was the biggest problem I was having, trying to get the bytea split up. (Well, without writing a function to do it for me) > My other questions then are: > > Does anything else (other than fk's) ever reference a primary key? > What can reference a unique key? Foreign keys are the only one I know of, but they can reference either.
> (I've got a query that can find all
> foreign keys on a
> > relation, and what they relate to, that I'm going to add to psql).
>
> How'd you do the splitting of the arguments to get the columns referenced?
> That was the biggest problem I was having, trying to get the bytea split
> up. (Well, without writing a function to do it for me)
My original functionality for showing foreign keys was implemented in PHP,
so all I had to do was go:
$tgargs = explode('\000', $row['tgargs']);
It's going to be harder to do that in C I guess...
Chris
> Hi Bruce, > > I was just looking at the TODO list and noticed my name in it - cool! (You > spelled it wrong - but hey :) ) > > Just thought you might like to add > > * ALTER TABLE ADD PRIMARY KEY > * ALTER TABLE ADD UNIQUE > > I thought they were there before, but they're not there any more. I am > currently about 90% finished on a patch that will add the functionality > listed above. Added, name fixed. Thanks. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026