So, we have discussed issues related to , and we took a detour about . And finally, we have reached the most interesting part — the versions of rows.
The header
As we mentioned earlier, each row can simultaneously exist in multiple versions within the database. One version must somehow be distinguished from another. To this end, each version has two markers that define the "time" of validity for that version (xmin and xmax). In quotes, because it is not time per se, but a special incrementing counter. And this counter is the transaction number.
(As usual, in reality, it is more complex: the transaction numbers cannot continuously increase due to the limited bit-width of the counter. But we will look at these details in depth when we get to freezing.)
When a row is created, the xmin value is set to the transaction number that executed the INSERT command, while the xmax remains unfilled.
When a row is deleted, the xmax value of the current version is marked with the transaction number that executed the DELETE.
When a row is modified using the UPDATE command, in fact, two operations are performed: DELETE and INSERT. The current version of the row is assigned an xmax equal to the transaction number that executed the UPDATE. Then a new version of that same row is created; its xmin value matches the xmax value of the previous version.
The xmin and xmax fields are part of the row version header. In addition to these fields, the header contains others, such as:
- infomask — a series of bits that define the properties of this version. There are quite a few; we will gradually review the main ones.
- ctid — a link to the next, newer version of the same row. For the newest, most current version of the row, the ctid points to this version itself. The number has the form (x,y), where x is the page number, and y is the pointer's ordinal number in the array.
- bitmap of undefined values — marks those columns in this version that contain an undefined value (NULL). NULL is not one of the ordinary values of data types, so this characteristic has to be stored separately.
As a result, the header becomes quite large — at least 23 bytes for each row version, and usually more due to the NULL bitmap. If the table is "narrow" (i.e., contains few columns), the overhead may occupy more space than useful information.
Insertion
Let's take a closer look at how low-level string operations are performed, starting with insertion.
For experiments, we'll create a new table with two columns and an index on one of them:
=> CREATE TABLE t(
id serial,
s text
);
=> CREATE INDEX ON t(s);
We'll insert one row, having first started a transaction.
=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');
Here is the number of our current transaction:
=> SELECT txid_current();
txid_current
--------------
3664
(1 row)
Let's take a look at the content of the page. The heap_page_items function from the pageinspect extension allows us to get information about the pointers and versions of rows:
=> SELECT * FROM heap_page_items(get_raw_page('t',0)) gx
-[ RECORD 1 ]-------------------
lp | 1
lp_off | 8160
lp_flags | 1
lp_len | 32
t_xmin | 3664
t_xmax | 0
t_field3 | 0
t_ctid | (0,1)
t_infomask2 | 2
t_infomask | 2050
t_hoff | 24
t_bits |
t_oid |
t_data | x0100000009464f4f
It should be noted that the term heap in PostgreSQL refers to tables. This is yet another strange usage of the term — a heap is a well-known , which has nothing in common with a table. Here, this word is used in the sense of "everything is thrown into a heap," unlike ordered indexes.
The function shows the data "as is," in a format that is complex to perceive. To understand, let's keep only part of the information and decode it:
=> SELECT '(0,'||lp||')' AS ctid,
CASE lp_flags
WHEN 0 THEN 'unused'
WHEN 1 THEN 'normal'
WHEN 2 THEN 'redirect to '||lp_off
WHEN 3 THEN 'dead'
END AS state,
t_xmin as xmin,
t_xmax as xmax,
(t_infomask & 256) > 0 AS xmin_commited,
(t_infomask & 512) > 0 AS xmin_aborted,
(t_infomask & 1024) > 0 AS xmax_commited,
(t_infomask & 2048) > 0 AS xmax_aborted,
t_ctid
FROM heap_page_items(get_raw_page('t',0)) gx
-[ RECORD 1 ]-+-------
ctid | (0,1)
state | normal
xmin | 3664
xmax | 0
xmin_commited | f
xmin_aborted | f
xmax_commited | f
xmax_aborted | t
t_ctid | (0,1)
Here’s what we did:
- We added a zero to the pointer number to format it like t_ctid: (page number, pointer number).
- We decoded the state of the lp_flags pointer. Here it is "normal" — this means that the pointer indeed refers to a version of the row. Other values will be considered later.
- From all the informational bits, we highlighted only two pairs for now. The bits xmin_committed and xmin_aborted indicate whether the transaction with the xmin number has been committed (or aborted). Two similar bits pertain to the transaction with the xmax number.
What do we see? When inserting a row in a table page, a pointer with the number 1 will appear, referencing the first and only version of the row.
In the version of the row, the xmin field is filled with the number of the current transaction. The transaction is still active, so both bits xmin_committed and xmin_aborted are not set.
The ctid field of the row version refers to the same row. This means that there is no newer version.
The xmax field is filled with a dummy number 0, as this version of the row is not deleted and is current. Transactions will not pay attention to this number since the xmax_aborted bit is set.
Let's take one more step to improve readability by adding informational bits to the transaction numbers. We will create a function, as we will need this query more than once:
=> CREATE FUNCTION heap_page(relname text, pageno integer)
RETURNS TABLE(ctid tid, state text, xmin text, xmax text, t_ctid tid)
AS $$
SELECT (pageno,lp)::text::tid AS ctid,
CASE lp_flags
WHEN 0 THEN 'unused'
WHEN 1 THEN 'normal'
WHEN 2 THEN 'redirect to '||lp_off
WHEN 3 THEN 'dead'
END AS state,
t_xmin || CASE
WHEN (t_infomask & 256) > 0 THEN ' (c)'
WHEN (t_infomask & 512) > 0 THEN ' (a)'
ELSE ''
END AS xmin,
t_xmax || CASE
WHEN (t_infomask & 1024) > 0 THEN ' (c)'
WHEN (t_infomask & 2048) > 0 THEN ' (a)'
ELSE ''
END AS xmax,
t_ctid
FROM heap_page_items(get_raw_page(relname,pageno))
ORDER BY lp;
$$ LANGUAGE SQL;
In this form, it is much clearer what is happening in the header of the row version:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
Similar, but significantly less detailed information can also be obtained from the table itself using the pseudo-columns xmin and xmax:
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3664 | 0 | 1 | FOO
(1 row)
Commit
Upon successful completion of a transaction, its status needs to be remembered — to mark it as committed. This is done using a structure called XACT (which until version 10 was called CLOG (commit log), and this name may still be encountered in various places).
XACT is not a system catalog table; these are files in the PGDATA/pg_xact directory. For each transaction, two bits are allocated: committed and aborted — just like in the header of the row version. This information is split across several files purely for convenience; we will return to this topic when we discuss freezing. The work with these files is done page by page, just like with all others.
So, when a transaction is committed in XACT, the committed bit is set for that transaction. And that's all that happens during the commit (though we are not yet talking about the write-ahead log).
When another transaction accesses the table page we just viewed, it will need to answer a few questions.
- Has the transaction xmin completed? If not, the created version of the row should not be visible.
Such a check is performed by viewing another structure that resides in the shared memory of the instance called ProcArray. It contains a list of all active processes, and for each one, the number of its current (active) transaction is specified. - If it has completed, then how — by commit or rollback? If by rollback, then the version of the row should also not be visible.
This is exactly what XACT is needed for. However, although the last pages of XACT are stored in buffers in RAM, checking XACT every time is costly. Therefore, once determined, the status of the transaction is recorded in the xmin_committed and xmin_aborted bits of the row version. If one of these bits is set, then the status of transaction xmin is considered known, and the next transaction will not have to refer to XACT.
Why aren’t these bits set by the transaction performing the insert? When the insert occurs, the transaction does not yet know if it will complete successfully. And at the moment of commit, it’s unclear exactly which rows in which pages were changed. There can be many such pages, and remembering them is inefficient. Moreover, some pages may have been flushed from the buffer cache to disk; reading them back into memory to change the bits would significantly slow down the commit.
The downside of this economy is that after changes, any transaction (even one performing a simple read — SELECT) can start modifying data pages in the buffer cache.
So, let’s commit the change.
=> COMMIT;
Nothing has changed on the page (but we know that the status of the transaction is already recorded in XACT):
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3664 | 0 (a) | (0,1)
(1 row)
Now, the transaction that first accessed the page will need to determine the status of transaction xmin and will record it in the informational bits:
=> SELECT * FROM t;
id | s
----+-----
1 | FOO
(1 row)
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+-------+--------
(0,1) | normal | 3664 (c) | 0 (a) | (0,1)
(1 row)
Deletion
When a row is deleted, the current transaction ID is recorded in the xmax field, and the xmax_aborted bit is reset.
Note that the set xmax value corresponding to the active transaction acts as a row lock. If another transaction attempts to update or delete this row, it must wait for the xmax transaction to complete. We will discuss locks in more detail later. For now, just note that the number of row locks is unlimited. They do not occupy space in RAM, and system performance is not affected by their quantity. However, 'long' transactions have other drawbacks, which we will cover later.
Let's delete the row.
=> BEGIN;
=> DELETE FROM t;
=> SELECT txid_current();
txid_current
--------------
3665
(1 row)
We see that the transaction number has been recorded in the xmax field, but the informational bits are not set:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
Rollback
Canceling changes works similarly to committing; however, in XACT for the transaction, the aborted bit is set. The rollback occurs just as quickly as the commit. Although the command is called ROLLBACK, no actual rollback of changes takes place: everything the transaction managed to change in the data pages remains unchanged.
=> ROLLBACK;
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+------+--------
(0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 row)
When accessing the page, the status will be checked, and the xmax_aborted hint bit will be set in the row version. The xmax number remains on the page, but no one will pay attention to it.
=> SELECT * FROM t;
id | s
----+-----
1 | FOO
(1 row)
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+----------+--------
(0,1) | normal | 3664 (c) | 3665 (a) | (0,1)
(1 row)
Upgrade
The update works as if the current version of the row was first deleted, and then a new one was inserted.
=> BEGIN;
=> UPDATE t SET s = 'BAR';
=> SELECT txid_current();
txid_current
--------------
3666
(1 row)
The query returns one row (the new version):
=> SELECT * FROM t;
id | s
----+-----
1 | BAR
(1 row)
But on the page, we see both versions:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+-------+--------
(0,1) | normal | 3664 (c) | 3666 | (0,2)
(0,2) | normal | 3666 | 0 (a) | (0,2)
(2 rows)
The deleted version is marked with the current transaction number in the xmax field. This value is written over the old one since the previous transaction was aborted. The xmax_aborted bit is reset because the status of the current transaction is still unknown.
The first version of the row now references the second (in the t_ctid field) as the newer version.
A second pointer and a second row appear on the index page, linking to the second version on the table page.
Similarly to deletion, the xmax value in the first version of the row indicates that the row is locked.
And we will complete the transaction.
=> COMMIT;
Indexes
Until now, we've only talked about table pages. What happens inside the indexes?
The information in index pages heavily depends on the specific type of index. Even within one type of index, there can be different types of pages. For example, a B-tree has a metadata page and 'regular' pages.
However, there is usually an array of pointers to rows and the rows themselves on the page (just like in a table page). Additionally, space at the end of the page is reserved for special data.
Rows in indexes can also have very different structures depending on the type of index. For example, in a B-tree, the rows related to leaf pages contain the index key value and a reference (ctid) to the corresponding table row. In general, an index can be designed quite differently.
The most important point is that there are no row versions in indexes of any type. Or one might argue that each row is represented by exactly one version. In other words, the index row header does not contain xmin and xmax fields. It can be assumed that references in the index link to all table versions of the rows — thus, determining which version a transaction will see can only be done by looking into the table. (As usual, this is not the whole truth. In some cases, the visibility map allows for optimizing the process, but we will look into this in more detail later.)
In this case, the index page reveals pointers to both versions, both the current and the old:
=> SELECT itemoffset, ctid FROM bt_page_items('t_s_idx',1);
itemoffset | ctid
------------+-------
1 | (0,2)
2 | (0,1)
(2 rows)
Virtual transactions
In practice, PostgreSQL uses an optimization that allows it to 'save' transaction numbers.
If a transaction only reads data, it does not affect the visibility of row versions. Thus, initially, the service process assigns a virtual number (virtual xid) to transactions. The number consists of a process identifier and a sequential number.
Issuing this number does not require synchronization between all processes and therefore occurs very quickly. We will learn about another reason for using virtual numbers when we discuss freezing.
Virtual numbers are not accounted for in data snapshots.
At different points in time, the system may have virtual transactions with numbers that have already been used, and this is normal. However, such a number cannot be recorded in data pages because it may lose all meaning upon the next access to the page.
=> BEGIN;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
(1 row)
If the transaction starts to modify data, it is assigned a real, unique transaction number.
=> UPDATE accounts SET amount = amount - 1.00;
=> SELECT txid_current_if_assigned();
txid_current_if_assigned
--------------------------
3667
(1 row)
=> COMMIT;
Nested transactions
Savepoints
In SQL, savepoints are defined savepoints (savepoint), which allow part of a transaction's operations to be rolled back without fully terminating it. But this does not fit into the scheme outlined above, since there is only one status for all changes of the transaction, and no data is physically rolled back.
To implement such functionality, a transaction with a savepoint is split into several separate nested transactions (subtransaction), whose status can be managed separately.
Nested transactions have their own number (greater than the main transaction number). The status of nested transactions is recorded in the usual way in XACT, but the final status depends on the status of the main transaction: if it is aborted, all nested transactions are also aborted.
Information about the nesting of transactions is stored in files in the PGDATA/pg_subtrans directory. Access to the files occurs through buffers in the shared memory of the instance, organized similarly to XACT buffers.
Do not confuse nested transactions with autonomous transactions. Autonomous transactions do not depend on each other, while nested ones do. There are no autonomous transactions in regular PostgreSQL, and perhaps that is for the best: they are rarely needed and their presence in other DBMSs leads to abuse, which then affects everyone.
Let's clear the table, start a transaction, and insert a row:
=> TRUNCATE TABLE t;
=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');
=> SELECT txid_current();
txid_current
--------------
3669
(1 row)
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3669 | 0 | 2 | FOO
(1 row)
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3669 | 0 (a) | (0,1)
(1 row)
Now we will set a savepoint and insert another row.
=> SAVEPOINT sp;
=> INSERT INTO t(s) VALUES ('XYZ');
=> SELECT txid_current();
txid_current
--------------
3669
(1 row)
Note that the txid_current() function returns the ID of the outer transaction, not the inner transaction.
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3669 | 0 | 2 | FOO
3670 | 0 | 3 | XYZ
(2 rows)
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+------+-------+--------
(0,1) | normal | 3669 | 0 (a) | (0,1)
(0,2) | normal | 3670 | 0 (a) | (0,2)
(2 rows)
We will rollback to the savepoint and insert a third row.
=> ROLLBACK TO sp;
=> INSERT INTO t(s) VALUES ('BAR');
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3669 | 0 | 2 | FOO
3671 | 0 | 4 | BAR
(2 rows)
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+-------+--------
(0,1) | normal | 3669 | 0 (a) | (0,1)
(0,2) | normal | 3670 (a) | 0 (a) | (0,2)
(0,3) | normal | 3671 | 0 (a) | (0,3)
(3 rows)
On the page, we still see the row added by the canceled nested transaction.
We commit the changes.
=> COMMIT;
=> SELECT xmin, xmax, * FROM t;
xmin | xmax | id | s
------+------+----+-----
3669 | 0 | 2 | FOO
3671 | 0 | 4 | BAR
(2 rows)
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+-------+--------
(0,1) | normal | 3669 (c) | 0 (a) | (0,1)
(0,2) | normal | 3670 (a) | 0 (a) | (0,2)
(0,3) | normal | 3671 (c) | 0 (a) | (0,3)
(3 rows)
It is now clear that each nested transaction has its own state.
Note that nested transactions cannot be explicitly used in SQL, meaning you cannot start a new transaction without finishing the current one. This mechanism is implicitly utilized when using savepoints, and also during PL/pgSQL exception handling and in several other, more exotic cases.
=> BEGIN;
BEGIN
=> BEGIN;
WARNING: there is already a transaction in progress
BEGIN
=> COMMIT;
COMMIT
=> COMMIT;
WARNING: there is no transaction in progress
COMMIT
Errors and atomicity of operations
What happens if an error occurs during an operation? For example:
=> BEGIN;
=> SELECT * FROM t;
id | s
----+-----
2 | FOO
4 | BAR
(2 rows)
=> UPDATE t SET s = repeat('X', 1/(id-4));
ERROR: division by zero
An error has occurred. The transaction is now considered aborted, and no operations are allowed within it:
=> SELECT * FROM t;
ERROR: current transaction is aborted, commands ignored until end of transaction block
And even if you try to commit the changes, PostgreSQL will inform you of the rollback:
=> COMMIT;
ROLLBACK
Why can't the transaction processing continue after a failure? The issue is that an error could arise in such a way that we would gain access to part of the changes — the atomicity would be violated not just of the transaction, but of the operator as well. As in our example, where the operator managed to update one row before the error occurred:
=> SELECT * FROM heap_page('t',0);
ctid | state | xmin | xmax | t_ctid
-------+--------+----------+-------+--------
(0,1) | normal | 3669 (c) | 3672 | (0,4)
(0,2) | normal | 3670 (a) | 0 (a) | (0,2)
(0,3) | normal | 3671 (c) | 0 (a) | (0,3)
(0,4) | normal | 3672 | 0 (a) | (0,4)
(4 rows)
It should be noted that psql has a mode that allows continuing the transaction processing after a failure as if the actions of the erroneous operator are rolled back.
=> set ON_ERROR_ROLLBACK on
=> BEGIN;
=> SELECT * FROM t;
id | s
----+-----
2 | FOO
4 | BAR
(2 rows)
=> UPDATE t SET s = repeat('X', 1/(id-4));
ERROR: division by zero
=> SELECT * FROM t;
id | s
----+-----
2 | FOO
4 | BAR
(2 rows)
=> COMMIT;
It’s easy to guess that in such a mode, psql effectively places an implicit savepoint before each command, and in the event of a failure, initiates a rollback to it. This mode is not used by default, as setting savepoints (even without rolling back to them) entails significant overhead.
Source: habr.com
