Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE myTable (
    datetime TEXT,
    sys_id INT,
    cputil REAL,
    memfree REAL,
    sessnum INT,
    util_lag REAL,   -- to be calculated AFTER INSERT via TRIGGER
    mem_lag REAL,    -- to be calculated AFTER INSERT via TRIGGER
    util_diff REAL,  -- to be calculated AFTER INSERT via TRIGGER
    mem_diff REAL,/*a comma was missing here*/    -- to be calculated AFTER INSERT via TRIGGER
    util_change TEXT -- to be calculated AFTER INSERT via TRIGGER
);

CREATE TABLE


insert into myTable(datetime,sys_id,cputil,memfree,sessnum)values
 ('2019/05/03 08:06:14',100,0.57,0.51,47)
,('2019/05/03 08:11:14',100,0.47,0.62,43)
,('2019/05/03 08:16:14',100,0.56,0.57,62)
,('2019/05/03 08:21:14',100,0.57,0.56,50)
,('2019/05/03 08:26:14',100,0.35,0.46,43)
,('2019/05/03 08:31:14',100,0.41,0.58,48)
,('2019/05/03 08:36:14',100,0.57,0.35,58)
,('2019/05/03 08:41:14',100,0.41,0.4,58)
,('2019/05/03 08:46:14',100,0.53,0.35,62)
,('2019/05/03 08:51:14',100,0.51,0.6,45)
,('2019/05/03 08:56:14',100,0.32,0.37,47)
,('2019/05/03 09:01:14',100,0.62,0.59,60)
,('2019/05/03 09:06:14',100,0.66,0.72,57)
,('2019/05/03 09:11:14',100,0.54,0.54,44)
,('2019/05/03 09:16:14',100,0.29,0.4,47)
,('2019/05/03 09:21:14',100,0.43,0.68,66)
,('2019/05/03 09:26:14',100,0.49,0.66,65)
,('2019/05/03 09:31:14',100,0.64,0.55,66)
,('2019/05/03 09:36:14',100,0.42,0.6,42)
,('2019/05/03 09:41:14',100,0.55,0.59,63) returning *;


datetime
sys_id
cputil
memfree
sessnum
util_lag
mem_lag
util_diff
mem_diff
util_change


2019/05/03 08:06:14
100
0.57
0.51
47
null
null
null
null
null

2019/05/03 08:11:14
100
0.47
0.62
43
null
null
null
null
null

2019/05/03 08:16:14
100
0.56
0.57
62
null
null
null
null
null

2019/05/03 08:21:14
100
0.57
0.56
50
null
null
null
null
null

2019/05/03 08:26:14
100
0.35
0.46
43
null
null
null
null
null

2019/05/03 08:31:14
100
0.41
0.58
48
null
null
null
null
null

2019/05/03 08:36:14
100
0.57
0.35
58
null
null
null
null
null

2019/05/03 08:41:14
100
0.41
0.4
58
null
null
null
null
null

2019/05/03 08:46:14
100
0.53
0.35
62
null
null
null
null
null

2019/05/03 08:51:14
100
0.51
0.6
45
null
null
null
null
null

2019/05/03 08:56:14
100
0.32
0.37
47
null
null
null
null
null

2019/05/03 09:01:14
100
0.62
0.59
60
null
null
null
null
null

2019/05/03 09:06:14
100
0.66
0.72
57
null
null
null
null
null

2019/05/03 09:11:14
100
0.54
0.54
44
null
null
null
null
null

2019/05/03 09:16:14
100
0.29
0.4
47
null
null
null
null
null

2019/05/03 09:21:14
100
0.43
0.68
66
null
null
null
null
null

2019/05/03 09:26:14
100
0.49
0.66
65
null
null
null
null
null

2019/05/03 09:31:14
100
0.64
0.55
66
null
null
null
null
null

2019/05/03 09:36:14
100
0.42
0.6
42
null
null
null
null
null

2019/05/03 09:41:14
100
0.55
0.59
63
null
null
null
null
null



INSERT 0 20


CREATE OR REPLACE FUNCTION fn_calculate_columns_after_insert()--missing parentheses here
    RETURNS TRIGGER LANGUAGE PLPGSQL AS $f$
BEGIN
    NEW.util_lag := (SELECT LAG(cputil) OVER (ORDER BY datetime) FROM myTable);
    NEW.mem_lag := (SELECT LAG(memfree) OVER (ORDER BY datetime) FROM myTable);
    NEW.util_diff := cputil - util_lag;
    NEW.mem_diff := memfree - mem_lag;
    IF NEW.util_diff > 0 THEN
        NEW.util_change := 'Up';
    END IF;--this was missing
    IF NEW.util_diff < 0 THEN
        NEW.util_change := 'Down';
    ELSE
        NEW.util_change := '';
    END IF;--this was missing
    RETURN NEW;
END;--This semicolon is optional because it's immediately followed by closing dollar qoutes.
$f$;--This semicolon was missing. It is not optional, because it ends the whole `create function` statement

CREATE TRIGGER tr_fill_calculated_columns
    AFTER INSERT ON myTable
    FOR EACH ROW
    EXECUTE FUNCTION fn_calculate_columns_after_insert();

CREATE FUNCTION



CREATE TRIGGER

datetime	sys_id	cputil	memfree	sessnum	util_lag	mem_lag	util_diff	mem_diff	util_change
2019/05/03 08:06:14	100	0.57	0.51	47	null	null	null	null	null
2019/05/03 08:11:14	100	0.47	0.62	43	null	null	null	null	null
2019/05/03 08:16:14	100	0.56	0.57	62	null	null	null	null	null
2019/05/03 08:21:14	100	0.57	0.56	50	null	null	null	null	null
2019/05/03 08:26:14	100	0.35	0.46	43	null	null	null	null	null
2019/05/03 08:31:14	100	0.41	0.58	48	null	null	null	null	null
2019/05/03 08:36:14	100	0.57	0.35	58	null	null	null	null	null
2019/05/03 08:41:14	100	0.41	0.4	58	null	null	null	null	null
2019/05/03 08:46:14	100	0.53	0.35	62	null	null	null	null	null
2019/05/03 08:51:14	100	0.51	0.6	45	null	null	null	null	null
2019/05/03 08:56:14	100	0.32	0.37	47	null	null	null	null	null
2019/05/03 09:01:14	100	0.62	0.59	60	null	null	null	null	null
2019/05/03 09:06:14	100	0.66	0.72	57	null	null	null	null	null
2019/05/03 09:11:14	100	0.54	0.54	44	null	null	null	null	null
2019/05/03 09:16:14	100	0.29	0.4	47	null	null	null	null	null
2019/05/03 09:21:14	100	0.43	0.68	66	null	null	null	null	null
2019/05/03 09:26:14	100	0.49	0.66	65	null	null	null	null	null
2019/05/03 09:31:14	100	0.64	0.55	66	null	null	null	null	null
2019/05/03 09:36:14	100	0.42	0.6	42	null	null	null	null	null
2019/05/03 09:41:14	100	0.55	0.59	63	null	null	null	null	null