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