How do we know if the correlation is significant? Suppose that we have continuous data $(X_1,Y_1),\dots,(X_n,Y_n)$. Suppose that $r_{x,y}$ is the Karl-Pearson correlation coefficient between $X_i$'s and $Y_i$'s. For what range of values of $r_{x,y}$, can we really decide that there may indeed be a linear relationship between $X_i$'s and $Y_i$' and proceed to predict $Y$ by using a linear regression?
I'm sure the topic concerning this question should be a well-studied one. I did a little search here; couldn't find relevant posts. Any answers to the above question or pointers to such a study is greatly appreciated.

Often the term "significance" is used in the meaning "$\rho$ is statistically significantly different from zero". This is, however, not what most users of $\rho$ are interested in, because the null hypothesis that $\rho$ is exactly zero is almost certainly false. Hence even the tiniest deviation from zero becomes "significant" for a sample size that is large enough.
It is generally of more interest whether a correlation is strong. What is considered a "strong" correlation depends on the field, but here is a rule of thumb taken from an introductory textbook (here is an online reference for the same rule):
\begin{eqnarray*}
|\rho|\leq 0.3: & & \mbox{weak correlation}\\
0.3 < |\rho|\leq 0.7: & & \mbox{moderate correlation}\\
|\rho|> 0.7: & & \mbox{strong correlation}\\
\end{eqnarray*}
I would thus suggest, not to do a hypothesis test against $\rho=0$, but to report a confidence interval for $\rho$. You can find the formulas, e.g., here, and most statistical packages provide functions that compute it for you, for example cor.test in R. Then you can see how far this interval overlaps with the "weak" range.
