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, Pennsylvania 19026
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?
Do the 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, Pennsylvania 19026Let 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, Pennsylvania 19026Bruce 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.
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, Pennsylvania 19026
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 just with fully qualifying.
- Otherwise, we'd probably want to refer to the table by oid, but that
would require having some way 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, but that 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 generally better way to handle
this? How does this tie into the problem Jan noted before?
- We probably need a way to check the 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 broken in a few cases. Update/insert trigger
on fk needs to make sure the row is still there at check time, no
action trigger 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 immediately may be the correct thing, but in
any case, they'd probably need checks like the no action trigger
(what happens if the delete/insert is done within one statement
due to triggers or whatever)
- Match partial - Complicated. To do this completely means almost
a separate implementation since stuff like the above checks wouldn't
work in this case and 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 see if we can generalize more constraints
into the system as well, but we'd have to see)
- We'll want to make dump/restores show the constraint in a better
fashion. This may need the above, and we'd still need to have
backward compatibility (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, Pennsylvania 19026
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 UNIQUEAnd what
ALTER TABLE DROP PRIMARY KEY
ALTER TABLE DROP UNIQUEBTW, 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 UNIQUEAnd what
ALTER TABLE DROP PRIMARY KEY
ALTER TABLE DROP UNIQUEBTW, 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 arelation, 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 UNIQUEI 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, Pennsylvania 19026