Re: Persistent changes in rolled-back transactions

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.oliver@gmail.com