Skip site navigation (1) Skip section navigation (2)

Re: pl/proxy and sequence generation

From: "Asko Oja" <ascoja(at)gmail(dot)com>
To: "Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com>
Cc: "Igor Katson" <descentspb(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: pl/proxy and sequence generation
Date: 2008-12-25 01:36:47
Message-ID: ecd779860812241736n5ce3d947g13ca69b8773ac556@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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
>

In response to

pgsql-general by date

Next:From: Andrej PodzimekDate: 2008-12-25 05:51:54
Subject: Automatic CRL reload
Previous:From: Erik JonesDate: 2008-12-24 23:41:20
Subject: Re: lack of consequence with domains and types

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group