乘风原创程序

  • postgres 使用存储过程批量插入数据的操作
  • 2021/2/2 17:09:07
  • 参考

    create or replace function creatdata2() returns 
    boolean as
    $body$
    declare ii integer;
     begin
     ii:=1;
     for ii in 1..10000000 loop
     insert into ipm_model_history_data (res_model, res_id) values (116, ii);
     end loop;
     return true;
     end;
    $body$
    language plpgsql;
    select * from creatdata2() as tab;

    插入1千万条数据耗时610s,当然字段不多的情况下。

    补充:postgresql存储过程--更新或者插入数据

    要记录某一时段机器cpu、内存、硬盘的信息,展示的时间粒度为分钟,但是为了精确,输入数据源的时间粒度为6s。这个统计过程可以在应用层做好,每分钟插入一次,也可以在数据库层写个存储过程来完成,根据传入数据的时间来判断是更新数据库旧数据还是插入新数据。

    同时,这些数据只需要保留一周,更老的数据需要被删除。删除动作可以每天定时执行一次,也可以写在存储过程中每次检查一下。

    考虑到性能在此时没什么太大约束,而后面存储过程的接口方式更漂亮些,不用应用层去关心数据到底组织成什么样,因此实现了一个如下:

    postgresql v8.3
    create or replace function insert_host_status(_log_date timestamp without time zone, _host inet, _cpu integer, _mem integer, _disk integer)
     returns void as
    $body$
    declare
      new_start timestamp without time zone;
      current_start timestamp without time zone;
      c_id integer;
      c_log_date timestamp without time zone;
      c_cpu integer;
      c_mem integer;
      c_disk integer;
      c_count integer;
      date_span interval;
    begin
      -- insert or update
      select id, log_date, cpu, mem, disk, count into c_id, c_log_date, c_cpu, c_mem, c_disk, c_count from host_status_byminute where host=_host order by id desc limit 1;
      select timestamp_mi(_log_date, c_log_date) into date_span;
      if date_span >= '00:00:60' or c_id is null then
        insert into host_status_byminute (log_date, host, cpu, mem, disk, count) values (_log_date, _host, _cpu, _mem, _disk, 1);
      elsif date_span >= '-00:00:60' then
        c_mem := ((c_mem * c_count) + _mem)/(c_count + 1);
        c_cpu := ((c_cpu * c_count) + _cpu)/(c_count + 1);
        c_disk := ((c_disk * c_count) + _disk)/(c_count + 1);
        c_count := c_count + 1;
        update host_status_byminute set mem=c_mem, cpu=c_cpu, disk=c_disk, count=c_count where id=c_id;
      end if;
      -- delete old data
      select date_mii(date(now()), 6) into new_start;
      select date(log_date) from host_status_byminute limit 1 into current_start; -- omit a bug happened when date is disordered.
      if new_start > current_start then
        delete from host_status_byminute where log_date < new_start;
      end if;
    end;
    $body$
     language 'plpgsql' volatile
     cost 100;
    alter function insert_host_status(timestamp without time zone, inet, integer, integer, integer) owner to dbuser_test;
    

    以上为个人经验,希望能给大家一个参考,也希望大家多多支持本教程网。如有错误或未考虑完全的地方,望不吝赐教。