当前位置：首页 > news >正文

Postgresql的ddl在事务中可以回滚，truncate时relfilenode在当前会话会改变

news 来源：原创 2024/5/20 14:52:07

Postgresql的事务里面ddl可以回滚,这点和oracle不太一样。其中postgresql alter table事务操作中，包括回滚的整个过程中表对象的relfilenode不变，但是postgresql truncate事务操作中，一旦执行truncate操作表对象的relfilenode在当前会话就变了，其他会话查询表对象的relfilenode没有变化，truncate事务操作回滚后表对象的relfilenode在当前会话中也回滚了

Postgresql的事务里面ddl可以回滚，如下新增字段操作的ddl回滚后，新增的字段就消失了

postgres=# select * from emp;id  |  name  | position | managerid | yearmomthday | salary | ticheng | salper
------+--------+----------+-----------+--------------+--------+---------+--------7369 | SMITH  | CLERK    |      7902 | 1982-12-17   |    800 |         |     207499 | ALLEN  | SALESMAN |      7698 | 1981-02-20   |   1600 |     300 |     207521 | WARD   | SALESMAN |      7698 | 1981-02-22   |   1250 |     500 |     307566 | JONES  | MANAGER  |      7389 | 1981-04-02   |   2975 |         |     207654 | MARTIN | SALESMAN |      7698 | 1982-09-28   |   1250 |    1400 |     30
(5 rows)postgres=# select oid,relname,relfilenode from pg_class where relname='emp';oid  | relname | relfilenode
-------+---------+-------------16412 | emp     |       16412
(1 row)postgres=# begin;
BEGIN
postgres=# alter table emp add column lx varchar(100) default 'good';
ALTER TABLE
postgres=# select oid,relname,relfilenode from pg_class where relname='emp';oid  | relname | relfilenode
-------+---------+-------------16412 | emp     |       16412
(1 row)postgres=# select * from emp;id  |  name  | position | managerid | yearmomthday | salary | ticheng | salper |  lx
------+--------+----------+-----------+--------------+--------+---------+--------+------7369 | SMITH  | CLERK    |      7902 | 1982-12-17   |    800 |         |     20 | good7499 | ALLEN  | SALESMAN |      7698 | 1981-02-20   |   1600 |     300 |     20 | good7521 | WARD   | SALESMAN |      7698 | 1981-02-22   |   1250 |     500 |     30 | good7566 | JONES  | MANAGER  |      7389 | 1981-04-02   |   2975 |         |     20 | good7654 | MARTIN | SALESMAN |      7698 | 1982-09-28   |   1250 |    1400 |     30 | good
(5 rows)postgres=# rollback;
ROLLBACK
postgres=# select oid,relname,relfilenode from pg_class where relname='emp';oid  | relname | relfilenode
-------+---------+-------------16412 | emp     |       16412
(1 row)postgres=# select * from emp;id  |  name  | position | managerid | yearmomthday | salary | ticheng | salper
------+--------+----------+-----------+--------------+--------+---------+--------7369 | SMITH  | CLERK    |      7902 | 1982-12-17   |    800 |         |     207499 | ALLEN  | SALESMAN |      7698 | 1981-02-20   |   1600 |     300 |     207521 | WARD   | SALESMAN |      7698 | 1981-02-22   |   1250 |     500 |     307566 | JONES  | MANAGER  |      7389 | 1981-04-02   |   2975 |         |     207654 | MARTIN | SALESMAN |      7698 | 1982-09-28   |   1250 |    1400 |     30
(5 rows)

Postgresql的事务里面ddl可以回滚,如下truncate操作，回滚后，truncate的数据又回来了

testdb=# select * from emp;id  |  name  | position | managerid | yearmomthday | salary | ticheng | salper
------+--------+----------+-----------+--------------+--------+---------+--------7369 | SMITH  | CLERK    |      7902 | 1982-12-17   |    800 |         |     207499 | ALLEN  | SALESMAN |      7698 | 1981-02-20   |   1600 |     300 |     207521 | WARD   | SALESMAN |      7698 | 1981-02-22   |   1250 |     500 |     307566 | JONES  | MANAGER  |      7389 | 1981-04-02   |   2975 |         |     207654 | MARTIN | SALESMAN |      7698 | 1982-09-28   |   1250 |    1400 |     30
(5 rows)testdb=# select oid,relname,relfilenode from pg_class where relname='emp';oid  | relname | relfilenode
-------+---------+-------------16400 | emp     |       16400
(1 row)testdb=# begin;
BEGIN
testdb=# truncate table emp;
TRUNCATE TABLE
testdb=# select * from emp;id | name | position | managerid | yearmomthday | salary | ticheng | salper
----+------+----------+-----------+--------------+--------+---------+--------
(0 rows)testdb=# select oid,relname,relfilenode from pg_class where relname='emp';oid  | relname | relfilenode
-------+---------+-------------16400 | emp     |       24610
(1 row)testdb=# rollback;
ROLLBACK
testdb=# select oid,relname,relfilenode from pg_class where relname='emp';oid  | relname | relfilenode
-------+---------+-------------16400 | emp     |       16400
(1 row)testdb=# select * from emp;id  |  name  | position | managerid | yearmomthday | salary | ticheng | salper
------+--------+----------+-----------+--------------+--------+---------+--------7369 | SMITH  | CLERK    |      7902 | 1982-12-17   |    800 |         |     207499 | ALLEN  | SALESMAN |      7698 | 1981-02-20   |   1600 |     300 |     207521 | WARD   | SALESMAN |      7698 | 1981-02-22   |   1250 |     500 |     307566 | JONES  | MANAGER  |      7389 | 1981-04-02   |   2975 |         |     207654 | MARTIN | SALESMAN |      7698 | 1982-09-28   |   1250 |    1400 |     30
(5 rows)

truncate会话执行过程中ll -rt |grep 24610能查到relfilenode 24610

[root@centos7 16386]# ll -rt |grep 24610
-rw-------. 1 postgres postgres      0 Oct 29 20:55 24610

不过会话退出后不久，ll -rt |grep 24610就查不到对应的文件了

[root@centos7 16386]# ll -rt |grep 24610
[root@centos7 16386]#

在会话1执行truncate的过程中只要还没提交，其他会话查询到表的relfilenode不变

postgres=# select oid,relname,relfilenode from pg_class where relname='emp';oid  | relname | relfilenode
-------+---------+-------------16412 | emp     |       16412