Re: Question regarding how databases support atomicity

Siddharth Jain <siddhsql(at)gmail(dot)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