CONTRIB: int8 sequence simulator - Mailing list pgsql-general

Apologies if this appears twice - testing a new email client.

There was some discussion the other day about a desire for int8
sequences. This simulates that by providing a large base value combined
with an int4 sequence. You will need to reset the big sequence to a new
base value every once in a while.

The one thing to be careful of is resetting a big sequence while another
process is using pgx_curr_big_val() - the base value will be changed
anyway. You can protect against this by issuing SET TRANSACTION ISOLATION
LEVEL SERIALIZABLE within a transaction but that will stop
pgx_next_big_val() from incrementing. You have been warned.

BTW - where should I post stuff for contrib directory, and is this
suitable?

- Richard Huxton

-- Big sequence generator: generates int8 sequences from a base value
-- and int4 sequence
--
-- Requirements: Postgres v7.1 with plpgsql enabled on your database
--
-- Usage:
--   select pgx_create_big_seq('testseq',1000000);
--   select pgx_next_big_val('testseq');
--   select pgx_curr_big_val('testseq');
--
-- At intervals you will need to reset the big sequence to a new base
--   select pgx_reset_big_seq('testseq',2000000);
--
-- This code is provided as-is and licenced under the same terms as
PostgreSQL
--
-- Author:  Richard Huxton
-- Email:   dev@archonet.com
-- Version: 1.0 (2001-03-07)

--
-- Create a table to track our int8 sequences
--
CREATE TABLE pgx_bigseq (
  bigname    varchar(64) not null,
  base       int8,
  last_reset timestamp
);
CREATE UNIQUE INDEX pgx_bigseq_name ON pgx_bigseq(bigname);

--
-- pgx_create_big_seq(BIG-SEQ-NAME, BASE)
--
CREATE FUNCTION pgx_create_big_seq(text, int8) RETURNS text AS '
DECLARE
  bigseqname ALIAS FOR $1;
  bigbase ALIAS FOR $2;
  seqname text;
BEGIN
  seqname := ''pgx_'' || bigseqname;
  EXECUTE ''CREATE SEQUENCE '' || seqname;
  INSERT INTO pgx_bigseq VALUES (bigseqname, bigbase, now());
  RETURN ''Big sequence '' || bigseqname || '' created.'';
END;
' language 'plpgsql';

--
-- pgx_next_big_val(SEQ)
--
CREATE FUNCTION pgx_next_big_val(text) RETURNS int8 AS '
DECLARE
  bigseq     ALIAS FOR $1;
  nextbigval int8;
BEGIN
  SELECT INTO nextbigval base FROM pgx_bigseq WHERE bigname = bigseq;
  IF NOT FOUND THEN
    RAISE EXCEPTION ''Unknown big sequence %'', bigseq;
  END IF;
  nextbigval := nextbigval + nextval(''pgx_'' || bigseq);
  RETURN nextbigval;
END;
' language 'plpgsql';

--
-- pgx_curr_big_val(SEQ)
--
CREATE FUNCTION pgx_curr_big_val(text) RETURNS int8 AS '
DECLARE
  bigseq     ALIAS FOR $1;
  nextbigval int8;
BEGIN
  SELECT INTO nextbigval base FROM pgx_bigseq WHERE bigname = bigseq;
  IF NOT FOUND THEN
    RAISE EXCEPTION ''Unknown big sequence %'', bigseq;
  END IF;
  nextbigval := nextbigval + currval(''pgx_'' || bigseq);
  RETURN nextbigval;
END;
' language 'plpgsql';

--
-- pgx_reset_big_seq(NEWBASE)
--
CREATE FUNCTION pgx_reset_big_seq(text, int8) RETURNS text AS '
DECLARE
  bigseqname ALIAS FOR $1;
  newbase ALIAS FOR $2;
  seqname text;
BEGIN
  seqname := ''pgx_'' || bigseqname;
  EXECUTE ''SELECT setval('''''' || seqname || '''''',1,true)'';
  UPDATE pgx_bigseq SET base=newbase, last_reset=now() WHERE
bigname=bigseqname;
  RETURN ''Big sequence '' || bigseqname || '' reset.'';
END;
' language 'plpgsql';