一个特简单的linux shell scripting

#!/bin/sh
#FM
#2008-08-26
#This script will batch insert data into partitioned views table per 90 000
#It will import all of old_views data into views
#the old_views contains 398372489 rows
#
v_startID=1
v_endID=900000
v_seq=900000
v_begin=1
for ((i=1;i<443;i++))
do
psql adchap_dataware << Foo
BEGIN;
INSERT INTO views SELECT * FROM old_views where id >= $v_startID  and id <= $v_endID;
COMMIT;
Foo
v_startID=$(($v_endID+$v_begin))
v_endID=$(($v_endID+$v_seq))
echo $v_startID $v_endID
done

其实就是批量将旧表数据导入到新的分区表中而已。用java做特简单但是服务器没有jre环境。

用plpgsql写的话，缺点是旧表数据达到4亿，这样批量执行的话，这个函数是全部写完后作为一个事务提交的，这样很容易得到
ERROR: cannot have more than 2^32-1 commands in a transaction

因为分区表中有触发器，会根据时间判断放入到什么分区表中的

--Import Data to table partition
CREATE OR REPLACE FUNCTION BatchInsertViews(IN startID integer,IN totalNo integer) RETURNS integer AS $$
DECLARE
  v_startID integer;
  v_endID   integer;
  v_seqNo   integer;
  v_no      integer;

BEGIN

	v_seqNo := 900000;
	v_no := (totalNo/v_seqNo)+1;
	v_startID := startID;
	v_endID   := v_seqNo;
	FOR i IN 1 .. v_no LOOP
		RAISE NOTICE 'This Should be executed times :%',v_no;
		EXECUTE 'INSERT INTO views SELECT * FROM old_views where id >= ' || v_startID ||' and id <= '|| v_endID;
		v_startID := v_endID + 1;
		v_endID := v_endID + v_seqNo;
		RAISE NOTICE 'StartID Change To :%',v_startID;
		RAISE NOTICE 'EndID Change To :%',v_endID;
	END LOOP;

   RETURN 0;
END;
$$ LANGUAGE plpgsql;