1. What you quote in your message is not a recursive query.
2. Common table expression (the "WITH" part) does not necessarily create a temporary table.
3. If the database manager runs out of temporary space it shouldn't crash. However, the query itself will fail. Other queries may also fail if they use the temporary space as well.