I'm familiar with coordinate systems where the direction of the basis vectors changes with position, but I haven't come across any where the relative magnitude of the basis vectors themselves are allowed to change also.
[Physics] What coordinate systems allows the magnitude of the basis vectors to change with position
coordinate systemsdifferential-geometryvectors
Related Solutions
You are correct.
Position is a vector when you are working in a vector space, since, well, it is a vector space. Even then, if you use a nonlinear coordinate system, the coordinates of a point expressed in that coordinate system will not behave as a vector, since a nonlinear coordinate system is basically a nonlinear map from the vector space to $\mathbb{R}^n$, and nonlinear maps do not preserve the linear structure.
On a manifold, there is no sense in attempting to "vectorize" points. A point is a point, an element of the manifold, a vector is a vector, element of a tangent space at a point. Of course you can map points into $n$-tuples, that is part of the definition of a topological manifold, but there is no reason why the inverse of this map should carry the linear structure over to the manifold.
And now, for a purely personal opinion: While Carroll's book is really good, the physicist's way of attempting to categorize everything by "transformation properties" is extremely counterproductive, and leads to such misunderstandings as you have overcome here. If one learns proper manifold theory, this is clear from the start...
I hope someone could clarify me the general concept of position vectors and change of frame of reference (or coordinate system), or link me a good resource where this stuff is treated from the mathematician's viewpoint.
First off, a better name for a position vector is a displacement vector. Displacement vectors are not free vectors. They aren't quite vectors, period, in the sense of the mathematical concept of a vector space. They instead are members of an affine space, with the transformation between two affine spaces given by an affine transformation $\boldsymbol x' = \boldsymbol {\mathrm M} \boldsymbol x + \boldsymbol b$, where $\boldsymbol {\mathrm M}$ is an invertible matrix (or a proper orthogonal matrix if you want to keep things simple) and $\boldsymbol b$ is the displacement vector from one origin to another.
You asked for a good resource where this stuff is treated from a mathematician's point of view. The sister site, Mathemetics StackExchange has a good number of questions and answers on the topics of affine spaces and affine transformations.
Best Answer
The only orthonormal coordinate basis is the Cartesian coordinate basis. The basis vectors for the, e.g., polar coordinate basis are orthogonal but not normalized.
That doesn't mean that one can't normalize the polar basic vectors to get the polar unit basis but such a basis isn't a coordinate basis.
For the Cartesian coordinate basis, the basis vectors are orthonormal:
$$\vec e_x \cdot \vec e_x = g_{xx} = 1 $$
$$\vec e_y \cdot \vec e_y = g_{yy} =1$$
$$\vec e_x \cdot \vec e_y = g_{xy} = g_{yx}= 0 $$
and the line element is
$$dl^2 = g_{xx}dx^2 + g_{yy}dy^2 + 2g_{xy}dxdy = dx^2 + dy^2$$
Now, polar coordinates are defined by
$$r = \sqrt{x^2 + y^2}$$
$$\theta = \tan^{-1}\left(\frac{y}{x}\right)$$
thus
$$x = r \cos \theta$$
$$y = r \sin \theta$$
and the polar coordinate basis vectors are then
$$\vec e_r = \frac{\partial x}{\partial r}\vec e_x + \frac{\partial y}{\partial r}\vec e_y = \cos \theta \; \vec e_x + \sin \theta \; \vec e_y $$
$$\vec e_\theta = \frac{\partial x}{\partial \theta}\vec e_x + \frac{\partial y}{\partial \theta}\vec e_y = -r \sin \theta \; \vec e_x + r \cos \theta \; \vec e_y$$
so
$$\vec e_r \cdot \vec e_r = g_{rr}=1$$
$$\vec e_\theta \cdot \vec e_\theta = g_{\theta \theta}= r^2$$
$$\vec e_r \cdot \vec e_\theta = g_{r\theta} = g_{\theta r} = 0$$
and the line element is
$$ds^2 = g_{rr}dr^2 + g_{\theta \theta}d\theta^2 + 2g_{r\theta}drd\theta = dr^2 + r^2d\theta^2$$
Finally, we ask if coordinates $\{\hat r, \hat \theta\}$ can be found for the unit polar basis such that
$$\vec e_{\hat r} = \vec e_r = \frac{\partial x}{\partial \hat r}\vec e_x + \frac{\partial y}{\partial \hat r}\vec e_y = \cos \theta \; \vec e_x + \sin \theta \; \vec e_y $$
$$\vec e_\hat \theta = \frac{1}{r}\vec e_\theta = \frac{\partial x}{\partial \hat \theta}\vec e_x + \frac{\partial y}{\partial \hat \theta}\vec e_y = -\sin \theta \; \vec e_x + \cos \theta \; \vec e_y$$
If there are coordinates $\{\hat r, \hat \theta\}$, then
$$\frac{\partial^2 x}{\partial \hat r \partial \hat \theta} = \frac{\partial^2 x}{\partial \hat \theta \partial \hat r }$$
$$\frac{\partial^2 y}{\partial \hat r \partial \hat \theta} = \frac{\partial^2 y}{\partial \hat \theta \partial \hat r }$$
but
$$\frac{\partial^2 x}{\partial \hat r \partial \hat \theta} = \frac{\partial}{\partial \hat r} (-\sin \theta) = \frac{\partial}{\partial \hat r}\left(-\frac{y}{r}\right) = \frac{\partial}{\partial r}\left(-\frac{y}{r}\right) = \frac{y}{r^2}$$
$$\frac{\partial^2 x}{\partial \hat \theta \partial \hat r} = \frac{\partial}{\partial \hat \theta} (\cos \theta) \ne \frac{y}{r^2}$$
and thus coordinates ${\hat r, \hat \theta}$ do not exist; the unit polar basis is not a coordinate basis.
To better see this, consider the level curves of the polar coordinate system:
The concentric circles represent the basis one-form $\tilde dr$ dual to the $r$ basis vector $\vec e_r$. Note that the spacing of the circles is constant which means that magnitude of $\tilde dr$ is constant.
The radial lines represent the basis one-form $\tilde d\theta$ dual to the $\theta$ basis vector $\vec e_\theta$. Note that as the $r$ coordinate increases, the spacing between the radial lines increases or, put another way, the density goes as $\frac{1}{r}$ thus the magnitude of $\tilde d\theta$ is not constant and, in fact, is just $\frac{1}{r}$
$$\tilde d\theta \cdot \tilde d\theta = \frac{1}{r^2}$$
But since
$$\langle\tilde d\theta, \vec e_\theta\rangle = 1$$
it follows that
$$\vec e_\theta \cdot \vec e_\theta = r^2$$
Now it's easy to 'see' why the unit polar basis is not a coordinate basis; if
$$\vec e_\hat \theta \cdot \vec e_\hat \theta = 1$$
then the radial lines (lines of constant $\hat \theta$) must have constant density but radial lines cannot have constant density.