Zhenzhong Li joined Qunar in August 2020 and is mainly responsible for the operation and maintenance of PostgreSQL and Oracle.
1. Problem raising
There are often scenarios where DBAs need to examine what SQL is present in a particular transaction. A typical case is that DB has a lock alert, and SQL has an UPDATE wait and SQL has a SELECT. If you’re familiar with PostgreSQL, you’ll know that PostgreSQL uses a unique MVCC mechanism to prevent reads from blocking writes and writes from blocking reads.
So why does this lock happen?
The actual reason is that this select is in a transaction where other update/ DELETE writes hold the lock required by subsequent update operations in other transactions. At this point, it is necessary to check the complete SQL of the transaction in which the select is located in DB log and give feedback to the development students of the business line for further optimization.
Log Parameters
Now that you need to look at DB log, let’s first look at the configuration of parameters related to log.
PostgreSQL log configuration parameters are as follows:
This article focuses on several parameters commonly used in production: logging_collector, log_min_duration_statement, log_statement, log_duration, and log_line_prefix.
2.1 logging_collector
The log file can be recorded only when the parameter is set to ON. The change takes effect after a restart.
2.2 log_min_duration_statement
Possible values and meanings are as follows:
| The values | meaning |
|---|---|
| – 1 | Shut down |
| 0 | Record all statements and their runtime |
| > 0 | Only statements that exceed this threshold are recorded |
2.3 log_statement
Optional values are None, DDL, mod, and all. When log_min_DURation_statement >0, this parameter determines which level of statements to record. When log_min_DURation_statement =0, it does not affect whether to record all logs.
2.4 log_duration
Whether to record the time of execution of each completed statement. The value can be on or off.
2.5 log_line_prefix
Log_line_prefix is a printf-style string printed at the beginning of each line in the log. The meanings of escape parameters are as follows:
The session ID is a unique identifier for each session. It is two 4-byte hexadecimal numbers (without leading zeros), separated by periods. The value is the session start time and the process ID, so it can also be used as a space-saving way to print these items.
For example, to generate a session identifier from table pgStatActivity, use the following query statement:
SELECT to_hex(trunc(EXTRACT(EPOCH FROM backend_start))::integer) || '.' ||
to_hex(pid)
FROM pg_stat_activity;
Copy the codeWhen multiple transactions are executed concurrently, which statement is associated with which PID /session, which virtual transaction is associated with which transaction, and which transaction is associated with which transaction. Which transaction it was, and if we don’t record that information, we can’t find out.
Therefore, you must set sufficient parameters to the log_line_prefix prefix when you need to check logs for a specific problem.
To locate SQL in a transaction, focus on %p % C % L % V %x.
3. Log Configures case
Log_min_duration_statement, log_statement, and log_duration affect each other.
Due to space constraints, this article only lists two common production cases to look at DB Log recording examples.
3.1 Case 1 Full log mode
The full log mode is usually enabled when a problem needs to be investigated.
Note: There is not only one configuration for the full log mode, but only one is listed here.
The parameters are set as follows:
postgres@localhost ~$PSQL mydbpsql (12.3) Type "help" for help.mydb =# show logging_collector; logging_collector ------------------- on (1 row) mydb=# show log_min_duration_statement; log_min_duration_statement ---------------------------- 0 (1 row) mydb=# show log_statement; log_statement --------------- none (1 row) postgres=# show log_duration; log_duration -------------- off (1 row) mydb=# show log_line_prefix; log_line_prefix --------------------------------- [%u %d %a %h %m %p %c %l %v %x] (1 row)Copy the codeSQL Execution
postgres@localhost ~$PSQL mydbpsql (12.3) Type "help" for help.mydb =# select pg_backend_pid(); pg_backend_pid ---------------- 7106 (1 row) mydb=# begin; BEGIN mydb=# select 1; ? column? ---------- 1 (1 row) mydb=# create table if not exists test(id int); NOTICE: relation "test" already exists, skipping CREATE TABLE mydb=# truncate table test; TRUNCATE TABLE mydb=# commit; COMMIT mydb=# mydb=# begin; BEGIN mydb=# select * from test; id ---- (0 rows) mydb=# insert into test select 1; INSERT 0 1 mydb=# insert into test select 2; INSERT 0 1 mydb=# select * from test; id---- 1 2 (2 rows) mydb=# commit; COMMIT mydb=# mydb=# begin; BEGIN mydb=# select 1; ? column? ---------- 1 (1 row) mydb=# select * from test; id ---- 1 2 (2 rows) mydb=# update test set id = -1 where id =0; UPDATE 0 mydb=# update test set id = -2 where id =1; UPDATE 1 mydb=# select * from test; id ---- 2 -2 (2 rows) mydb=# rollback; ROLLBACK mydb=# mydb=# begin; BEGIN mydb=# update test set id = -1 where id =0; UPDATE 0 mydb=# rollback; ROLLBACK mydb=# mydb=# begin; BEGIN mydb=# update test set id = -2 where id =1; UPDATE 1 mydb=# rollback; ROLLBACK mydb=# mydb=# drop table if exists test; DROP TABLE mydb=#Copy the codeDB log Records information
[postgres mydb PSQL [local] 2021-03-29 16:58:59.378 CST 7106 606196D2.1bc2 1 5/0 0]LOG: duration: 0.770ms Statement: select pg_backend_pid(); [postgres mydb PSQL [local] 2021-03-29 16:58:59.379 CST 7106 606196D2.1bc225/17 0]LOG: duration: 0.057ms Statement: begin; [postgres mydb PSQL [local] 2021-03-29 16:58:59.380 CST 7106 606196D2.1bc2 3 5/17 0]LOG: duration: 0.121ms Statement: select 1; [postgres mydb PSQL [local] 2021-03-29 16:58:59.386 CST 7106 606196D2.1bc2 4 5/17 0]LOG: duration: 5.12ms Statement: create table if not exists test(id int); [postgres mydb PSQL [local] 2021-03-29 16:58:59.388 CST 7106 606196D2.1bc2.5 5/17959] 1.112 MS Statement: TRUNCate table test; [postgres mydb PSQL [local] 2021-03-29 16:58:59.391 CST 7106 606196D2.1bc2 65/0 0]LOG: duration: 1.916ms Statement: commit; [postgres mydb PSQL [local] 2021-03-29 16:58:59.392 CST 7106 606196D2.1bc275/180]LOG: duration: 0.058ms Statement: begin; [postgres mydb PSQL [local] 2021-03-29 16:58:59.393 CST 7106 606196D2.1bc2 8 5/18 0]LOG: duration: 0.548 ms Statement: select * from test; [postgres mydb PSQL [local] 2021-03-29 16:58:59.395 CST 7106 606196D2.1bc2 9 5/18 960] Statement: insert into test select 1; [postgres mydb PSQL [local] 2021-03-29 16:58:59.396 CST 7106 606196D2.1bc2 10 5/18 960] Statement: insert into test select 2; [postgres mydb PSQL [local] 2021-03-29 16:58:59.397 CST 7106 606196D2.1bc2 11 5/18 960] Statement: select * from test; [postgres mydb PSQL [local] 2021-03-29 16:58:59.399 CST 7106 606196D2.1bc2 125/0 0]LOG: duration: 1.194ms Statement: commit; [postgres mydb PSQL [local] 2021-03-29 16:58:59.400 CST 7106 606196D2.1bc2 135/19 0]LOG: duration: 0.050ms Statement: begin; [postgres mydb PSQL [local] 2021-03-29 16:58:59.400 CST 7106 606196D2.1bc2 145/19 0]LOG: duration: 0.222ms Statement: select 1; [postgres mydb PSQL [local] 2021-03-29 16:58:59.402 CST 7106 606196D2.1bc2 15 5/19 0]LOG: duration: 0.197ms Statement: select * from test; [postgres mydb PSQL [local] 2021-03-29 16:58:59.404 CST 7106 606196D2.1bc2 165/190]LOG: duration: 0.934 ms Statement: update test set id = -1 where id =0; [postgres mydb PSQL [local] 2021-03-29 16:58:59.406 CST 7106 606196D2.1bc2 175/19 961] Statement: update test set ID = -2 where ID =1; [postgres mydb PSQL [local] 2021-03-29 16:58:59.407 CST 7106 606196D2.1bc2 185/19 961] Statement: select * from test; [postgres mydb PSQL [local] 2021-03-29 16:58:59.407 CST 7106 606196D2.1bc2 195/0 0]LOG: duration: 0.06ms Statement: rollback; [postgres mydb PSQL [local] 2021-03-29 16:58:59.408 CST 7106 606196D2.1bc2 20 5/20]LOG: duration: 0.036ms Statement: begin; [postgres mydb PSQL [local] 2021-03-29 16:58:59.410 CST 7106 606196D2.1bc2 21 5/20 0]LOG: duration: 0.233ms Statement: update test set id = -1 where id =0; [postgres mydb PSQL [local] 2021-03-29 16:58:59.411 CST 7106 606196D2.1bc2225/0 0]LOG: duration: 0.053ms Statement: rollback; [postgres mydb PSQL [local] 2021-03-29 16:58:59.411 CST 7106 606196D2.1bc2 235/21 0]LOG: duration: 0.032ms Statement: begin; [postgres mydb PSQL [local] 2021-03-29 16:58:59.413 CST 7106 606196D2.1bc2 24 5/21 962] 0.457 MS Statement: update test set ID = -2 where ID =1; [postgres mydb PSQL [local] 2021-03-29 16:58:59.414 CST 7106 606196D2.1bc2 255/0 0]LOG: duration: 0.076ms Statement: rollback; [postgres mydb PSQL [local] 2021-03-29 16:58:59.428 CST 7106 606196D2.1bc2 265/0 0]LOG: duration: 12.573 ms Statement: drop table if exists test;Copy the code3.2 Case 2 Record full write && Partial read modes
Full write && partial read is the most commonly used log configuration mode in production. The configuration mode is more flexible.
The parameters are set as follows:
postgres@localhost ~$PSQLPSQL (12.3) Type "help" for help.postgres =# show logging_collector; logging_collector ------------------- on (1 row) postgres=# show log_min_duration_statement ; log_min_duration_statement ---------------------------- 1ms (1 row) postgres=# show log_statement; log_statement --------------- mod (1 row) postgres=# show log_duration ; log_duration -------------- off (1 row) mydb=# show log_line_prefix ; log_line_prefix --------------------------------- [%u %d %a %h %m %p %c %l %v %x] (1 row)Copy the codeSQL Execution
postgres@localhost ~$PSQL mydbpsql (12.3) Type "help" for help.mydb =# select pg_backend_pid(); pg_backend_pid ---------------- 7234 (1 row) mydb=# begin; BEGIN mydb=# select 1; ? column? ---------- 1 (1 row) mydb=# create table if not exists test(id int); CREATE TABLE mydb=# truncate table test; TRUNCATE TABLE mydb=# commit; COMMIT mydb=# mydb=# begin; BEGIN mydb=# select * from test; id ---- (0 rows) mydb=# insert into test select 1; INSERT 0 1 mydb=# insert into test select 2; INSERT 0 1mydb=# select * from test; id ---- 1 2 (2 rows) mydb=# commit; COMMIT mydb=#mydb=# begin; BEGIN mydb=# select 1; ? column? ---------- 1 (1 row) mydb=# select * from test; id ---- 1 2 (2 rows) mydb=# update test set id = -1 where id =0; UPDATE 0 mydb=# update test set id = -2 where id =1; UPDATE 1 mydb=# select * from test; id ---- 2 -2 (2 rows) mydb=# rollback; ROLLBACK mydb=#mydb=# begin; BEGINmydb=# update test set id = -1 where id =0; UPDATE 0 mydb=# rollback; ROLLBACK mydb=#mydb=# begin; BEGIN mydb=# update test set id = -2 where id =1; UPDATE 1 mydb=# rollback; ROLLBACK mydb=#mydb=# drop table if exists test; DROP TABLECopy the codeDB log Records information
[postgres mydb PSQL [local] 2021-03-29 17:05:21.188 CST 7234 60619844.1 C42 1 3/521 0] create table if not exists test(id int); [postgres mydb PSQL [local] 2021-03-29 17:05:21.208 CST 7234 60619844.1c42 2 3/521 964] 19.577ms [postgres mydb PSQL [local] 2021-03-29 17:05:21.209 CST 7234 60619844.1 C42 3 3/521 964]LOG: Statement: truncate table test; [postgres mydb PSQL [local] 2021-03-29 17:05:21.212 CST 7234 60619844.1 C42 4 3/0 0]LOG: duration: 2.575 ms Statement: commit; [postgres mydb PSQL [local] 2021-03-29 17:05:21.216 CST 7234 60619844.1 C42 5 3/522 0]LOG: Statement: insert into test select 1; [postgres mydb PSQL [local] 2021-03-29 17:05:21.217 CST 7234 60619844.1 C42 6 3/522 965]LOG: Statement: insert into test select 2; [postgres mydb PSQL [local] 2021-03-29 17:05:21.221 CST 7234 60619844.1 C42 7 3/0]LOG: duration: 1.439ms Statement: commit; [postgres mydb PSQL [local] 2021-03-29 17:05:21.226 CST 7234 60619844.1 C42 8 3/523 0] update test set id = -1 where id =0; [postgres mydb PSQL [local] 2021-03-29 17:05:21.228 CST 7234 60619844.1 C42 9 3/523 0]LOG: Statement: update test set id = -2 where id =1; [postgres mydb PSQL [local] 2021-03-29 17:05:21.233 CST 7234 60619844.1 C42 10 3/524 0] update test set id = -1 where id =0; [postgres mydb PSQL [local] 2021-03-29 17:05:21.235 CST 7234 60619844.1 C42 11 3/5250 0]LOG: Statement: update test set id = -2 where id =1; [postgres mydb PSQL [local] 2021-03-29 17:05:21.237 CST 7234 60619844.1 C42 12 3/5260 0]LOG: Statement: drop table if exists test; [postgres mydb PSQL [local] 2021-03-29 17:05:21.240 CST 7234 60619844.1c42 13 3/0]LOG: duration: 2.545msCopy the code4. Summary
As you can see from the above cases, different combinations of Settings and different TYPES of SQL statements (DML,DDL,DCL) affect the final content and format of records in the log file.
The PostgreSQL DB log provides the following basic results:
- %p and %c, that is, the process ID and the session ID are one to one, but obviously the session ID is more specific than the numeric form of the process ID in a large number of logs.
- % V is assigned to the virtual transaction ID (backendID/localXID), and the localXID of the COMMIT/ROLLBACK is 0. However, the transaction ID of BEGIN/COMMIT/ROLLBACK is 0.
- In a transaction, % V is the same as the virtual transaction ID (backendID/localXID), which can be used as the basis for searching the entire SQL statement in the same transaction.
- In the same transaction, %x, which is the transaction ID, is more complicated,
- If the first SQL in a transaction is a SELECT or does not produce an actual written DML, the transaction ID printed is always 0,
- The first SQL in a transaction does not necessarily print the transaction ID even if it produces the actual written DML and DDL (although PostgreSQL has a transaction ID assigned internally at this point).
- Therefore, the transaction ID can not be used as the basis to find the complete SQL in the same transaction, but as auxiliary information;
5. Once a non-0 transaction number appears, all subsequent SQL transaction ids remain the same;
5. Examples
Based on the above examples and conclusions, looking at a DB Lock requires looking at an instance of the full SQL in the transaction.
Seesion A:
mydb=# begin;
BEGIN
mydb=# update test set id = 0
mydb-# where id =1;
UPDATE 1
mydb=# select txid_current_if_assigned();
txid_current_if_assigned
--------------------------
954
(1 row)
mydb=# select id from test;
id
----
2
0
(2 rows)
Copy the codeSession B:
mydb=# select pg_backend_pid(); pg_backend_pid ---------------- 5703 (1 row) mydb=# update test set id = -1 where id =1; Session B's update is in the waiting stateCopy the codeSession C:
-- SQL query for lock condition is too long Is omitted - [1] RECORD -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - pid | 5703 state | active transactionid | 956 virtualxid | locktype | transactionid usename | postgres application_name | psql client_addr | wait_event_type | Lock Wait_event | transactionid query_start | 2021-03-29 15:54:20. 126504 + 08 query_runtime | 115.839947 query | update test set id = -1 where id =1; relation | relname | waitfor_relation | waitfor_pid | 5665 waitfor_state | idle in transaction waitfor_transactionid | 954 waitfor_virtualxid | waitfor_locktype | transactionid waitfor_usename | postgres waitfor_client_addr | waitfor_application_name | psql waitfor_wait_event_type | Client waitfor_wait_event | ClientRead waitfor_query_start | The 2021-03-29 15:53:01. 613482 + 08 waitfor_query_runtime | 194.352969 waitfor_query | select id from the test.Copy the codeFor the sake of illustration, assume that the DB instance is in full log mode.
Now look at the complete SQL procedure in DB log detail:
- Locate the log file where query_start is located at 2021-03-29 15:53:01.
postgres@localhost log$ll postgresqL-2021-03-29 * log-rw ------- 1 postgres postgres 19405 March 29 16:00 postgresql-2021-03-29_142024.logCopy the codeBecause the native log is small, it can be directly located in this unique file. Production environments, especially high QPS instances, need to be a little more careful to narrow down the scope.
- Continue positioning according to the PID
You can see that the pid corresponding to the waiting transaction is 5665.
postgres@localhost log$grep 5665 postgresql-2021-03-29_142024.log [postgres mydb PSQL [local] 2021-03-29 15:47:35.859 CST 5665 6061856F.1621 1 3/0 0]LOG: duration: 4.909 MS Statement: select pg_backend_pid(); [postgres mydb PSQL [local] 2021-03-29 15:47:45.398 CST 5665 6061856F.1621 2 3/0]LOG: duration: 8.214ms Statement: select id from test; [postgres mydb PSQL [local] 2021-03-29 15:47:52.950 CST 5665 6061856F.1621 3 3/0]LOG: duration: 8.431 ms Statement: truncate table test; [postgres mydb PSQL [local] 2021-03-29 15:48:07.668 CST 5665 6061856F.1621 4 3/0]LOG: duration: 5.687 ms Statement: insert into test select 1; [postgres mydb PSQL [local] 2021-03-29 15:48:14.163 CST 5665 6061856F.1621 5 3/0 0]LOG: duration: 5.082 ms Statement: insert into test select 2; [postgres mydb PSQL [local] 2021-03-29 15:49:03.791 CST 5735 606185ab.1667 1 5/0 0]LOG: duration: 1.091ms Statement: select 5665 + 1; [postgres mydb PSQL [local] 2021-03-29 15:49:07.518 CST 5735 606185ab.1667 2 5/0]LOG: duration: 0.356ms Statement: select 5665 - 1; [postgres mydb PSQL [local] 2021-03-29 15:49:38.542 CST 5735 606185ab.1667 3 5/0 0]LOG: duration: 0.196ms Statement: select 56650 - 56650; [postgres mydb PSQL [local] 2021-03-29 15:51:45.950 CST 5665 6061856F.1621 6 3/0]LOG: duration: 0.36ms Statement: select txid_current_if_assigned(); [postgres mydb PSQL [local] 2021-03-29 15:51:48.958 CST 5665 6061856F.1621 7 3/516 0]LOG: duration: 0.06ms Statement: begin; [postgres mydb PSQL [local] 2021-03-29 15:52:09.311 CST 5665 6061856F.1621 8 3/516 954]LOG: duration: 1.646 ms statement: Update test set id = 0 [postgres mydb PSQL [local] 2021-03-29 15:52:12.030 CST 5665 6061856F.1621 9 3/516954]LOG: Duration: 0.181ms Statement: select TXID_CURRENT_IF_assigned (); [postgres mydb PSQL [local] 2021-03-29 15:53:01.613 CST 5665 6061856F.1621 10 3/516954]LOG: duration: Statement: select id from test;Copy the code- Further locating based on the session ID
You can see that the session corresponding to PID 31720 is 6061856F.1621, and then the statement and waitForqueryStart time are combined to further specify the interference items.
postgres@localhost log$ grep ' 6061856f.1621 ' postgresql-2021-03-29_142024.log | grep 'select id from test' [postgres Mydb PSQL [local] 2021-03-29 15:47:45.398 CST 5665 6061856F.1621 2 3/0]LOG: duration: 8.214ms Statement: select id from test; [postgres mydb PSQL [local] 2021-03-29 15:53:01.613 CST 5665 6061856F.1621 10 3/516954]LOG: duration: Statement: select id from test; postgres@localhost log$ grep ' 6061856f.1621 ' postgresql-2021-03-29_142024.log | grep 'select id from test' | grep '2021-03-29 15:53' [postgres mydb PSQL [local] 2021-03-29 15:53:01.613 CST 5665 6061856F.1621 10 3/516954]LOG: Duration: 0.213ms Statement: select id from test;Copy the code- Use VirtualXID to locate the location
You can see that virtualXID is 3/516.
postgres@localhost log$ grep ' 6061856f.1621 ' postgresql-2021-03-29_142024.log | grep '3/516' [postgres mydb psql [local] 2021-03-29 15:51:48.958 CST 5665 6061856F.1621 7 3/516 0]LOG: duration: 0.06ms Statement: begin; [postgres mydb PSQL [local] 2021-03-29 15:52:09.311 CST 5665 6061856F.1621 8 3/516 954]LOG: duration: 1.646 ms statement: Update test set id = 0 [postgres mydb PSQL [local] 2021-03-29 15:52:12.030 CST 5665 6061856F.1621 9 3/516954]LOG: Duration: 0.181ms Statement: select TXID_CURRENT_IF_assigned (); [postgres mydb PSQL [local] 2021-03-29 15:53:01.613 CST 5665 6061856F.1621 10 3/516954]LOG: duration: Statement: select id from test;Copy the code- Deal with folding line
Note: Some SQL statements have a return/newline (e.g., Statement: update test set ID = 0), which is recorded as TAB in DB log. There are a number of ways to handle this situation, and here I use a little Perl to do it.
postgres@localhost log$ cat postgresql-2021-03-29_142024.log | perl -e 'while(<>){ if($_ ! ~ /^\t/ig) { chomp; print "\n",$_; } else {chomp; print; }} '| grep' 6061856 f. 1621 '| grep' 3/516 '[postgres mydb PSQL [local] 15:51:48. 2021-03-29 958 5665 6061856 CST f. 1621 7 3/516 0]LOG: duration: 0.061 ms Statement: begin; [postgres mydb PSQL [local] 2021-03-29 15:52:09.311 CST 5665 6061856F.1621 8 3/516 954]LOG: duration: 1.646 MS Statement: Update test set ID = 0 where ID =1; [postgres mydb PSQL [local] 2021-03-29 15:52:12.030 CST 5665 6061856F.1621 9 3/516 954]LOG: duration: 0.185 ms Statement: select TXID_CURRENT_IF_assigned (); [postgres mydb PSQL [local] 2021-03-29 15:53:01.613 CST 5665 6061856F.1621 10 3/516954]LOG: duration: Statement: select id from test;Copy the codeYou can see that by using the consecutive line numbers (7 through 10), no logs are missing; At this point, the entire transaction of process 5665 blocking process 5703 in DB Lock has been located. \
In the actual production environment, especially in the DB instance with high QPS and large concurrent read and write volume, the situation is far more complicated than this instance. In most cases, the log mode is to record full writes && partial reads, so it is difficult to locate.
In this case, you can continue to use other escape parameters of log_line_prefix, such as %u, %a, %h, etc.
Other parameters can also be enabled to assist positioning. For example, we set log_lock_waits to on, cancel the update from process 5703, and run the same update again after deadlock_timeout (1s by default). Process 5703 prints the following information in the DB log:
[postgres mydb PSQL [local] 2021-03-29 15:54:11.789 CST 5703 60618581.1647 1 4/0 0]LOG: duration: 0.297ms Statement: select pg_backend_pid(); [postgres mydb PSQL [local] 2021-03-29 16:17:28.272 CST 5703 60618581.1647 2 4/11 956]ERROR: Scalable Statement Due to User Request [Postgres mydb PSQL [local] 2021-03-29 16:17:28.272 CST 5703 60618581.1647 3 4/11 956]CONTEXT: While updating tuple (0,1) in relation to "test" [postgres mydb PSQL [local] 2021-03-29 16:17:28.272 CST 5703 60618581.1647 4 4/11 956]STATEMENT: update test set id = -1 where id =1; [postgres mydb PSQL [local] 2021-03-29 16:17:31.545 CST 5703 60618581.1647 5 4/12957]LOG: Process 5703 still waiting for ShareLock on transaction 954 after 1000.199ms [postgres mydb PSQL [local] 2021-03-29 16:17:31.545 CST 5703 60618581.1647 6 4/12 957]DETAIL: [postgres mydb PSQL [local] 2021-03-29 16:17:31.545 CST 5703 60618581.1647 7 4/12957]CONTEXT: While updating tuple (0,1) in relation "test" [postgres mydb PSQL [local] 2021-03-29 16:17:31.545 CST 5703 60618581.1647 8 4/12 957]STATEMENT: update test set id = -1 where id =1;Copy the code6 review
Finding the complete SQL in a transaction is a slow process that examines the DBA’s knowledge base in detail and tests the DBA’s patience, but it is a very practical DBA operation skill requirement.
This article from the perspective of operation and maintenance practice of a brief analysis, welcome to come from the bottom principle/source Angle to explore more efficient conclusions and methods.