From: Tom Lane <tgl@sss.pgh.pa.us>
To: Wael Khobalatte <wael@vendr.com>
cc: "David G. Johnston" <david.g.johnston@gmail.com>,
        Wells Oliver <wells.oliver@gmail.com>,
        pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Persistent changes in rolled-back transactions
In-reply-to: 
 <CAJZ8yobSeSs8b28jzH5P4=OGvntPNxPyu_PsNWKm4De2E_WTjg@mail.gmail.com>
References: 
 <CAOC+FBX=usM8nj_TavDgrBW0kQYpC2Ubg97qShGQ4c4m33NFXQ@mail.gmail.com>
 <CAJZ8yoZvQ7C5j6d+HnQ4aj=nq0R1LA_mLwKcFNcN2yJc=YxWFQ@mail.gmail.com>
 <CAOC+FBX2DWpgb-Ny3Y5yEFGftXX2_wZNG1W3XePa7oRAMxP1Rg@mail.gmail.com>
 <CAKFQuwYScf5K8TJ80C4iohDPdyKrjDNbPqwHTNn-xu=kgUtjWQ@mail.gmail.com>
 <CAJZ8yobSeSs8b28jzH5P4=OGvntPNxPyu_PsNWKm4De2E_WTjg@mail.gmail.com>
Comments: In-reply-to Wael Khobalatte <wael@vendr.com>
	message dated "Wed, 09 Nov 2022 20:40:12 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <78832.1668051767.1@sss.pgh.pa.us>
Date: Wed, 09 Nov 2022 22:42:47 -0500
Message-ID: <78833.1668051767@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/78833.1668051767%40sss.pgh.pa.us>
Precedence: bulk

Wael Khobalatte <wael@vendr.com> writes:
>> Why do you say truncate is non-transactional? Something simple proves
>> that it's not?

> Right, I meant 'non-transactional' in the sense that "persisted changes" as
> you quoted them will also appear in the case of Truncate (MVCC-safety is
> more correct here).

Yeah, TRUNCATE will cause MVCC anomalies, in that data will disappear
although it should still be visible to other transactions running with
pre-TRUNCATE snapshots.  Another thing TRUNCATE does that's not very
kosher is to skip running ON DELETE triggers.  If you don't like these
things, use "DELETE FROM table" instead --- much slower, but no shortcuts.

Sequences are the same sort of animal, in that they give up some
transactional guarantees for performance reasons.

For that matter, every transaction isolation level below full
SERIALIZABLE represents a performance-vs-semantic-guarantees tradeoff.

I can't offhand think of any more examples within Postgres, but I
probably haven't thought long enough.

			regards, tom lane