On Sat, 22 Apr 2023 at 11:01, Gurjeet Singh <gurjeet@singh.im> wrote:
This is a proposal for a new transaction characteristic. I haven't written any code, yet, and am interested in hearing if others may find this feature useful.
Many a times we start a transaction that we never intend to commit; for example, for testing, or for EXPLAIN ANALYZE, or after detecting unexpected results but still interested in executing more commands without risking commit, etc.
A user would like to declare their intent to eventually abort the transaction as soon as possible, so that the transaction does not accidentally get committed.
I have an application for this: creating various dev/test versions of data from production.
Start by restoring a copy of production from backup. Then successively create several altered versions of the data and save them to a place where developers can pick them up. For example, you might have one version which has all data old than 1 year deleted, and another where 99% of the students/customers/whatever are deleted. Anonymization could also be applied. This would give you realistic (because it ultimately originates from production) test data.
This could be done by starting a non-committable transaction, making the adjustments, then doing a pg_dump in the same transaction (using --snapshot to allow it to see that transaction). Then rollback, and repeat for the other versions. This saves repeatedly restoring the (probably very large) production data each time.
What I’m not sure about is how long it takes to rollback a transaction. I'm assuming that it’s very quick compared to restoring from backup.
It would be nice if pg_basebackup could also have the --snapshot option.
