10.3. Using global sequences

Basic usage of global sequences is trivial: In a BDR node, when creating a sequence, append USING bdr; to your sequence creation statement:

   CREATE SEQUENCE test_seq USING bdr;
   

Once you've created a global sequence you may use it with nextval like any other sequence.

Most PostgreSQL applications don't use CREATE SEQUENCE directly; instead, they use the SERIAL pseudo-type. To accomodate this, BDR provides the default_sequenceam parameter. When set to bdr, all sequences will be automatically created as global sequences unless explicitly overridden by a USING option. So every SERIAL or BIGSERIAL will automatically be a global sequence. e.g.:

    BEGIN;

    SET LOCAL default_sequenceam = 'bdr';

    CREATE TABLE gstest (
      id serial primary key,
      parrot text
    );

    COMMIT;
   

will create a global sequence named gstest_id_seq and set the DEFAULT of gstest.id to nextval('gstest_id_seq').

Alternately, the application may do what PostgreSQL does internally with the SERIAL and BIGSERIAL types:

    BEGIN;

    CREATE TABLE gstest (
      id bigint primary key,
      parrot text
    );

    CREATE SEQUENCE gstest_id_seq OWNED BY gstest.id USING bdr;

    ALTER TABLE gstest ALTER COLUMN id SET DEFAULT nextval('gstest_id_seq');
   

To convert a global sequence back to a normal local PostgreSQL sequence, use:

     ALTER SEQUENCE seqname USING local;
   

However, note that this will not cause the sequence to resume at the highest value allocated on any node; rather, it resumes at the highest value allocated on the local node. It is generally wise to setval(...) the sequence to the highest value present in the target table while a EXCLUSIVE lock is held on the sequence.

Warning

State for global sequences is stored in the BDR catalogs, not the sequence relation its self, and is tracked by sequence name not sequence oid. If you drop a global sequence and re-create it with the same name, it will pick up roughly where it left off, though it'll throw away a large range of IDs in the process.