From: Tom Lane <tgl@sss.pgh.pa.us>
To: Siddharth Jain <siddhsql@gmail.com>
cc: "David G. Johnston" <david.g.johnston@gmail.com>,
        "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: Question regarding how databases support atomicity
In-reply-to: <CAPqV3pTe+b3Q44jdvhAtZNnroteRLp7kHFLvWFypu0JDwtaYjA@mail.gmail.com>
References: <CAPqV3pRO=u1Mno06WLCFxoyxbzkYTetiqTs7WaW=_fkjRPgYhw@mail.gmail.com> <CAPqV3pQquw63Fv_fCJzAAnUWk86qHfg1cH=+mJ2N=4LGdDyrNw@mail.gmail.com> <CAKFQuwb2MtE02QgMQ3vvLOj7bvGMar6qwnigCBBaYLBKsdk3xg@mail.gmail.com> <2805642.1714793392@sss.pgh.pa.us> <CAPqV3pTe+b3Q44jdvhAtZNnroteRLp7kHFLvWFypu0JDwtaYjA@mail.gmail.com>
Comments: In-reply-to Siddharth Jain <siddhsql@gmail.com>
	message dated "Tue, 07 May 2024 09:48:38 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4133570.1715101484.1@sss.pgh.pa.us>
Date: Tue, 07 May 2024 13:04:44 -0400
Message-ID: <4133571.1715101484@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/4133571.1715101484%40sss.pgh.pa.us>
Precedence: bulk

Siddharth Jain <siddhsql@gmail.com> writes:
> 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?

There aren't, because we don't assume that we can apply filesystem
metadata changes like that.  That's why our on-disk files are not
named after their tables ;-) --- that way they never need renaming.

https://www.postgresql.org/docs/current/storage-file-layout.html

There may be filesystems out there that would give you guarantees in
this area, but it'd require non-POSIX and hence non-portable system
calls.

			regards, tom lane