Re: Persistent changes in rolled-back transactions

On 11/10/22 02:07, Wells Oliver wrote:
> I've noticed serials still maintain incremented values even when a
> transaction is rolled back. Are there other similar persistent changes
> to be aware of?

If we relax the word "persistence" then I would mention advisory_locks:

You can request an advisory lock with pg_advisory_lock() and
pg_advisory_xact_lock().

The lock obtained by pg_advisory_lock() will survive a rollback:

postgres=# begin;
BEGIN

postgres=*# select pg_advisory_lock(1);
 pg_advisory_lock
------------------

(1 row)

postgres=*# select pg_advisory_xact_lock(2);
 pg_advisory_xact_lock
-----------------------

(1 row)

postgres=*# select objid from pg_locks where locktype = 'advisory';
 objid
-------
     2
     1
(2 rows)

postgres=*# rollback ;
ROLLBACK

postgres=# select objid from pg_locks where locktype = 'advisory';
 objid
-------
     1
(1 rows)

==========

Reinhard

>
> --
> Wells Oliver
> wells(dot)oliver(at)gmail(dot)com <mailto:wellsoliver(at)gmail(dot)com>