1

Topic: Whether idle in transaction (aborted) holds a DB picture?

Colleagues, vigorous day.
From the description it is not clear:
idle in transaction: server process is in transaction, but now does not fulfill any request.
idle in transaction (aborted): This state similarly idle in transaction except that one of operators in transaction caused an error.

Whether idle in transaction (aborted) holds a DB picture?

2

Re: Whether idle in transaction (aborted) holds a DB picture?

Dark blue Elephant;
To begin with it is necessary to be defined with isolation level of this transaction.
For READ COMMITTED that by default, the picture forms at the moment of the beginning of each operator and operates before the termination of operation of the operator, therefore and for idle in transaction, and for idle in transaction (aborted) the picture already "ended".
As to REPEATABLE READ and SERIALIZABLE for which the picture forms at the moment of the beginning of the first operator of transaction here  understands that for transaction with idle in transaction (aborted) the picture any more is not required, and for idle in transaction the picture needs to be held.

3

Re: Whether idle in transaction (aborted) holds a DB picture?

Luzanov wrote:

the Dark blue Elephant;
To begin with it is necessary to be defined with isolation level of this transaction.
For READ COMMITTED that by default, the picture forms at the moment of the beginning of each operator and operates before the termination of operation of the operator, therefore and for idle in transaction, and for idle in transaction (aborted) the picture already "ended".
As to REPEATABLE READ and SERIALIZABLE for which the picture forms at the moment of the beginning of the first operator of transaction here  understands that for transaction with idle in transaction (aborted) the picture any more is not required, and for idle in transaction the picture needs to be held.

Whether I strongly suspect a question was influences idle in transaction (aborted) on bloat just as simply idle in transaction instead of about actually snapshot.
Whether or about holds idle in transaction (aborted) AccessShareLock on tables which were in transaction (influence on depositing alters).
I would assume yes on both questions but is not assured on 100 %.
--
Maxim Boguk
the best support PostgreSQL: dataegret.ru

4

Re: Whether idle in transaction (aborted) holds a DB picture?

Maxim Boguk wrote:

whether I strongly suspect a question was influences idle in transaction (aborted) on bloat just as simply idle in transaction instead of about actually snapshot.

I under "" a picture understood that vacuum should not clean the version of lines remote in other transaction.
Like it just about bloat.
In that case, business is exactly how wrote above.

Maxim Boguk wrote:

whether Or about holds idle in transaction (aborted) AccessShareLock on tables which were in transaction (influence on depositing alters).
I would assume yes on both questions but is not assured on 100 %.

And here of such deal did not think and rechecked.
For transaction in the status idle in transaction (aborted) locks in pg_locks (type AccessShareLock) are deleted. Truly for any isolation levels.
It and is logical, after all such transaction all the same more than anything cannot make.

5

Re: Whether idle in transaction (aborted) holds a DB picture?

In general I would answer an initial question that idle in transaction (aborted) holds nothing.
But it would be desirable to explain here what moment.
Often I hear that is simple idle in transaction influences on bloat, here and Maxim hints at it:

Maxim Boguk wrote:

whether I strongly suspect a question was influences idle in transaction (aborted) on bloat just as simply idle in transaction ...

It would be desirable to understand in which image.
The matter is that simple experiments of this influence do not show (provided that transaction by default in READ COMMITTED).
The first session:

postgres=# create table t (id int);
CREATE TABLE
postgres=# insert into t values (1);
INSERT 0 1
postgres=# begin isolation level read committed;
BEGIN
postgres=# select *, pg_backend_pid () from t;
id | pg_backend_pid
----+----------------
1 | 3876
(1 row)

The second session:

postgres=# select state from pg_stat_activity where pid =3876;
state
---------------------
idle in transaction
(1 row)
postgres=# delete from t where id = 1;
DELETE 1
postgres=# vacuum verbose t;
INFO: vacuuming "public.t"
INFO: "t": removed 1 row versions in 1 pages
INFO: "t": found 1 removable, 0 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 122792
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "t": stopping truncate due to conflicting lock request
VACUUM

Apparently, hinders nothing to vacuum to clean the remote version of a line, though the first transaction in idle in transaction.
Swelling does not happen.
I suspect that life more richly, but where a dirty trick?

6

Re: Whether idle in transaction (aborted) holds a DB picture?

Pavel Luzanov;
I will assume that put isolation level.
Level read commited allows to delete the old version of a line (including in a state idle in transaction), repeatable read - is not present

7

Re: Whether idle in transaction (aborted) holds a DB picture?

gav21;
I came to such output.
Dolgovisjashchie transactions in the status idle in transaction it, certainly, angrily:
1. It is impossible to receive exclusive locks on tables (for example for ALTER TABLE) to which requests since hanging transactions retain locks AccessShareLock were fulfilled.
2. Can stop a frost of transactions.
3. If hanging transaction already changed/deleted a line vacuum cannot clean old versions of these lines.
4. If the status idle in transaction at transactions REPEATABLE READ, SERIALIZABLE - generally the trouble, vacuum does not clean the version of lines visible in these transactions, and this swelling of tables and indexes.
And generally, the stop of operation in the middle of transaction - the strange logic of operation of application from a DB, is an occasion to reflect on a rewriting. Therefore the parameter idle_in_transaction_session_timeout is invented in 9.6 knowingly.
Nevertheless, generally it is impossible to state that transactions hanging in the status idle in transaction lead to swelling of tables.

8

Re: Whether idle in transaction (aborted) holds a DB picture?

Luzanov wrote:

in general I would answer an initial question that idle in transaction (aborted) holds nothing.
But it would be desirable to explain here what moment.
Often I hear that is simple idle in transaction influences on bloat, here and Maxim hints at it:
it is passed...
It would be desirable to understand in which image.
The matter is that simple experiments of this influence do not show (provided that transaction by default in READ COMMITTED).
The first session:

postgres=# create table t (id int);
CREATE TABLE
postgres=# insert into t values (1);
INSERT 0 1
postgres=# begin isolation level read committed;
BEGIN
postgres=# select *, pg_backend_pid () from t;
id | pg_backend_pid
----+----------------
1 | 3876
(1 row)

The second session:

postgres=# select state from pg_stat_activity where pid =3876;
state
---------------------
idle in transaction
(1 row)
postgres=# delete from t where id = 1;
DELETE 1
postgres=# vacuum verbose t;
INFO: vacuuming "public.t"
INFO: "t": removed 1 row versions in 1 pages
INFO: "t": found 1 removable, 0 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 122792
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "t": stopping truncate due to conflicting lock request
VACUUM

Apparently, hinders nothing to vacuum to clean the remote version of a line, though the first transaction in idle in transaction.
Swelling does not happen.
I suspect that life more richly, but where a dirty trick?

Good example, however, in most cases it not the true sad
If session changes something,  any more ...
If to interpose to
select *, pg_backend_pid () from t;
In your example it:

insert into t values (2);

That we receive:
The INFORMATION: cleaning "public.t"
The INFORMATION: "t": it is found deleted versions of lines: 0, not deleted - 2, it is processed pages: 1, all pages: 1
DETAIL: At present it is impossible to delete versions of "dead" lines: 1.
Not used pointers: 0.
Completely empty pages: 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
The request is successfully fulfilled without the returned data for 79 msec.

9

Re: Whether idle in transaction (aborted) holds a DB picture?

Or I am mistaken?...

10

Re: Whether idle in transaction (aborted) holds a DB picture?

And aborted and it did not turn out to make,
I open transaction:
begin isolation level read committed;
I make a syntactic mistake:
selectw
And the connection state turns in idle.