A Second Step Into PostgreSQL Transactions
We started looking at PostgreSQL transactions last time, and now we can plunge onward. Populating a new table is an intense process. Besides the columns we declare, there are others created for us by the server. Please read the documentation on System Columns.
First Example
We start by creating a simple two-column table.
demo=# create table foo (a int, b int);
CREATE TABLE
demo=# insert into foo (a,b) values (1,2),(3,4),(5,6);
INSERT 0 3
demo=# select a,b from foo;
a | b
---+---
1 | 2
3 | 4
5 | 6
(3 rows)
CREATE TABLE
demo=# insert into foo (a,b) values (1,2),(3,4),(5,6);
INSERT 0 3
demo=# select a,b from foo;
a | b
---+---
1 | 2
3 | 4
5 | 6
(3 rows)
The server adds the system columns that can be seen if we explicitly select them.
demo=# select a, b, tableoid, xmin, cmin, xmax, cmax, ctid from foo;
a | b | tableoid | xmin | cmin | xmax | cmax | ctid
---+---+----------+------+------+------+------+-------
1 | 2 | 16401 | 776 | 0 | 0 | 0 | (0,1)
3 | 4 | 16401 | 776 | 0 | 0 | 0 | (0,2)
5 | 6 | 16401 | 776 | 0 | 0 | 0 | (0,3)
(3 rows)
demo=#
What are those columns?
The tableoid is the identifier of the table. We can get a great deal of data on our table by using the value of the oid to look into the pg_class catalog.
demo=# select oid, relname from pg_class where oid = 16401;
oid | relname
-------+---------
16401 | foo
(1 row)
For now, we will ignore the other columns. in the pg_class catalog.
The xmin column has the transaction ID that was inserted into the row. It is logically paired with xmax, which is a) the transaction ID of the deleting transaction and b) zero for an undeleted row version. Xmax can also be nonzero in a visible row version, which usually indicates that the deleting transaction hasn't been committed yet or that an attempted deletion was rolled back.
For transactions, cmin and cmax are used to track the command ID during the transaction. Multiple commands (insert, update, or delete commands) can touch a row during a transaction. Cmin starts at zero and increments from there. Cmax is either zero or the command ID of a delete.
Please refer to the previous blog for details on ctid;
Inside a Transaction
It is time to modify some data! But first, take a peek at the data before changes are made.
demo=# select a, b, xmin, cmin, xmax, cmax, ctid from foo;
a | b | xmin | cmin | xmax | cmax | ctid
---+---+------+------+------+------+-------
1 | 2 | 776 | 0 | 0 | 0 | (0,1)
3 | 4 | 776 | 0 | 0 | 0 | (0,2)
5 | 6 | 776 | 0 | 0 | 0 | (0,3)
(3 rows)
It is time to start the transaction itself. Then, we can check the current transaction ID by using txid_current().
demo=# start transaction;
START TRANSACTION
demo=*# select txid_current();
txid_current
--------------
778
(1 row)
Now, we can make changes to a row.
demo=*# update foo set a = 10 where b = 2;
UPDATE 1
Now, we look at the data again.
demo=*# select a, b, xmin, cmin, xmax, cmax, ctid from foo;
a | b | xmin | cmin | xmax | cmax | ctid
----+---+------+------+------+------+-------
3 | 4 | 776 | 0 | 0 | 0 | (0,2)
5 | 6 | 776 | 0 | 0 | 0 | (0,3)
10 | 2 | 778 | 0 | 0 | 0 | (0,4)
(3 rows)
Previously, the row changed to location ctid(0,1). Now the changed row is placed at location (0,4). The xmin was updated to the current transaction ID.
On A Separate Connection
Open up another connection to the server. Remember, this new view of the data is not inside the previous transaction. The transaction has not been committed, so the changed data is unavailable for this connection.
demo=# select a, b, xmin, cmin, xmax, cmax, ctid from foo;
a | b | xmin | cmin | xmax | cmax | ctid
---+---+------+------+------+------+-------
1 | 2 | 776 | 0 | 778 | 0 | (0,1)
3 | 4 | 776 | 0 | 0 | 0 | (0,2)
5 | 6 | 776 | 0 | 0 | 0 | (0,3)
(3 rows)
But for this second connection, note that there is a warning that something is going on by the xmax value. This is a notification that a deleting transaction has not been committed on this row.
Back To The First Connection
It is time to make more changes. A SAVEPOINT is issued to make a transaction within the current transaction. If something we do after declaring the SAVEPOINT is not wanted, we can 'roll back' to the SAVEPOINT without having to abort the entire transaction;
demo=*# savepoint a;
SAVEPOINT
demo=*# update foo set a = a * 10 where a < 10;
UPDATE 2
demo=*# select a, b, xmin, cmin, xmax, cmax, ctid from foo;
a | b | xmin | cmin | xmax | cmax | ctid
----+---+------+------+------+------+-------
10 | 2 | 778 | 0 | 0 | 0 | (0,4)
30 | 4 | 779 | 1 | 0 | 1 | (0,5)
50 | 6 | 779 | 1 | 0 | 1 | (0,6)
(3 rows)
There are now a lot of changes in the system columns. Quiz time! What is the current transaction ID?
It may surprise you that the transaction ID has not changed.
demo=*# select txid_current();
txid_current
--------------
778
(1 row)
We are still inside the original transaction that started with the START TRANSACTION command. The server uses cmin and cmax within a transaction, much like it uses xmin and xmax.
A Second Savepoint
To see how the xmin and xmax columns change within a transaction, we will set another SAVEPOINT.
demo=*# savepoint b;
SAVEPOINT
demo=*# update foo set a = a * 5 where a < 20;
UPDATE 1
demo=*# select a, b, xmin, cmin, xmax, cmax, ctid from foo;
a | b | xmin | cmin | xmax | cmax | ctid
----+---+------+------+------+------+-------
30 | 4 | 779 | 1 | 0 | 1 | (0,5)
50 | 6 | 779 | 1 | 0 | 1 | (0,6)
50 | 2 | 780 | 2 | 0 | 2 | (0,7)
(3 rows)
The row we did not change within Savepoint B at ctid (0,6) does not change. But the other two rows, the ones that were altered, have all their min and max columns incremented.
Unravel
If we undo the second save point, the data reverses. First, let us look at the as it is before any rollbacks.
demo=*# select a, b, xmin, cmin, xmax, cmax, ctid from foo;
a | b | xmin | cmin | xmax | cmax | ctid
----+---+------+------+------+------+-------
30 | 4 | 779 | 1 | 0 | 1 | (0,5)
50 | 6 | 779 | 1 | 0 | 1 | (0,6)
50 | 2 | 780 | 2 | 0 | 2 | (0,7)
(3 rows)
Now, we issue a rollback to the first savepoint, savepoint a.
demo=*# rollback to savepoint a;
ROLLBACK
demo=*# select a, b, xmin, cmin, xmax, cmax, ctid from foo;
a | b | xmin | cmin | xmax | cmax | ctid
----+---+------+------+------+------+-------
3 | 4 | 776 | 1 | 779 | 1 | (0,2)
5 | 6 | 776 | 1 | 779 | 1 | (0,3)
10 | 2 | 778 | 0 | 780 | 0 | (0,4)
(3 rows)
And finally, we rollback to before the START transaction.
demo=*# rollback;
ROLLBACK
demo=# select a, b, xmin, cmin, xmax, cmax, ctid from foo;
a | b | xmin | cmin | xmax | cmax | ctid
---+---+------+------+------+------+-------
1 | 2 | 776 | 0 | 778 | 0 | (0,1)
3 | 4 | 776 | 1 | 779 | 1 | (0,2)
5 | 6 | 776 | 1 | 779 | 1 | (0,3)
What About The Transaction ID?
The transaction ID does not 'roll back.' It would be dangerous to return to the original number because other transactions may be ongoing, and reusing the number could cause catastrophic problems. The transaction ID counter is a 32-bit integer that could roll over if the system has a lot of transactions. We will look at transaction ID wrap-around issues later.
demo=# select txid_current();
txid_current
--------------
781
(1 row)
Next Time
So what happens to those rows that are replaced during a transaction?
Comments
Post a Comment