Обсуждение: pl/proxy and sequence generation
Hello! The problem, i'm going to describe is about pl/proxy usage. I'll call the bases, which are proxied to, the node-based and the base, which contains pl/proxy the proxy-base. The task, I need to accomplish, is to make an insert into node-bases, using the sequence, generated inside the proxy-base, as the primary key. As far as I understand, this is not a good idea to generate the keys inside the node-bases, simply because the values will not be unique, cause they will have different sequence generators. So I decided to make a sequence in the proxy-base. And the sequence number should be passed to the node-bases as a fucntion argument. As far as I know, you should have the same number and types of arguments for this function both in the node-bases and the proxy-base. So I should have a sequence number as an argument in the proxy-base too. Usually, the application calls the function in the proxy-base, and this function calls the same ones in the node-bases. But the application should not generate the sequence, and it should not pass it as an argument to the function. 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? Thanks in advance, Igor Katson.
On Wed, Dec 24, 2008 at 10:18 AM, Igor Katson <descentspb@gmail.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, ...);
--
Jonah H. Harris, Senior DBA
myYearbook.com
On Wed, Dec 24, 2008 at 5:44 PM, Jonah H. Harris <jonah.harris@gmail.com> wrote:
On Wed, Dec 24, 2008 at 10:18 AM, Igor Katson <descentspb@gmail.com> wrote:Why not put the sequence on your main PL/Proxy hub and call the function with:
> 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?
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()
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general