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@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
> "David G. Johnston" <david.g.johnston@gmail.com
> <mailto:david.g.johnston@gmail.com>> writes:
> > On Friday, May 3, 2024, Siddharth Jain <siddhsql@gmail.com
> <mailto:siddhsql@gmail.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.klaver@aklaver.com