[prev in list] [next in list] [prev in thread] [next in thread] 

List:       slony1-commit
Subject:    [Slony1-commit] By cbbrowne: Added a substantially more
From:       cvsuser () gborg ! postgresql ! org (CVS User Account)
Date:       2006-08-11 15:46:03
Message-ID: 20060811154603.3FB8E11BF038 () gborg ! postgresql ! org
[Download RAW message or body]

Log Message:
-----------
Added a substantially more sophisticated schema to the inheritance / 
partitioning test.

- The master table references a couple foreign keys
- Constraints are added to the subtables to restrict them to
  accept data for particular geographic regions
- Rules are put on the master table to automatically distribute data
  to the subtables; note that some data is left over and kept in the
  master table
- A stored procedure is used to manage inserts to the tables
- Data is selected randomly from region/product lists, showing that
  some decidedly nondeterministic queries are replicated properly

Modified Files:
--------------
    slony1-engine/tests/testinherit:
        README (r1.1 -> r1.2)
        generate_dml.sh (r1.1 -> r1.2)
        init_add_tables.ik (r1.1 -> r1.2)
        init_data.sql (r1.1 -> r1.2)
        init_schema.sql (r1.1 -> r1.2)
        schema.diff (r1.1 -> r1.2)

-------------- next part --------------
Index: init_add_tables.ik
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/tests/testinherit/init_add_tables.ik,v
 retrieving revision 1.1
retrieving revision 1.2
diff -Ltests/testinherit/init_add_tables.ik -Ltests/testinherit/init_add_tables.ik -u \
                -w -r1.1 -r1.2
--- tests/testinherit/init_add_tables.ik
+++ tests/testinherit/init_add_tables.ik
@@ -2,3 +2,9 @@
 set add table (id=2, set id=1, origin=1, fully qualified name = 'public.sub2', \
comment='sub table 2');  set add table (id=3, set id=1, origin=1, fully qualified \
name = 'public.sub3', comment='sub table 3');  set add table (id=4, set id=1, \
origin=1, fully qualified name = 'public.master', comment='master table'); +
+set add table (id=5, set id=1, origin=1, fully qualified name = 'public.regions', \
comment='lookup table'); +set add table (id=6, set id=1, origin=1, fully qualified \
name = 'public.sales_data', comment='master sales table'); +set add table (id=7, set \
id=1, origin=1, fully qualified name = 'public.us_east', comment='Eastern US'); +set \
add table (id=8, set id=1, origin=1, fully qualified name = 'public.us_west', \
comment='Western US'); +set add table (id=9, set id=1, origin=1, fully qualified name \
= 'public.canada', comment='All of Canada'); \ No newline at end of file
Index: generate_dml.sh
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/tests/testinherit/generate_dml.sh,v
retrieving revision 1.1
retrieving revision 1.2
diff -Ltests/testinherit/generate_dml.sh -Ltests/testinherit/generate_dml.sh -u -w \
                -r1.1 -r1.2
--- tests/testinherit/generate_dml.sh
+++ tests/testinherit/generate_dml.sh
@@ -48,7 +48,7 @@
     echo "INSERT INTO sub2(data) VALUES ('sub2 ${txtb}');" >> $GENDATA
     echo "INSERT INTO sub3(data) VALUES ('sub3 ${txtc}');" >> $GENDATA
     echo "INSERT INTO master(data) VALUES ('master ${txtd}');" >> $GENDATA
-
+    echo "select purchase_product( region_code, product_id, \
(random()*5+random()*8+random()*7)::integer) from regions, products order by random() \
limit 3;" >> $GENDATA  if [ ${i} -ge ${numrows} ]; then
       break;
     else
Index: schema.diff
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/tests/testinherit/schema.diff,v
retrieving revision 1.1
retrieving revision 1.2
diff -Ltests/testinherit/schema.diff -Ltests/testinherit/schema.diff -u -w -r1.1 \
                -r1.2
--- tests/testinherit/schema.diff
+++ tests/testinherit/schema.diff
@@ -1,2 +1,6 @@
 select id, trans_on, data from master order by id
+select 'main', * from only sales_data order by id
+select 'us west', * from us_west order by id
+select 'us east', * from us_east order by id
+select 'canada', * from canada order by id
 
Index: init_data.sql
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/tests/testinherit/init_data.sql,v
retrieving revision 1.1
retrieving revision 1.2
diff -Ltests/testinherit/init_data.sql -Ltests/testinherit/init_data.sql -u -w -r1.1 \
                -r1.2
--- tests/testinherit/init_data.sql
+++ tests/testinherit/init_data.sql
@@ -2,3 +2,47 @@
 insert into sub1 (data) values ('sub1 a');
 insert into sub2 (data) values ('sub1 b');
 insert into sub3 (data) values ('sub1 c');
+
+
+insert into regions (region_code, iso_country, region, city) values (1001, 'US', \
'NY', 'New York City'); +insert into regions (region_code, iso_country, region, city) \
values (1002, 'US', 'NY', 'Albany'); +insert into regions (region_code, iso_country, \
region, city) values (1003, 'US', 'MA', 'Boston'); +insert into regions (region_code, \
iso_country, region, city) values (1004, 'US', 'PH', 'Philadelphia'); +insert into \
regions (region_code, iso_country, region, city) values (1005, 'US', 'DC', \
'Washington'); +insert into regions (region_code, iso_country, region, city) values \
(1006, 'US', 'GA', 'Atlanta'); +insert into regions (region_code, iso_country, \
region, city) values (1007, 'US', 'FL', 'Miami'); +insert into regions (region_code, \
iso_country, region, city) values (1008, 'US', 'ME', 'Portland'); +insert into \
regions (region_code, iso_country, region, city) values (2000, 'US', 'OR', \
'Portland'); +insert into regions (region_code, iso_country, region, city) values \
(2001, 'US', 'CA', 'Los Angeles'); +insert into regions (region_code, iso_country, \
region, city) values (2002, 'US', 'CA', 'San Francisco'); +insert into regions \
(region_code, iso_country, region, city) values (2003, 'US', 'NV', 'Las Vegas'); \
+insert into regions (region_code, iso_country, region, city) values (2004, 'US', \
'CA', 'San Diego'); +insert into regions (region_code, iso_country, region, city) \
values (2005, 'US', 'WA', 'Seattle'); +insert into regions (region_code, iso_country, \
region, city) values (3000, 'CA', 'ON', 'Ottawa'); +insert into regions (region_code, \
iso_country, region, city) values (3001, 'CA', 'ON', 'Toronto'); +insert into regions \
(region_code, iso_country, region, city) values (3002, 'CA', 'NS', 'Halifax'); \
+insert into regions (region_code, iso_country, region, city) values (3003, 'CA', \
'AB', 'Calgary'); +insert into regions (region_code, iso_country, region, city) \
values (3004, 'CA', 'BC', 'Vancouver'); +insert into regions (region_code, \
iso_country, region, city) values (4000, 'GB', NULL, 'London'); +insert into regions \
(region_code, iso_country, region, city) values (4001, 'DE', NULL, 'Munich'); +insert \
into regions (region_code, iso_country, region, city) values (4002, 'FR', NULL, \
'Paris'); +insert into regions (region_code, iso_country, region, city) values (4003, \
'IT', NULL, 'Rome'); +insert into regions (region_code, iso_country, region, city) \
values (4004, 'EG', NULL, 'Cairo'); +insert into regions (region_code, iso_country, \
region, city) values (4005, 'JP', NULL, 'Tokyo'); +insert into regions (region_code, \
iso_country, region, city) values (4006, 'CH', NULL, 'Bejing'); +insert into regions \
(region_code, iso_country, region, city) values (4007, 'AU', NULL, 'Melbourne'); +
+insert into products (name, price) values ('widget', 275.00);
+insert into products (name, price) values ('thingamajig', 17.55);
+insert into products (name, price) values ('whatzit', 24.99);
+insert into products (name, price) values ('dunno', 8.95);
+insert into products (name, price) values ('one of those', 182.44);
+insert into products (name, price) values ('thingamabob', 18.05);
+
+select purchase_product( region_code, product_id, \
(random()*5+random()*8+random()*7)::integer) from regions, products order by random() \
limit 3; +select purchase_product( region_code, product_id, \
(random()*5+random()*8+random()*7)::integer) from regions, products order by random() \
limit 3; +select purchase_product( region_code, product_id, \
(random()*5+random()*8+random()*7)::integer) from regions, products order by random() \
limit 3; +select purchase_product( region_code, product_id, \
(random()*5+random()*8+random()*7)::integer) from regions, products order by random() \
limit 3; +select purchase_product( region_code, product_id, \
(random()*5+random()*8+random()*7)::integer) from regions, products order by random() \
limit 3; +select purchase_product( region_code, product_id, \
(random()*5+random()*8+random()*7)::integer) from regions, products order by random() \
limit 3; +
Index: init_schema.sql
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/tests/testinherit/init_schema.sql,v
retrieving revision 1.1
retrieving revision 1.2
diff -Ltests/testinherit/init_schema.sql -Ltests/testinherit/init_schema.sql -u -w \
                -r1.1 -r1.2
--- tests/testinherit/init_schema.sql
+++ tests/testinherit/init_schema.sql
@@ -16,4 +16,72 @@
 ) inherits (master);
 alter table sub3 add primary key(id);
 
+-- And a second, rather more complex instance, which does some automatic \
partitioning by region... +create table regions (
+  region_code integer primary key,
+  iso_country char(2),
+  region text,
+  city text
+);
+create unique index region_names on regions(iso_country, region, city);
+
+create table products (
+  product_id serial primary key,
+  name text unique not null,
+  price numeric(10,2) not null
+);
+
+create table sales_data (
+  id serial primary key,
+  trans_on timestamptz default 'now()',
+  region_code integer not null references regions(region_code),
+  product_id integer not null references products(product_id),
+  quantity integer not null,
+  amount numeric(12,2) not null
+);
+
+create table us_east (
+) inherits (sales_data);
+alter table us_east add primary key(id);
+alter table us_east add constraint region_chk check (region_code between 1000 and \
1999); +alter table us_east add constraint region_code foreign key(region_code) \
references regions(region_code); +alter table us_east add constraint product_code \
foreign key(product_id) references products(product_id); +
+create table us_west (
+) inherits (sales_data);
+alter table us_west add primary key(id);
+alter table us_west add constraint region_chk check (region_code between 2000 and \
2999); +alter table us_west add constraint region_code foreign key(region_code) \
references regions(region_code); +alter table us_west add constraint product_code \
foreign key(product_id) references products(product_id); +
+create table canada (
+) inherits (sales_data);
+alter table canada add primary key(id);
+alter table canada add constraint region_chk check (region_code between 3000 and \
3999); +alter table canada add constraint region_code foreign key(region_code) \
references regions(region_code); +alter table canada add constraint product_code \
foreign key(product_id) references products(product_id); +
+create rule sales_data_distribute_us_east as on insert to sales_data where \
new.region_code between 1000 and 1999 +do instead insert into us_east (id, trans_on, \
region_code, data) values (new.id, new.trans_on, new.region_code, new.data); +
+create rule sales_data_distribute_us_west as on insert to sales_data where \
new.region_code between 2000 and 2999 +do instead insert into us_west (id, trans_on, \
region_code, data) values (new.id, new.trans_on, new.region_code, new.data); +
+create rule sales_data_distribute_canada as on insert to sales_data where \
new.region_code between 3000 and 3999 +do instead insert into canada (id, trans_on, \
region_code, data) values (new.id, new.trans_on, new.region_code, new.data); +
+-- We will be doing inserts into sales_data inside the following stored proc
+create or replace function purchase_product (integer, integer, integer) returns \
numeric(12,2) as ' +declare
+   i_region alias for $1;
+   i_product alias for $2;
+   i_quantity alias for $3;
+   c_price numeric(10,2);
+   c_amount numeric(12,2);
+begin
+   select price into c_price from products where product_id = i_product;
+   c_amount := c_price * i_quantity;
+   insert into sales_data (region_code, product_id, quantity, amount) values \
(i_region, i_product, i_quantity, c_amount); +   return c_amount;
+end' language plpgsql;
 
Index: README
===================================================================
RCS file: /usr/local/cvsroot/slony1/slony1-engine/tests/testinherit/README,v
retrieving revision 1.1
retrieving revision 1.2
diff -Ltests/testinherit/README -Ltests/testinherit/README -u -w -r1.1 -r1.2
--- tests/testinherit/README
+++ tests/testinherit/README
@@ -1,6 +1,28 @@
 $Id$
   
-testinherit does some simple handling of inheritance...
+testinherit does a somewhat nontrivial handling of inheritance...
 
-A "master" table is created; three "sub tables" inherit from it, and
-all four tables are replicated.
+A "master" sales table is created which has three "sub tables" for
+three regions (US west, US east, Canada) which inherit from it, and all
+four tables are replicated.
+
+Notice that constraints do not automatically propagate to the
+inherited tables; they need to be added explicitly if direct
+manipulations to the inherited tables are to retain referential
+integrity.  (Mind you, inserts to the "master" table are checked at
+creation time, so as long as you don't do modifications to the
+inherited tables, you at least start with referential integrity...)
+
+Rules are added to cause the data for those three regions to be
+automatically directed to the right partition table.
+
+There is also a product table, products...
+
+A stored procedure, purchase_product, is used to generate sales data,
+demonstrating that it's fine to use stored procs with Slony-I;
+replication will pick up updates even if they weren't expressly
+declared.
+
+The queries used to request product purchases use random() to choose
+products and regions, demonstrating that Slony-I copes perfectly well
+with nondeterministic queries.


[prev in list] [next in list] [prev in thread] [next in thread]