pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPersistent changes in rolled-back transactions
10+ messages / 7 participants
[nested] [flat]
* Persistent changes in rolled-back transactions
@ 2022-11-10 01:07 Wells Oliver <wells.oliver@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Wells Oliver @ 2022-11-10 01:07 UTC (permalink / raw)
To: pgsql-admin
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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Persistent changes in rolled-back transactions
@ 2022-11-10 01:16 Wael Khobalatte <wael@vendr.com>
parent: Wells Oliver <wells.oliver@gmail.com>
1 sibling, 2 replies; 10+ messages in thread
From: Wael Khobalatte @ 2022-11-10 01:16 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
> 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Persistent changes in rolled-back transactions
@ 2022-11-10 01:18 Wells Oliver <wells.oliver@gmail.com>
parent: Wael Khobalatte <wael@vendr.com>
1 sibling, 1 reply; 10+ messages in thread
From: Wells Oliver @ 2022-11-10 01:18 UTC (permalink / raw)
To: Wael Khobalatte <wael@vendr.com>; +Cc: pgsql-admin
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)
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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Persistent changes in rolled-back transactions
@ 2022-11-10 01:20 David G. Johnston <david.g.johnston@gmail.com>
parent: Wael Khobalatte <wael@vendr.com>
1 sibling, 0 replies; 10+ messages in thread
From: David G. Johnston @ 2022-11-10 01:20 UTC (permalink / raw)
To: Wael Khobalatte <wael@vendr.com>; +Cc: Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Persistent changes in rolled-back transactions
@ 2022-11-10 01:23 David G. Johnston <david.g.johnston@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: David G. Johnston @ 2022-11-10 01:23 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: Wael Khobalatte <wael@vendr.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Persistent changes in rolled-back transactions
@ 2022-11-10 01:40 Wael Khobalatte <wael@vendr.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Wael Khobalatte @ 2022-11-10 01:40 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
> 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Persistent changes in rolled-back transactions
@ 2022-11-10 03:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wael Khobalatte <wael@vendr.com>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2022-11-10 03:42 UTC (permalink / raw)
To: Wael Khobalatte <wael@vendr.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Persistent changes in rolled-back transactions
@ 2022-11-10 08:10 Reinhard Mayer <rumayer@freenet.de>
parent: Wells Oliver <wells.oliver@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Reinhard Mayer @ 2022-11-10 08:10 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Persistent changes in rolled-back transactions
@ 2022-11-11 23:18 Rui DeSousa <rui@crazybean.net>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Rui DeSousa @ 2022-11-11 23:18 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Wells Oliver <wells.oliver@gmail.com>; Wael Khobalatte <wael@vendr.com>; pgsql-admin
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Persistent changes in rolled-back transactions
@ 2022-11-12 08:37 Ron <ronljohnsonjr@gmail.com>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 0 replies; 10+ messages in thread
From: Ron @ 2022-11-12 08:37 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 11/11/22 17:18, Rui DeSousa wrote:
>
>
>> 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2022-11-12 08:37 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-10 01:07 Persistent changes in rolled-back transactions Wells Oliver <wells.oliver@gmail.com>
2022-11-10 01:16 ` Wael Khobalatte <wael@vendr.com>
2022-11-10 01:18 ` Wells Oliver <wells.oliver@gmail.com>
2022-11-10 01:23 ` David G. Johnston <david.g.johnston@gmail.com>
2022-11-10 01:40 ` Wael Khobalatte <wael@vendr.com>
2022-11-10 03:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-11 23:18 ` Rui DeSousa <rui@crazybean.net>
2022-11-12 08:37 ` Ron <ronljohnsonjr@gmail.com>
2022-11-10 01:20 ` David G. Johnston <david.g.johnston@gmail.com>
2022-11-10 08:10 ` Reinhard Mayer <rumayer@freenet.de>
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