Overview

Background

The ledger database, which integrates a blockchain idea, records a user operation in two types of historical tables: a user history table and a global blockchain table. When a user creates a tamper-proof user table, the system automatically adds a hash column to the table to save the hash summary of each row of data. In blockchain schema, a user history table is created to record the change behavior of each data record in the user table. The user's modification to the tamper-proof user table will be recorded in the global blockchain table. Because the history table can only be appended and cannot be modified, the records in the history table are connected to form the modification history of the tamper-proof user table.

The name and structure of the user history table are as follows:

Table 1 Columns in the blockchain.<schemaname>_<tablename>_hist user history table

Table 2 Mapping between hash_ins and hash_del

Procedure

1. Create a schema in tamper-proof schema.

   For example, create ledgernsp in tamper-proof schema.

   openGauss=# CREATE SCHEMA ledgernsp WITH BLOCKCHAIN;
   

2. Create a tamper-proof user table in tamper-proof schema.

   For example, create a tamper-proof user table ledgernsp.usertable.

   openGauss=# CREATE TABLE ledgernsp.usertable(id int, name text);
   

   Check the structure of the tamper-proof user table and the corresponding user history table.

   openGauss=# \d+ ledgernsp.usertable;
   openGauss=# \d+ blockchain.ledgernsp_usertable_hist;
   

   The command output is as follows:

   openGauss=# \d+ ledgernsp.usertable;
                        Table "ledgernsp.usertable"
    Column |  Type   | Modifiers | Storage  | Stats target | Description
   --------+---------+-----------+----------+--------------+-------------
    id     | integer |           | plain    |              |
    name   | text    |           | extended |              |
    hash   | hash16  |           | plain    |              |
   Has OIDs: no
   Options: orientation=row, compression=no
   History table name: ledgernsp_usertable_hist
   
   openGauss=# \d+ blockchain.ledgernsp_usertable_hist;
                Table "blockchain.ledgernsp_usertable_hist"
     Column  |  Type  | Modifiers | Storage | Stats target | Description
   ----------+--------+-----------+---------+--------------+-------------
    rec_num  | bigint |           | plain   |              |
    hash_ins | hash16 |           | plain   |              |
    hash_del | hash16 |           | plain   |              |
    pre_hash | hash32 |           | plain   |              |
   Indexes:
       "gs_hist_16388_index" PRIMARY KEY, btree (rec_num int4_ops) TABLESPACE pg_default
   Has OIDs: no
   Distribute By: HASH(rec_num)
   Location Nodes: ALL DATANODES
   Options: internal_mask=263
   

   NOTE:

   • Tamper-proof tables cannot be non-row-store tables, temporary tables, foreign tables, or unlogged tables. Non-row-store tables do not have the temper-proof attribute.
   • When a temper-proof table is created, a system column named hash is automatically added. Therefore, the maximum number of columns in the temper-proof table is 1599.

   Warning:

   • The dbe_perf and snapshot schemas cannot be altered to the blockchain attribute, for example, ALTER SCHEMA dbe_perf WITH BLOCKCHAIN.
   • The system schema cannot be altered to the blockchain attribute, such as ALTER SCHEMA pg_catalog WITH BLOCKCHAIN.
   • The attribute of a schema that contains a table cannot be changed to blockchain using the ALTER SCHEMA statement.

3. Modify the data in the tamper-proof user table.

   For example, execute INSERT, UPDATE, or DELETE on the tamper-proof user table.

   openGauss=# INSERT INTO ledgernsp.usertable VALUES(1, 'alex'), (2, 'bob'), (3, 'peter');
   INSERT 0 3
   openGauss=# SELECT *, hash FROM ledgernsp.usertable ORDER BY id;
    id | name  |       hash
   ----+-------+------------------
     1 | alex  | 1f2e543c580cb8c5
     2 | bob   | 8fcd74a8a6a4b484
     3 | peter | f51b4b1b12d0354b
   (3 rows)
   
   openGauss=# UPDATE ledgernsp.usertable SET name = 'bob2' WHERE id = 2;
   UPDATE 1
   openGauss=# SELECT *, hash FROM ledgernsp.usertable ORDER BY id;
    id | name  |       hash
   ----+-------+------------------
     1 | alex  | 1f2e543c580cb8c5
     2 | bob2  | 437761affbb7c605
     3 | peter | f51b4b1b12d0354b
   (3 rows)
   
   openGauss=# DELETE FROM ledgernsp.usertable WHERE id = 3;
   DELETE 1
   openGauss=# SELECT *, hash FROM ledgernsp.usertable ORDER BY id;
    id | name |       hash
   ----+------+------------------
     1 | alex | 1f2e543c580cb8c5
     2 | bob2 | 437761affbb7c605
   (2 rows)