gistfile1.txt

CREATE OR REPLACE FUNCTION clear_count_1(sid int) RETURNS int AS 
$$
if '__count_1' in SD:
  SD['__count_1'] = -1
  return 1
return 0
$$ LANGUAGE plpythonu;


CREATE OR REPLACE FUNCTION updateid(startid bigint, sid int, sids int[], base_ids bigint[], base_ids_noagg bigint[]) RETURNS bigint AS 
$$
if '__count_1' in SD:
  a = SD['__count_2']
  b = SD['__count_1']
  SD['__count_2'] = SD['__count_2'] - 1
  if SD['__count_2'] < 0:
    plpy.info(("~~~~~~~~ POP SEGMENT %d" % sid));
    SD.pop('__count_1')
  return startid+b-a
else:
  plpy.info("        SEGMENT %d" % sid + " " + sids.__repr__());
  for i in range(0, len(sids)):
    if sids[i] == sid:
      plpy.info(("SEGMENT %d" % sid) + (" ID ENDS AT %i" % base_ids[i]));
      SD['__count_1'] = base_ids[i] - 1
      SD['__count_2'] = base_ids_noagg[i] - 1
  a = SD['__count_2']
  b = SD['__count_1']
  SD['__count_2'] = SD['__count_2'] - 1
  if SD['__count_2'] < 0:
    SD.pop('__count_1')
  return startid+b-a
  
$$ LANGUAGE plpythonu;

CREATE OR REPLACE FUNCTION fast_seqassign(tname character varying, startid bigint) RETURNS TEXT AS $$
BEGIN
  RAISE NOTICE 'DROPING tmp_gpsid_count...';
  EXECUTE 'drop table if exists tmp_gpsid_count cascade;';
  EXECUTE 'drop table if exists tmp_gpsid_count_noagg cascade;';
  RAISE NOTICE 'CREATING tmp_gpsid_count...';
  EXECUTE 'create table tmp_gpsid_count as select gp_segment_id as sid, count(clear_count_1(gp_segment_id)) as base_id from ' || quote_ident(tname) || ' group by gp_segment_id order by sid distributed by (sid);';
  EXECUTE 'create table tmp_gpsid_count_noagg as select * from tmp_gpsid_count distributed by (sid);';
  EXECUTE 'update tmp_gpsid_count as t set base_id = (SELECT SUM(base_id) FROM tmp_gpsid_count as t2 WHERE t2.sid <= t.sid);';
  RAISE NOTICE 'EXECUTING _fast_seqassign()...';
  EXECUTE 'select * from _fast_seqassign(''' || quote_ident(tname) || ''', ' || startid || ');';
  RETURN ':-)';
END;
$$LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION _fast_seqassign(tname character varying, startid bigint)
RETURNS TEXT
AS
$$
DECLARE
  sids int[] :=  ARRAY(SELECT sid FROM tmp_gpsid_count ORDER BY sid);
  base_ids bigint[] :=  ARRAY(SELECT base_id FROM tmp_gpsid_count ORDER BY sid);
  base_ids_noagg bigint[] :=  ARRAY(SELECT base_id FROM tmp_gpsid_count_noagg ORDER BY sid);
  tsids text;
  tbase_ids text;
  tbase_ids_noagg text;
BEGIN
  SELECT INTO tsids array_to_string(sids, ',');
  SELECT INTO tbase_ids array_to_string(base_ids, ',');
  SELECT INTO tbase_ids_noagg array_to_string(base_ids_noagg, ',');
  EXECUTE 'update ' || tname || ' set id = updateid(' || startid || ', gp_segment_id, ARRAY[' || tsids || '], ARRAY[' || tbase_ids || '], ARRAY[' || tbase_ids_noagg || ']);';
  RETURN ':-)';
END;
$$
LANGUAGE 'plpgsql';