Re: Mark a transaction uncommittable

Поиск
Список
Период
Сортировка
От Isaac Morland
Тема Re: Mark a transaction uncommittable
Дата
Msg-id CAMsGm5e-6-Abg7t+EeNocAdAAFZW=2D9JoK-3jkqjmv9T=fcow@mail.gmail.com
обсуждение исходный текст
Ответ на Mark a transaction uncommittable  (Gurjeet Singh <gurjeet@singh.im>)
Ответы Re: Mark a transaction uncommittable  (Julien Rouhaud <rjuju123@gmail.com>)
Список pgsql-hackers
Дерево обсуждения
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.

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Gurjeet Singh
Дата:
Сообщение: Re: Doc limitation update proposal: include out-of-line OID usage per TOAST-ed columns
Следующее
От: "Daniel Verite"
Дата:
Сообщение: Re: pg_collation.collversion for C.UTF-8