I'm on the same line: BEGIN/ROLLBACK requires trivial effort and a --dry-run option might give a false sense of security, but it cannot possibly rollback side-effects of user functions which modify filesystem or interact with the outside world in some other way.
Pretty much. Side effects.
The most glaringly obvious example is nextval(...) and setval(...). You can't make them have no effect, since your script will then fail to run. But you can't roll them back either.
Also, anything that touches the file system, like COPY. Untrusted PLs that can fiddle with the file system. FDWs. All sorts. Oh, and of course psql commands like \o .
I think this idea is completely unworkable. You might work around the filesystem access issues with a new attribute (like LEAKPROOF) that asserts that a function relies strictly on in-database transactional behaviour. But you're not going to be able to do a dry run with sequence setting. You could ignore setval and run nextval as normal, but then your dry-run wouldn't reflect what the real run would do...