Re: [SQL] multi column foreign key for implicitly unique columns
Jan, > Because the value in b.y is redundant. b.x->a.x->a.y is exactly the same > value and he even wants to ensure this with the constraint. And in the absence of that constraint, what ensures that b.y = a.y, exactly? -- Josh Berkus Aglio Database Solutions San Francisco ---(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
Re: [SQL] multi column foreign key for implicitly unique columns
On Thu, 2004-08-19 at 17:21, Josh Berkus wrote: > Jan, > > > Because the value in b.y is redundant. b.x->a.x->a.y is exactly the same > > value and he even wants to ensure this with the constraint. > > And in the absence of that constraint, what ensures that b.y = a.y, exactly? In the absence of b.y, it would be impossible for it to be anything else. Isn't that the point? It seems to me that he was trying to use the database to show errors in his source data, but since his constraint would reject the data, he wouldn't be able to enter it; all he could do would be to see the error. So he might as well turn it round, normalise the data properly and use the database to tell the rest of the system what the data ought to be. Oliver Elphick ---(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
Re: [SQL] multi column foreign key for implicitly unique columns
On 8/19/2004 12:52 PM, Oliver Elphick wrote: On Thu, 2004-08-19 at 17:21, Josh Berkus wrote: Jan, > Because the value in b.y is redundant. b.x->a.x->a.y is exactly the same > value and he even wants to ensure this with the constraint. And in the absence of that constraint, what ensures that b.y = a.y, exactly? In the absence of b.y, it would be impossible for it to be anything else. Isn't that the point? Precisely. I meant that the entire column is redundant and obsolete. Without the column, no need for any constraint. It seems to me that he was trying to use the database to show errors in his source data, but since his constraint would reject the data, he wouldn't be able to enter it; all he could do would be to see the error. So he might as well turn it round, normalise the data properly and use the database to tell the rest of the system what the data ought to be. I assumed he often queries b, and to avoid joining a all the time he duplicates values from a into b and then tries to ensure that they stay in sync with constraints. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(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
Re: [SQL] SQL Challenge: Arbitrary Cross-tab
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > Nope; Gaetano's right, you cannot assume that. It's entirely possible > for the planner to choose different plans depending on the OFFSET. > (Maybe not very likely, with such small offsets, but could happen.) Interesting. I realized that there was a finite chance of different plans being chosen, but it seemed neigh-impossible since there is no WHERE clause and the offsets only vary from 0-7. What sort of different plans would it choose, out of curiosity? Seq-scan vs. index-scan? Are there any particular cases where the same plan is guaranteed to be used? - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200408192216 -BEGIN PGP SIGNATURE- iD8DBQFBJV9xvJuQZxSWSsgRAp74AJ96mtrKC1J53y0TPqTPdq2Xost0fACg4DnJ 7P+dgpHWBazGNE9+SR7uxLY= =MZuM -END PGP SIGNATURE- ---(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
Re: [SQL] SQL Challenge: Arbitrary Cross-tab
"Greg Sabino Mullane" <[EMAIL PROTECTED]> writes: >> Nope; Gaetano's right, you cannot assume that. It's entirely possible >> for the planner to choose different plans depending on the OFFSET. >> (Maybe not very likely, with such small offsets, but could happen.) > Interesting. I realized that there was a finite chance of different > plans being chosen, but it seemed neigh-impossible since there is > no WHERE clause and the offsets only vary from 0-7. What sort of > different plans would it choose, out of curiosity? For the particular trivial case you were showing (no WHERE, no GROUP BY, no ORDER BY, no DISTINCT, no nada) it's probably true that only a seqscan plan would ever be chosen. I was worrying that people would take this example and try to add "just that other little thing" to it and get burnt. > Are there any particular cases where the same plan is guaranteed to be > used? No, I'd never care to make such a guarantee. The planner is entirely driven by cost estimates. Even if I could say something definitive about the behavior with the default cost parameters, it wouldn't necessary hold up when someone had taken an axe to random_page_cost or something like that. (It's not impossible that the thing would pick an indexscan plan for even this trivial case, were you to set random_page_cost below 1.) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
