What happens with WITH if later the query fails
A single statement is always run atomically. 

Quote from the manual


  PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block


A set of common table expressions defined under the same WITH clause, however many of them (including writeable CTEs), is still part of a single statement.  

So the statement either fails completely or succeeds completely.

You only need (explicit) transactions if you want to run atomically more than one statement.

The handling of generated sequence values is no different than in any other situation: a nextval operation is never rolled back