Re: [PATCHES] notice about costly ri checks (2)
As a side question, if there are multiple cross-type conversions in one constraint on different column pairs, what do we think the message should be? One message with multiple column mentions in detail or multiple notices? (I haven't looked at the patch to see if one or the other is easier with how it's set up) I would expect it to generate one WARNING for each mismatch; doing anything else would make life a lot more complex, both as to writing the code and as to formatting the output readably. I agree. patch v1 issued the warning once for each mismatch, in the check loop. patch v2 issued the warning once for the foreign key, outside of the loop. patch v3 is yet to come;-) I'll re-submit a patch some time later, with a WARNING and mismatch column names and types specified. -- Fabien. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] notice about costly ri checks (2)
On Mar 5, 2004, at 1:49 PM, Bruce Momjian wrote: Agreed. The current text is: NOTICE: costly cross-type foreign key because of component 1 Seems we should say something like: NOTICE: foreign key constraint 'constrname' must use a costly cross-type conversion It seems to me that in some ways this is similar to the situation where indexes are created to enforce a UNIQUE constraint. Indexes also incur additional overhead for inserts and updates, but make no mention of the cost: the DBA is assumed to know that, or they can check the docs if they're interested in why such a notice is being raised. I'd think something as simple as NOTICE: foreign key constraint 'constrname' will require a cross-type conversion similar to NOTICE: CREATE TABLE / UNIQUE will create implicit index foox_interesting_key for table foox Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] notice about costly ri checks (2)
Bruce Momjian [EMAIL PROTECTED] writes: The reason I think we have to mention the constraint name is that you could have a multi-column primary/foreign key, so instead of mentioning each column, we just mention the constraint name, which should be easy to identify. However, the complaint will be about one single column being of a non-matching type. In the case of a multicolumn foreign key, giving only the constraint name is unhelpful. Even for a one-column key, it's not obvious to me why the constraint name is better than the column name. [ thinks... ] I guess it could be that the same column is being used in several different FK constraints, so if we just give column names then it would also be important to mention the referenced column. I'd suggest something along the lines of NOTICE: foreign key constraint constrname will require a cross-type conversion DETAIL: key columns fkcol and pkcol are of different types integer and double precision if you want to be really complete. I've got mixed feelings about the WARNING-vs-NOTICE issue. WARNING seems too strong, like we are trying to tell them that it won't work at all. Particularly with text like the above, which completely fails to explain that the problem is only one of speed and not functionality. If you want it to be a WARNING then we gotta work on the text some more. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] notice about costly ri checks (2)
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: I'd suggest something along the lines of NOTICE: foreign key constraint constrname will require a cross-type conversion DETAIL: key columns fkcol and pkcol are of different types integer and double precision I suggested the constraint name because of multi-column keys, where he would have to print an arbitrary number of columns in the message. It didn't seem worth doing that work. I see your idea of just printing the column, but that doesn't really point to the primary/foreign key relationship. If the user can't figure out which columns are a mismatch from the constraint name, they have larger problems than this. :-) Why should we make them guess which column is the problem, when we know it perfectly well? If you want it to be a WARNING then we gotta work on the text some more. Yes, let's re-add 'costly' to the text: WARNING: foreign key constraint constrname will require a costly cross-type conversion Works for me, but I still want the DETAIL ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] notice about costly ri checks (2)
On Fri, 5 Mar 2004, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: I'd suggest something along the lines of NOTICE: foreign key constraint constrname will require a cross-type conversion DETAIL: key columns fkcol and pkcol are of different types integer and double precision I suggested the constraint name because of multi-column keys, where he would have to print an arbitrary number of columns in the message. It didn't seem worth doing that work. I see your idea of just printing the column, but that doesn't really point to the primary/foreign key relationship. If the user can't figure out which columns are a mismatch from the constraint name, they have larger problems than this. :-) Why should we make them guess which column is the problem, when we know it perfectly well? As a side question, if there are multiple cross-type conversions in one constraint on different column pairs, what do we think the message should be? One message with multiple column mentions in detail or multiple notices? (I haven't looked at the patch to see if one or the other is easier with how it's set up) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] notice about costly ri checks (2)
Stephan Szabo [EMAIL PROTECTED] writes: Why should we make them guess which column is the problem, when we know it perfectly well? As a side question, if there are multiple cross-type conversions in one constraint on different column pairs, what do we think the message should be? One message with multiple column mentions in detail or multiple notices? (I haven't looked at the patch to see if one or the other is easier with how it's set up) I would expect it to generate one WARNING for each mismatch; doing anything else would make life a lot more complex, both as to writing the code and as to formatting the output readably. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] notice about costly ri checks (2)
Fabien COELHO wrote: Dear patchers, Here is my second and last try of the day. This patch adds a notice at constraint creation time if the referential integrity check is to be costly, that is it cannot use the index due to some incompatibility. The patch was generated with the difforig script against the current cvs head. I put much validation which looks fine to me, but it is only me. I'm not that satisfied with the wording and the content of the error message. Any better suggestion would be welcome. Agreed. The current text is: NOTICE: costly cross-type foreign key because of component 1 Seems we should say something like: NOTICE: foreign key constraint 'constrname' must use a costly cross-type conversion -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
