In the past, developers have wondered how we can provide "--dry-run"
functionality
https://www.postgresql.org/message-id/15791.1450383201%40sss.pgh.pa.us
This is important for application developers, especially when
migrating programs to Postgres.
Presented here are 3 features aimed at developers, each of which is
being actively used by me in a large and complex migration project.
* psql --parse-only
Checks the syntax of all SQL in a script, but without actually
executing it. This is very important in the early stages of complex
migrations because we need to see if the code would generate syntax
errors before we attempt to execute it. When there are many
dependencies between objects, actual execution fails very quickly if
we run in a single transaction, yet running outside of a transaction
can leave a difficult cleanup task. Fixing errors iteratively is
difficult when there are long chains of dependencies between objects,
since there is no easy way to predict how long it will take to make
everything work unless you understand how many syntax errors exist in
the script.
001_psql_parse_only.v1.patch
* nested transactions = off (default) | all | on
Handle nested BEGIN/COMMIT, which can cause chaos on failure. This is
an important part of guaranteeing that everything that gets executed
is part of a single atomic transaction, which can then be rolled back
- this is a pre-requisite for the last feature.
002_nested_xacts.v7.patch
The default behavior is unchanged (off)
Setting "all" treats nested BEGIN/COMMIT as subtransactions, allowing
some parts to fail without rolling back the outer transaction.
Setting "outer" flattens nested BEGIN/COMMIT into one single outer
transaction, so that any failure rolls back the entire transaction.
* rollback_on_commit = off (default) | on
Force transactions to fail their final commit, ensuring that no
lasting change is made when a script is tested. i.e. accept COMMIT,
but do rollback instead.
003_rollback_on_commit.v1.patch
We will probably want to review these on separate threads, but the
common purpose of these features is hopefully clear from these notes.
001 and 003 are fairly small patches, 002 is longer.
Comments please
--
Simon Riggs http://www.EnterpriseDB.com/
