Re: pl/proxy and sequence generation

On Wed, Dec 24, 2008 at 5:44 PM, Jonah H. Harris <jonah(dot)harris(at)gmail(dot)com>wrote:

> On Wed, Dec 24, 2008 at 10:18 AM, Igor Katson <descentspb(at)gmail(dot)com>
> wrote:
> > So, should I make a wrapper in e.g. PL/pgsql for every insert function
> > writen in PL/Proxy to remove the sequence from the argument list and to
> call
> > the sequence generator?
> > Is there a better way to do that?
>
> Why not put the sequence on your main PL/Proxy hub and call the function
> with:
>
> SELECT some_func(nextval('my_seq'), foo, bar, baz, ...);
>
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()

--
> Jonah H. Harris, Senior DBA
> myYearbook.com
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>