From: Reinhard Mayer <rumayer@freenet.de>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Persistent changes in rolled-back transactions
Date: Thu, 10 Nov 2022 09:10:36 +0100
Message-ID: <b15ca389-ddc4-6036-e283-230134cdd4fb@freenet.de> (raw)
In-Reply-To: <CAOC+FBX=usM8nj_TavDgrBW0kQYpC2Ubg97qShGQ4c4m33NFXQ@mail.gmail.com>
References: <CAOC+FBX=usM8nj_TavDgrBW0kQYpC2Ubg97qShGQ4c4m33NFXQ@mail.gmail.com>
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.oliver@gmail.com <mailto:wellsoliver@gmail.com>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: rumayer@freenet.de, pgsql-admin@lists.postgresql.org
Subject: Re: Persistent changes in rolled-back transactions
In-Reply-To: <b15ca389-ddc4-6036-e283-230134cdd4fb@freenet.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox