Re: Question regarding how databases support atomicity

On 5/7/24 9:48 AM, Siddharth Jain wrote:
> Thanks All for the kind responses. I understand how MVCC takes care of
> atomicity for updates to rows. I was developing a project where lets say
> data for each table is stored in its own folder together with metadata
> (we are not talking postgres now). So if I have two tables A and B I
> have a folder structure like:
> A
> \_ metadata.json
> B
> \_ metadata.json
> Now if I want to rename a table, I need to move the folder and also
> update metadata accordingly. These are two separate operations but need
> to be done atomically - all or none. in this case it is possible that we
> succeed in renaming the folder but fail to update metadata for whatever
> reason. then if we try to undo the folder rename we get another failure
> for whatever reason. how to deal with such scenarios? are there no such
> scenarios in postgres?

The only thing I can think of is creating a function in one of the
untrusted languages plpython3u or plperlu to do the renaming. Then in
say plpython3u case wrap the actions in try/except block. On a failure
take the appropriate undo action.

>
>
> On Fri, May 3, 2024 at 8:29 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>
> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com
> <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> writes:
> > On Friday, May 3, 2024, Siddharth Jain <siddhsql(at)gmail(dot)com
> <mailto:siddhsql(at)gmail(dot)com>> wrote:
> >> The way I understand this is that if there is a failure
> in-between, we
> >>> start undoing and reverting the previous operations one by one.
>
> > Not in PostgreSQL.  All work performed is considered provisional
> until a
> > commit succeeds.  At which point all provisional work, which had been
> > tagged with the same transaction identifier, becomes reality to
> the rest of
> > the system, by virtue of marking the transaction live.
>
> Right.  We don't use UNDO; instead, we use multiple versions of
> database rows (MVCC).  A transaction doesn't modify the contents
> of existing rows, but just marks them as provisionally outdated, and
> then inserts new row versions that are marked provisionally inserted.
> Other transactions ignore the outdate markings and the uncommitted new
> rows, until the transaction commits, at which time the new versions
> become live and the old ones become dead.  If the transaction never
> does commit -- either through ROLLBACK or a crash -- then the old row
> versions remain live and the new ones are dead.  In either case, we
> don't have a consistency or correctness problem, but we do have dead
> rows that must eventually get vacuumed away to prevent indefinite
> storage bloat.  That can be done by background housekeeping processes
> though (a/k/a autovacuum).
>
> I believe Oracle, for one, actually does use UNDO.  I don't know
> what they do about failure-to-UNDO.
>
>                         regards, tom lane
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com