Обсуждение: 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?
--
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
> 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?
Postgres sequences (what backs the serial type) are non-transactional. nextval, setval, et al. Truncate is also non-transactional.
Postgres sequences (what backs the serial type) are non-transactional. nextval, setval, et al. Truncate is also non-transactional.
Why do you say truncate is non-transactional? Something simple proves that it's not?
wells=# create table foo (f text);
wells=# insert into foo values('cat');
INSERT 0 1
wells=# select * from foo;
f
-----
cat
(1 row)
wells=# begin;
BEGIN
wells=*# truncate foo;
TRUNCATE TABLE
wells=*# rollback;
ROLLBACK
wells=# select * from foo;
f
-----
cat
(1 row)
wells=# insert into foo values('cat');
INSERT 0 1
wells=# select * from foo;
f
-----
cat
(1 row)
wells=# begin;
BEGIN
wells=*# truncate foo;
TRUNCATE TABLE
wells=*# rollback;
ROLLBACK
wells=# select * from foo;
f
-----
cat
(1 row)
On Wed, Nov 9, 2022 at 5:16 PM Wael Khobalatte <wael@vendr.com> 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?
Postgres sequences (what backs the serial type) are non-transactional. nextval, setval, et al. Truncate is also non-transactional.
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
On Wed, Nov 9, 2022 at 6:16 PM Wael Khobalatte <wael@vendr.com> 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?
Postgres sequences (what backs the serial type) are non-transactional. nextval, setval, et al. Truncate is also non-transactional.
Truncate is transactional, it is not MVCC compliant though.
Writing data out to file on the filesystem, using dblink and opening a new session in a different database and doing work there are both case where the actions cross the system boundary that the transaction is able to control.
David J.
On Wed, Nov 9, 2022 at 6:19 PM Wells Oliver <wells.oliver@gmail.com> wrote:
Why do you say truncate is non-transactional? Something simple proves that it's not?
Or just read the documentation for the current version (I seem to recall it used to be non-transactional, maybe...doesn't matter now).
Sequences really shouldn't have been a surprise given the great lengths we go to document their gap-ful nature and this property.
Most anything a typical user is going to do within a SQL transaction is going to either be transactional or it will be disallowed to execute said command within a transaction.
David J.
> 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). As David mention I also thought it was not transactional at all, but it seems it is in recent version or I am seeing ghosts. Regardless, it's definitely fits the description of what you are trying to be aware of when it comes to transactional behavior.
Consider starting a transaction in REPEATABLE READ, do a "begin", then nothing (because if you select you block the upcoming truncate). In a different session, do the truncation, commit it. Back to the REPEATABLE READ transaction, still open, you select, the data is gone. Therefore "persisted changes" is true.
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). As David mention I also thought it was not transactional at all, but it seems it is in recent version or I am seeing ghosts. Regardless, it's definitely fits the description of what you are trying to be aware of when it comes to transactional behavior.
Consider starting a transaction in REPEATABLE READ, do a "begin", then nothing (because if you select you block the upcoming truncate). In a different session, do the truncation, commit it. Back to the REPEATABLE READ transaction, still open, you select, the data is gone. Therefore "persisted changes" is true.
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
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
On Nov 9, 2022, at 8:23 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:Or just read the documentation for the current version (I seem to recall it used to be non-transactional, maybe...doesn't matter now).
In a lot of other databases DDL is not transactional and truncate is DDL — most likely confusing it with another RDBMS systems.
DDL is transactional in PostgreSQL thus you can even rollback table changes, etc. which is not possible in other RDBMS systems.
prod=# create table demo (a int);
CREATE TABLE
prod=#
prod=# \d+ demo;
Table "dev.demo"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | | | plain | | |
Access method: heap
prod=# begin;
BEGIN
prod=*# alter table demo add column b int;
ALTER TABLE
prod=*# \d+ demo;
Table "dev.demo"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | | | plain | | |
b | integer | | | | plain | | |
Access method: heap
prod=*# rollback;
ROLLBACK
prod=# \d+ demo;
Table "dev.demo"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | | | plain | | |
Access method: heap
On 11/11/22 17:18, Rui DeSousa wrote:
The first two RDBMS I dealt with had transactional DDL. It's... natural, given that table definitions reside in a system catalog that's just a bunch of tables... I was shocked to see how Oracle does it (and that they don't have integers).
On Nov 9, 2022, at 8:23 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:Or just read the documentation for the current version (I seem to recall it used to be non-transactional, maybe...doesn't matter now).In a lot of other databases DDL is not transactional and truncate is DDL — most likely confusing it with another RDBMS systems.
The first two RDBMS I dealt with had transactional DDL. It's... natural, given that table definitions reside in a system catalog that's just a bunch of tables... I was shocked to see how Oracle does it (and that they don't have integers).
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.