AntDB 通过扩展 default expression 实现字段值的自动更新

由 MySQL 的 ON UPDATE CURRENT_TIMESTAMP 带来的启发，AntDB 是否可以实现 UPDATE 时自动更新字段值。

方案

• 方案1：尝试为需要自动更新的字段自动创建 TRIGGER。
• 方案2：尝试通过 DEFAULT EXPRESSION 来实现字段值的自动更新。

实现

方案1

​ PostgreSQL之时间戳自动更新

方案2

1. PostgreSQL 默认的 default expression 实现

CREATE TABLE ts (
    id int,
    value int DEFAULT ,
    tt timestamp DEFAULT current_timestamp
);
           

2. 查看 pg_attrdef

postgres=# \d ts 
                  Table "public.ts"
 Column |            Type             |   Modifiers   
--------+-----------------------------+---------------
 id     | integer                     | 
 value  | integer                     | default 0
 tt     | timestamp without time zone | default now()

postgres=# select adrelid::regclass, adnum, pg_get_expr(adbin, adrelid) from pg_attrdef where adrelid = 'ts'::regclass ;
 adrelid | adnum | pg_get_expr 
---------+-------+-------------
 ts      |     2 | 0
 ts      |     3 | now()
(2 rows)
           

3. 扩展 default expression 语法

新增 ColDefaultWhen 规则，对 default expression 修饰，表示 default expression 的生效时机。

DEFAULT b_expr ColDefaultWhen
    {
        Constraint *n = makeNode(Constraint);
        n->contype = CONSTR_DEFAULT;
        n->location = @;
        n->raw_expr = makeDefaultExpr($3, $2, @);
        n->cooked_expr = NULL;
        $$ = (Node *)n;
    }

ColDefaultWhen:
            ON INSERT                     { $$ = DEF_ON_INSERT; }
			| ON UPDATE                   { $$ = DEF_ON_UPDATE; }
			| ON INSERT OR UPDATE         { $$ = DEF_ON_INSERT | DEF_ON_UPDATE; }
			|                             { $$ = DEF_ON_INSERT; }
        ;
           

4. 新增 DefaultExpr 数据结构

/*
 * Default Expression
 */
typedef struct DefaultExpr
{
    NodeTag     type;
    int16       def_when;       /* when to set default expression */
    Node       *def_expr;       /* default expression */
    int         location;       /* token location, or -1 if unknown */
} DefaultExpr;
           

5. 新版 default expression 实现自动更新

CREATE TABLE ts (
    id int,
    value int DEFAULT  ON INSERT DEFAULT  ON UPDATE,
    tt timestamp DEFAULT current_timestamp ON INSERT OR UPDATE
);

postgres=# \d ts 
                               Table "public.ts"
 Column |            Type             |               Modifiers                
--------+-----------------------------+----------------------------------------
 id     | integer                     | 
 value  | integer                     | default (0) ON INSERT, () ON UPDATE
 tt     | timestamp without time zone | default (now()) ON INSERT OR UPDATE

postgres=# select adrelid::regclass, adnum, pg_get_expr(adbin, adrelid) from pg_attrdef where adrelid = 'ts'::regclass; 
 adrelid | adnum |          pg_get_expr           
---------+-------+--------------------------------
 ts      |     2 | (0) ON INSERT, () ON UPDATE
 ts      |      | (now()) ON INSERT OR UPDATE
( rows)
           

6. 验证字段是否自动更新

postgres=# insert into ts values(, default, default);
INSERT  
postgres=# insert into ts(id) values();
INSERT  
postgres=# insert into ts(id, value) values(, );
INSERT  
postgres=# insert into ts default values;
INSERT  
postgres=# insert into ts values(,,now());
INSERT  
postgres=# select * from ts where id = ;
 id | value |             tt             
----+-------+----------------------------
  3 |     1 | 2018-03-06 14:38:52.51784
(1 row)
postgres=# update ts set value = id+ where id = ;
UPDATE 
postgres=# select * from ts order by ;            
 id | value |             tt             
----+-------+----------------------------
  3 |     4 | 2018-03-06 14:41:29.099994
(1 row)
           

由上述结果看到，更新 value 时，tt 字段的值自动更新。

7. 验证 default expression 的 alter 操作

postgres=# \d ts 
                               Table "public.ts"
 Column |            Type             |               Modifiers                
--------+-----------------------------+----------------------------------------
 id     | integer                     | 
 value  | integer                     | default (0) ON INSERT, () ON UPDATE
 tt     | timestamp without time zone | default (now()) ON INSERT OR UPDATE

postgres=# alter table ts alter COLUMN value drop default on update;
ALTER TABLE
postgres=# \d ts
                             Table "public.ts"
 Column |            Type             |              Modifiers              
--------+-----------------------------+-------------------------------------
 id     | integer                     | 
 value  | integer                     | default () ON INSERT
 tt     | timestamp without time zone | default (now()) ON INSERT OR UPDATE

postgres=# alter table ts alter COLUMN tt drop default on update;
ALTER TABLE
postgres=# \d ts
                        Table "public.ts"
 Column |            Type             |         Modifiers         
--------+-----------------------------+---------------------------
 id     | integer                     | 
 value  | integer                     | default () ON INSERT
 tt     | timestamp without time zone | default (now()) ON INSERT

postgres=# alter table ts alter COLUMN tt set default current_timestamp on update;    
ALTER TABLE
postgres=# \d ts
                                  Table "public.ts"
 Column |            Type             |                  Modifiers                   
--------+-----------------------------+----------------------------------------------
 id     | integer                     | 
 value  | integer                     | default () ON INSERT
 tt     | timestamp without time zone | default (now()) ON INSERT, (now()) ON UPDATE

postgres=# alter table ts alter COLUMN value set default  on update;                      
ALTER TABLE
postgres=# \d ts
                                  Table "public.ts"
 Column |            Type             |                  Modifiers                   
--------+-----------------------------+----------------------------------------------
 id     | integer                     | 
 value  | integer                     | default () ON INSERT, () ON UPDATE
 tt     | timestamp without time zone | default (now()) ON INSERT, (now()) ON UPDATE

postgres=# alter table ts alter COLUMN value set data type float4;  
ALTER TABLE
postgres=# \d ts
                                  Table "public.ts"
 Column |            Type             |                  Modifiers                   
--------+-----------------------------+----------------------------------------------
 id     | integer                     | 
 value  | real                        | default () ON INSERT, () ON UPDATE
 tt     | timestamp without time zone | default (now()) ON INSERT, (now()) ON UPDATE
           

8. patch 代码

default_on_update.patch