openGauss每日一练第16天-事务控制

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=#
omm=# start transaction;
START TRANSACTION
omm=# select * from pg_class limit 1;
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relal
lvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relhasindex | relisshared
| relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | rel
cmprs | relhasclusterkey | relrowmovement | parttype | relfrozenxid | relacl | reloptions | relreplident | relfrozenxid
64 | relbucket | relbucketkey
--------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+------
---------+---------------+---------------+---------------+-------------+----------------+--------------+-------------+------------
-+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+----
------+------------------+----------------+----------+--------------+-------------------+------------+--------------+-------------
---+-----------+--------------
pg_statistic | 11 | 11334 | 0 | 10 | 0 | 13689 | 0 | 18 | 476 |
18 | 2840 | 0 | 0 | 0 | 0 | 0 | t | f
| p | r | 29 | 0 | f | f | f | f | f | 0
| f | f | n | 0 | {omm=arwdDxt/omm} | | n | 78
91 | |
(1 row)

omm=# end;
COMMIT
omm=# omm=#
omm=#

omm=# begin;
WARNING: Session unused timeout.
FATAL: terminating connection due to administrator command
could not send data to server: Broken pipe
The connection to the server was lost. Attempting reset: Succeeded.
omm=# ^C
omm=# quit
omm-# \q
omm@modb:~$
omm@modb:~$
omm@modb:~$ gsql -r
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# START TRANSACTION;
START TRANSACTION
omm=# select * from pg_class limit 1;
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relal
lvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relhasindex | relisshared
| relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | rel
cmprs | relhasclusterkey | relrowmovement | parttype | relfrozenxid | relacl | reloptions | relreplident | relfrozenxid
64 | relbucket | relbucketkey
--------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+------
---------+---------------+---------------+---------------+-------------+----------------+--------------+-------------+------------
-+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+----
------+------------------+----------------+----------+--------------+-------------------+------------+--------------+-------------
---+-----------+--------------
| p | r | 29 | 0 | f | f | f | f | f | 0
| f | f | n | 0 | {omm=arwdDxt/omm} | | n | 78
91 | | pg_statistic | 11 | 11334 | 0 | 10 | 0 | 13689 | 0 | 18 | 476 |
18 | 2840 | 0 | 0 | 0 | 0 | 0 | t | f

(1 row)

omm=# END；
omm-# BEGIN;
ERROR: syntax error at or near "END；"
LINE 1: END；
^
omm=# END;
ROLLBACK
omm=#
omm=#
omm=#
omm=# BEGIN;
BEGIN
omm=# SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;
SET
omm=# show transaction_read_only;
transaction_read_only
-----------------------
on
(1 row)

omm=# select * from pg_class limit 1;
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relal
lvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relhasindex | relisshared
| relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | rel
cmprs | relhasclusterkey | relrowmovement | parttype | relfrozenxid | relacl | reloptions | relreplident | relfrozenxid
64 | relbucket | relbucketkey
--------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+------
---------+---------------+---------------+---------------+-------------+----------------+--------------+-------------+------------
---+-----------+--------------
pg_statistic | 11 | 11334 | 0 | 10 | 0 | 13689 | 0 | 18 | 476 |
18 | 2840 | 0 | 0 | 0 | 0 | 0 | t | f
-+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+----
------+------------------+----------------+----------+--------------+-------------------+------------+--------------+-------------
| f | f | n | 0 | {omm=arwdDxt/omm} | | n | 78
91 | |
(1 row)

| p | r | 29 | 0 | f | f | f | f | f | 0
omm=# create schema tpcds10;
ERROR: cannot execute CREATE SCHEMA in a read-only transaction
omm=# commit;
ROLLBACK
omm=# show transaction_isolation;
transaction_isolation
-----------------------
read committed
(1 row)

omm=# START TRANSACTION ISOLATION LEVEL repeatable read READ WRITE;
START TRANSACTION
omm=# show transaction_isolation;
transaction_isolation
-----------------------
repeatable read
(1 row)

omm=# show transaction_read_only;
transaction_read_only
-----------------------
off
(1 row)

omm=# select * from pg_class limit 1;
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relal
lvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relhasindex | relisshared
| relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | rel
cmprs | relhasclusterkey | relrowmovement | parttype | relfrozenxid | relacl | reloptions | relreplident | relfrozenxid
64 | relbucket | relbucketkey
--------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+------
---------+---------------+---------------+---------------+-------------+----------------+--------------+-------------+------------
-+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+----
------+------------------+----------------+----------+--------------+-------------------+------------+--------------+-------------
---+-----------+--------------
pg_statistic | 11 | 11334 | 0 | 10 | 0 | 13689 | 0 | 18 | 476 |
| f | f | n | 0 | {omm=arwdDxt/omm} | | n | 78
91 | |
(1 row)

18 | 2840 | 0 | 0 | 0 | 0 | 0 | t | f
| p | r | 29 | 0 | f | f | f | f | f | 0
omm=# create schema tpcds10;
CREATE SCHEMA
omm=# \dn+ tpcds10
List of schemas
Name | Owner | Access privileges | Description
---------+-------+-------------------+-------------
tpcds10 | omm | |
(1 row)

omm=# rollback;
omm=# ROLLBACK

omm=# \dn+ tpcds10
List of schemas
Name | Owner | Access privileges | Description
------+-------+-------------------+-------------
(0 rows)

omm=# CREATE TABLE table1(a int);
CREATE TABLE
omm=# START TRANSACTION;
START TRANSACTION
omm=# INSERT INTO table1 VALUES (1);
INSERT 0 1
omm=# SAVEPOINT my_savepoint;
SAVEPOINT
omm=# INSERT INTO table1 VALUES (2);
INSERT 0 1
omm=# select * from table1;
(2 rows)

omm=# a
---
1
2

omm=# ROLLBACK TO SAVEPOINT my_savepoint;
ROLLBACK
omm=# INSERT INTO table1 VALUES (3);
INSERT 0 1
omm=# COMMIT;
COMMIT
omm=# SELECT * FROM table1;
a
---
1
3
(2 rows)

omm=#