Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
From: Robert Leach <rleach@princeton.edu>
Mime-Version: 1.0 (1.0)
Date: Thu, 6 Feb 2025 10:40:41 -0500
Subject: How to perform a long running dry run transaction without blocking
Message-Id: <BD62E056-3F3B-4CC0-A8CA-E5B7B9CB35CA@princeton.edu>
To: pgsql-general@lists.postgresql.org
Archived-At: <https://www.postgresql.org/message-id/BD62E056-3F3B-4CC0-A8CA-E5B7B9CB35CA%40princeton.edu>
Precedence: bulk

I've been trying to solve this problem in Django and I've finally decided af=
ter over a year going down this path that there's no way to solve it in Djan=
go (in a database agnostic fashion). So I'm thinking that I need to explore a=
 Postgres-specific solution. FYI, I'm not a database expert. I just know eno=
ugh to get myself into trouble like this problem.

Let me try and distill all the back story down to an oversimplified explanat=
ion:

I created a scientific data submission validation interface that helps resea=
rchers compile their data submissions to our database. To do this, I decided=
 to add a `--validate` option to the load scripts that essentially raises a d=
ry run exception at the end of a load run so that the data is all rolled bac=
k before leaving the atomic transaction block.

This validation interface skips the raw data load step, which is the hefties=
t, most long running, part and generally finishes in well under a minute.

The interface works fantastically well. It rolls back problematic data in sm=
aller transactions and buffers those errors for a final report that the user=
 can work to fix in their data files and retry validation until all their pr=
oblems are solved and the data is ready to load.

The problem is that if we have a long running load going on in the backgroun=
d (which can take hours) and during that load, a user tries to validate a ne=
w submission that contains some overlapping common data (e.g. a new tissue t=
ype or protocol that is currently being loaded) or perhaps they are trying t=
o validate data added to an existing submission that is being partially load=
ed, that validation process gets blocked and the validation interface encoun=
ters a gateway timeout.

I had tried changing the isolation level to both repeatable read and seriali=
zable, but the hang can still occur (though serializable seems to avoid hang=
s in some cases that repeatable read does not).

My initial interpretation of the isolation level documentation was that tran=
sactions would not be aware of what is happening in other transactions and t=
hat if there was a problem, you would only ever get a serialization error at=
 the end when the data is committed, but after encountering the hang yesterd=
ay, I realized that the "snapshot" that the second transaction gets includes=
 locks already established by the first transaction.

I had hoped that since my validation interface would never commit, it would n=
ever have a serialization error or hang.

What options are available that might allow this all to work as I intended? I=
s there some way to tell the validation process transaction to assume that a=
ny pending changes from another transaction will succeed?

I have lots of ideas on how to mitigate these occurrences, but I don't want t=
o bias any potentially helpful responses. Am I screwed from my early decisio=
n to use the loading code to validate data submissions?=