PostgreSQL日志触发器优化(PostgreSQL log trigger optimalization)

PostgreSQL日志触发器优化(PostgreSQL log trigger optimalization)

我花了很多时间尝试优化我们的pgsql日志触发器，这开始成为一个问题。 我做了很大的进步（通过插入3M行从18分钟到2.5分钟）但我想知道一些pgSql主人是否能够做得更好。

CREATE OR REPLACE FUNCTION table_log_trig() RETURNS trigger AS $BODY$ DECLARE col TEXT; -- Single column name to save newVal TEXT; -- New value for column oldVal TEXT; -- Old value for column colLimit TEXT[]; -- Columns that should be logged BEGIN IF TG_ARGV[0] IS NOT NULL THEN -- Trigger specifies columns to log SELECT array_agg(unnest) FROM unnest(string_to_array(TG_ARGV[0], ',')) INTO colLimit; ELSE -- Trigger with no params. Log all columns SELECT array_agg(json_object_keys) FROM json_object_keys(row_to_json(NEW)) WHERE json_object_keys NOT IN ('id', 'created_at', 'updated_at') -- Exceptions INTO colLimit; END IF; -- Loop over columns that should be saved in log FOREACH col IN ARRAY colLimit LOOP -- INSERT & UPDATE EXECUTE 'SELECT ($1).' || col || '::text' INTO newVal USING NEW; -- UPDATE IF TG_OP = 'UPDATE' THEN EXECUTE 'SELECT ($1).' || col || '::text' INTO oldVal USING OLD; END iF; -- Add only new or changed data IF newVal != oldVal OR (oldVal IS NULL AND newVal IS NOT NULL) OR (oldVal IS NOT NULL AND newVal IS NULL) THEN INSERT INTO tab_logs (record_id, field_name, old_value, new_value, created_at, created_by, action) VALUES (NEW.id, col, oldVal, newVal, NOW(), 999, 'O'); END IF; END LOOP; RETURN NEW; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100;

I spent a lot of time trying to optimize our pgsql log trigger which started to be a problem. I did huge progress (from 18min to 2.5min by inserting 3M rows) but I would like to know if some pgSql masters will be able to do it even better.

CREATE OR REPLACE FUNCTION table_log_trig() RETURNS trigger AS $BODY$ DECLARE col TEXT; -- Single column name to save newVal TEXT; -- New value for column oldVal TEXT; -- Old value for column colLimit TEXT[]; -- Columns that should be logged BEGIN IF TG_ARGV[0] IS NOT NULL THEN -- Trigger specifies columns to log SELECT array_agg(unnest) FROM unnest(string_to_array(TG_ARGV[0], ',')) INTO colLimit; ELSE -- Trigger with no params. Log all columns SELECT array_agg(json_object_keys) FROM json_object_keys(row_to_json(NEW)) WHERE json_object_keys NOT IN ('id', 'created_at', 'updated_at') -- Exceptions INTO colLimit; END IF; -- Loop over columns that should be saved in log FOREACH col IN ARRAY colLimit LOOP -- INSERT & UPDATE EXECUTE 'SELECT ($1).' || col || '::text' INTO newVal USING NEW; -- UPDATE IF TG_OP = 'UPDATE' THEN EXECUTE 'SELECT ($1).' || col || '::text' INTO oldVal USING OLD; END iF; -- Add only new or changed data IF newVal != oldVal OR (oldVal IS NULL AND newVal IS NOT NULL) OR (oldVal IS NOT NULL AND newVal IS NULL) THEN INSERT INTO tab_logs (record_id, field_name, old_value, new_value, created_at, created_by, action) VALUES (NEW.id, col, oldVal, newVal, NOW(), 999, 'O'); END IF; END LOOP; RETURN NEW; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100;

最满意答案

row_to_json()返回列名和值; 您也可以使用这些值，而不是稍后通过动态SQL提取它们。

我没有对此进行过彻底的测试，更不用说对它进行基准测试了，但这里是它的要点：

CREATE OR REPLACE FUNCTION table_log_trig() RETURNS trigger AS $$ DECLARE OldJson JSONB = NULL; BEGIN IF TG_OP <> 'INSERT' THEN OldJson := to_jsonb(old); END IF; INSERT INTO tab_logs (record_id, field_name, old_value, new_value, created_at, created_by, action) SELECT new.id, key, OldValues.value, NewValues.value, now(), 999, 'O' FROM jsonb_each(to_jsonb(new)) NewValues LEFT JOIN jsonb_each(OldJson) OldValues USING (key) WHERE ( (TG_ARGV[0] IS NULL AND key NOT IN ('id', 'created_at', 'updated_at')) OR (TG_ARGV[0] IS NOT NULL AND key = ANY(string_to_array(TG_ARGV[0], ','))) ) AND OldValues.value::text IS DISTINCT FROM NewValues.value::text; RETURN NULL; END $$ LANGUAGE plpgsql VOLATILE;

row_to_json() returns both column names and values; you may as well make use of these values, rather than extracting them later via dynamic SQL.

I haven't thoroughly tested this, let alone benchmarked it, but here's the gist of it:

CREATE OR REPLACE FUNCTION table_log_trig() RETURNS trigger AS $$ DECLARE OldJson JSONB = NULL; BEGIN IF TG_OP <> 'INSERT' THEN OldJson := to_jsonb(old); END IF; INSERT INTO tab_logs (record_id, field_name, old_value, new_value, created_at, created_by, action) SELECT new.id, key, OldValues.value, NewValues.value, now(), 999, 'O' FROM jsonb_each(to_jsonb(new)) NewValues LEFT JOIN jsonb_each(OldJson) OldValues USING (key) WHERE ( (TG_ARGV[0] IS NULL AND key NOT IN ('id', 'created_at', 'updated_at')) OR (TG_ARGV[0] IS NOT NULL AND key = ANY(string_to_array(TG_ARGV[0], ','))) ) AND OldValues.value::text IS DISTINCT FROM NewValues.value::text; RETURN NULL; END $$ LANGUAGE plpgsql VOLATILE;