Feedback-ID: i76984098:Fastmail
Message-ID: <a24665e3-d712-4965-a904-26371e32ffe1@aklaver.com>
Date: Thu, 6 Feb 2025 14:23:57 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: How to perform a long running dry run transaction without
 blocking
To: Robert Leach <rleach@princeton.edu>
Cc: pgsql-general <pgsql-general@postgresql.org>
References: <BD62E056-3F3B-4CC0-A8CA-E5B7B9CB35CA@princeton.edu>
 <88d60ace-45e6-4d41-afc4-113df7219c4d@aklaver.com>
 <4000D0EE-B250-4E9E-831F-00C034D6D0B5@princeton.edu>
 <6d833658-f461-4ad4-a3e1-86d3c515bc18@aklaver.com>
 <0FE9C709-A108-4ED5-8132-B802B8D9908F@princeton.edu>
 <b89a7132-7fa5-4229-a03c-20f5d1e11cf4@aklaver.com>
 <00DF52D1-5ED0-4599-8480-8C671434EE4E@princeton.edu>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <00DF52D1-5ED0-4599-8480-8C671434EE4E@princeton.edu>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/a24665e3-d712-4965-a904-26371e32ffe1%40aklaver.com>
Precedence: bulk

On 2/6/25 12:08, Robert Leach wrote:


>> Alright I am trying to reconcile this with from below, 'The largest 
>> studies take just under a minute'.
> 
> The context of the 'The largest studies take just under a minute' 
> statement is that it's not loading the hefty/time-consuming raw data. 
>   It's only validating the metadata.  That's fast (5-60s).  And that 
> data is a portion of the transaction in the back-end load.  There are 
> errors that validation can miss that are due to not touching the raw 
> data, and in fact, those errors are addressed by curators editing the 
> excel sheets.  That's why it's all in the load transaction instead of 

As a scientist that makes me start to twitch.

Is there an audit trail for that?


> 
> I'm unfamiliar with retry functionality, but those options sound logical 
> to me as a good path forward, particularly using celery to spread out 
> validations and doing the back end loads at night (or using some sort of 
> fast dump/load).  The thing that bothers me about the celery solution is 
> that most of the time, 2 users validating different data will not block, 
> so I would be making users wait for no reason.  Ideally, I could 
> anticipate the block and only at that point, separate those validations.

Aah, time travel.

For fast dump/load on validated data see:

https://www.postgresql.org/docs/current/sql-copy.html

Though note in Postgres 16- COPY is all or nothing, so if there is an 
error nothing will be loaded. With version 17 you get ON_ERROR and 
LOG_VERBOSITY. One way to deal with is to load to a staging table and do 
your validation there and then move the data to the final table.

As to retry that depends on where you want to do it. For subtransactions 
(SAVEPOINT) see:

https://www.postgresql.org/docs/current/sql-savepoint.html

https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/

In Python there is try/except.


> 
> This brings up a question though about a possibility I suspect is not 
> practical.  My initial read of the isolation levels documentation found 
> this section really promising:
> 
>  > The Repeatable Read isolation level only sees data committed before 
> the transaction began; it never sees either uncommitted data or changes 
> committed during transaction execution by concurrent transactions.
> 
> This was before I realized that the actions of the previously started 
> transaction would include "locks" that would block validation even 
> though the load transaction hasn't committed yet:
> 
>  > a target row might have already been updated (or deleted or *locked*) 
> by another concurrent transaction by the time it is found. In this case, 
> the repeatable read transaction will wait for the first updating 
> transaction to commit or roll back
> 
> Other documentation I read referred to the state of the DB (when a 
> transaction starts) as a "snapshot" and I thought... what if I could 
> save such a snapshot automatically just *before* a back-end load starts, 
> and use that snapshot for validation, such that my validation processes 
> could use that to validate against and not encounter any locks?  The 
> validation will never commit, so there's no risk.

Hmm. I don't think so.

> 
> I know Django's ORM wouldn't support that, but I kind of hoped that 
> someone in this email list might suggest a snapshot functionality as a 
> possible solution.  Since the validations never commit, the only 
> downside would be if the backend load changed something that introduces 
> a problem with the validated data that would not be fixed until we 
> actually attempt to load it.
> 
> Is that too science-fictiony of an idea?

-- 
Adrian Klaver
adrian.klaver@aklaver.com