Re: pl/proxy and sequence generation - Mailing list pgsql-general

Because we try to keep our main hubs stateless. And we want to have several of them for load balancing and high availability.

We did try to to tricks with sequences using step. So if we have 4 nodes we can use step 4 and started it's nodes sequence on different number. This is not fun to manage and if you have failover replicas you need to use step 8 or replicate sequences.

Now we are using schema called partconf in each node: That has requirements that all id wield must be bigint and each node has to be assigned unique number. This has proved to be quite easy to manage so far. In addition no need to replicate sequences to failover replicas we just assign them unique db_code's and all data coming from there gets unique id's automatically.

create schema partconf;
create sequence partconf.global_id_seq;
create sequence partconf.local_id_seq;
create table partconf.conf (
    part_nr integer ,  -- number of current partition in cluster
    max_part integer , -- maximum partition number in given cluster
    db_code bigint    -- unique code fro given partition used to make id's unique
);

create or replace function partconf.global_id() returns bigint language sql as
$$ -- used to create globally unique keys
    select db_code + nextval('partconf.global_id_seq') from partconf.conf;
$$ VOLATILE SECURITY DEFINER;

 create or replace function partconf.set_conf( i_part_nr integer, i_max_part integer, i_db_code integer)
returns text language plpgsql security definer as
$$ -- used by dbas to initialize or change partiton configutration
declare
    r_conf record;
begin
    if i_part_nr > i_max_part then
        raise exception 'Partition number (%) should not be bigger than maximum number of partitons (%)' , i_part_nr, i_max_part;
    end if;
    select * from partconf.conf into r_conf;
    if found then
        r_conf.db_code = r_conf.db_code / 10 ^ 15;
        raise notice 'Partiton conf changed!';
        raise notice 'Part nr: % -> %', r_conf.part_nr, i_part_nr;
        raise notice 'Max partition: % -> %', r_conf.max_part, i_max_part;
        raise notice 'Part nr: % -> %', r_conf.db_code, i_db_code;
        delete from partconf.conf;
    end if;
    if i_db_code < 1111 or 9999 < i_db_code then
        raise exception  'Db code (%) should be between 1111 and 9999', i_db_code;
    end if;
    insert into partconf.conf ( part_nr , max_part , db_code )
    values ( i_part_nr , i_max_part , i_db_code::bigint * 10 ^ 15 );

    return 'Ok';
end;
$$;

so all id fields get default.partconf.global_id()