This topic describes the query mechanism of PolarDB for PostgreSQL(Compatible with Oracle).
When you execute a SELECT statement to query data, the system reads a data block from the disk, writes the data block into the memory, and then returns the results based on the data blocks in the memory.
When you execute an INSERT statement to insert a row of data, the system performs the following steps:
- The system reads a data block from the data file, writes the data block to the memory, and then writes the row of data into this data block. Then, the system generates a write-ahead logging (WAL) log for the INSERT statement.
- After you run the COMMIT command, the system generates a WAL log for the COMMIT command.
- The system immediately writes the generated WAL logs for the INSERT statement and the COMMIT command to disks. The commit log (clog) buffer records information about the successful commitment of the transaction.
- The bgwriter process writes the data in the data block to the data file. The system updates the clog file.
In the preceding figure, txid indicates a transaction identifier (XID). t_xmin indicates the insert XID. t_xmax indicates the delete XID. t_cid indicates a command identifier (CID) within the transaction. t_tcid indicates the offset in a data block. The data field indicates the actual values that are stored.
- Transaction 101
On the left side of the preceding figure, the SQL statement indicates that a row of data whose value is 0 is inserted into the tbl table. On the right side of the preceding figure, the actual values in the data block appear. The XID is 101. The delete XID is 0 because no delete transaction exists. The CID within the transaction is 0. The offset (0,1) indicates the first offset of the data block.
- Transaction 102In transaction 102, the tbl table is updated. The ID is updated to 1 first and then updated to 2. The transaction is committed.
- In the second table on the right side of the preceding figure, the delete XID is changed from 0 to 102. This indicates that the row of data is deleted by transaction 102. The offset changes to (0,2). This indicates the second offset of data block 0. Based on the second offset, the XID is 102. This indicates that the row of data whose value is 1 is inserted by transaction 102.
- In the third table on the right side of the preceding figure, the XID is still 102 and the offset is (0,3). This indicates that the actual value that is stored is 2. The CID within the transaction is 1. This indicates that the second command in the transaction is updated, which corresponds to the second UPDATE statement.
- Transaction 103
On the left side of the preceding figure, the SQL statement indicates that the tbl table is deleted. The delete XID changes to 103.