If a pullback exists in the category of smooth manifold then, its underlying set of points has to be what you described simply by looking at morphism from the point. Moreover a map into the fiber product is smooth if and only if the map to the product is smooth (because it is smooth if and only if each component is smooth by the universal properties of the pullback).
So the only things surprising things that can happen if the categorical pullback exists, is that the fiber product has a smooth structure which is not induced by the differentiable structure on the product, but still have the same smooth map into it, like the example you gave a link to... But this situation is rather weird, and it is a lot more common to simply have no pullback:
So take for example the pullback of $\{0\}$ along the map $(x,y) \rightarrow xy$. If this pullback existed it would be a smooth structure on the union of the horizontal and the vertical line in $\mathbb{R^2}$ such that a map into it is smooth if and only if it is smooth when seen as a map into $\mathbb{R^2}$.