diff options
| -rw-r--r-- | python/londiste/handlers/dispatch.py | 4 | ||||
| -rw-r--r-- | sql/dispatch/create_partition.sql | 85 | ||||
| -rw-r--r-- | sql/dispatch/expected/test_create_part.out | 8 | ||||
| -rw-r--r-- | sql/dispatch/sql/test_create_part.sql | 10 |
4 files changed, 70 insertions, 37 deletions
diff --git a/python/londiste/handlers/dispatch.py b/python/londiste/handlers/dispatch.py index 8954d2bf..95d08e72 100644 --- a/python/londiste/handlers/dispatch.py +++ b/python/londiste/handlers/dispatch.py @@ -886,10 +886,6 @@ class Dispatcher(BaseHandler): if skytools.exists_function(curs, self.conf.part_func, len(PART_FUNC_ARGS)): self.log.debug('check_part.exec: func:%s, args: %s' % (pfcall, vals)) curs.execute(pfcall, vals) - - # load grants from master table - struct = TableStruct(curs, self.dest_table) - struct.create(curs, T_GRANT, dst) else: self.log.debug('part func %s not found, cloning table' % self.conf.part_func) struct = TableStruct(curs, self.dest_table) diff --git a/sql/dispatch/create_partition.sql b/sql/dispatch/create_partition.sql index cfe72250..bd2c8516 100644 --- a/sql/dispatch/create_partition.sql +++ b/sql/dispatch/create_partition.sql @@ -1,12 +1,5 @@ -- drop old function with timestamp -DROP FUNCTION IF EXISTS public.create_partition( - text, - text, - text, - text, - timestamp, - text -); +DROP FUNCTION IF EXISTS public.create_partition(text, text, text, text, timestamp, text); CREATE OR REPLACE FUNCTION public.create_partition( i_table text, @@ -15,9 +8,7 @@ CREATE OR REPLACE FUNCTION public.create_partition( i_part_field text, i_part_time timestamptz, i_part_period text -) RETURNS int -AS $$ - +) RETURNS int AS $$ ------------------------------------------------------------------------ -- Function: public.create_partition -- @@ -40,24 +31,30 @@ declare chk_start text; chk_end text; part_start timestamptz; - table_schema text; - table_name text; + parent_schema text; + parent_name text; part_schema text; part_name text; pos int4; fq_table text; fq_part text; + g record; + sql text; + pgver integer; begin + -- load postgres version (XYYZZ). + show server_version_num into pgver; + -- parent table schema and name + quoted name pos := position('.' in i_table); if pos > 0 then - table_schema := substring(i_table for pos - 1); - table_name := substring(i_table from pos + 1); + parent_schema := substring(i_table for pos - 1); + parent_name := substring(i_table from pos + 1); else - table_schema := 'public'; - table_name := i_table; + parent_schema := 'public'; + parent_name := i_table; end if; - fq_table := quote_ident(table_schema) || '.' || quote_ident(table_name); + fq_table := quote_ident(parent_schema) || '.' || quote_ident(parent_name); -- part table schema and name + quoted name pos := position('.' in i_part); @@ -80,24 +77,46 @@ begin and t.relname = part_name; if found then return 0; + end if; + + -- need to use 'like' to get indexes + sql := 'create table ' || fq_part || ' (like ' || fq_table; + if pgver >= 90000 then + sql := sql || ' including all'; else - -- need to use 'like' to get indexes - execute 'create table ' || fq_part - || ' (like ' || fq_table || ' including indexes including constraints)' - -- || ' () ' - || ' inherits (' || fq_table || ')'; + sql := sql || ' including indexes including constraints'; + end if; + sql := sql || ') inherits (' || fq_table || ')'; + execute sql; - if i_part_field != '' then - part_start := date_trunc(i_part_period, i_part_time); - chk_start := to_char(part_start, 'YYYY-MM-DD HH24:MI:SS'); - chk_end := to_char(part_start + ('1 '||i_part_period)::interval, - 'YYYY-MM-DD HH24:MI:SS'); - execute 'alter table '|| fq_part ||' add check(' || i_part_field || ' >= ''' - || chk_start ||''' and ' || i_part_field || ' < ''' || chk_end || ''')'; - end if; + if i_part_field != '' then + part_start := date_trunc(i_part_period, i_part_time); + chk_start := to_char(part_start, 'YYYY-MM-DD HH24:MI:SS'); + chk_end := to_char(part_start + ('1 '||i_part_period)::interval, + 'YYYY-MM-DD HH24:MI:SS'); + sql := 'alter table '|| fq_part || ' add check(' + || i_part_field || ' >= ''' || chk_start || ''' and ' + || i_part_field || ' < ''' || chk_end || ''')'; + execute sql; end if; + + -- load grants from parent table + for g in + select grantor, grantee, privilege_type, is_grantable + from information_schema.table_privileges + where table_schema = parent_schema + and table_name = parent_name + loop + sql := 'grant ' || g.privilege_type || ' on ' || fq_part + || ' to ' || quote_ident(g.grantee); + if g.is_grantable = 'YES' and g.grantor <> g.grantee then + sql := sql || ' with grant option'; + end if; + execute sql; + end loop; + return 1; end; -$$ -LANGUAGE plpgsql; +$$ language plpgsql; + diff --git a/sql/dispatch/expected/test_create_part.out b/sql/dispatch/expected/test_create_part.out index 13dfb7c4..20a9ef21 100644 --- a/sql/dispatch/expected/test_create_part.out +++ b/sql/dispatch/expected/test_create_part.out @@ -1,4 +1,8 @@ \set ECHO none +drop role if exists ptest1; +drop role if exists ptest2; +create group ptest1; +create group ptest2; create table events ( id int4 primary key, txt text not null, @@ -6,6 +10,8 @@ create table events ( someval int4 check (someval > 0) ); create index ctime_idx on events (ctime); +grant select,delete on events to ptest1; +grant select,update,delete on events to ptest2 with grant option; select create_partition('events', 'events_2011_01', 'id', 'ctime', '2011-01-01', 'month'); create_partition ------------------ @@ -37,3 +43,5 @@ select count(*) from pg_constraint where conrelid = 'public.events_2011_01'::reg (1 row) -- \d events_2011_01 +-- \dp events +-- \dp events_2011_01 diff --git a/sql/dispatch/sql/test_create_part.sql b/sql/dispatch/sql/test_create_part.sql index f04a9eff..71a552a1 100644 --- a/sql/dispatch/sql/test_create_part.sql +++ b/sql/dispatch/sql/test_create_part.sql @@ -7,6 +7,11 @@ set client_min_messages = 'warning'; \i create_partition.sql \set ECHO all +drop role if exists ptest1; +drop role if exists ptest2; +create group ptest1; +create group ptest2; + create table events ( id int4 primary key, txt text not null, @@ -15,6 +20,9 @@ create table events ( ); create index ctime_idx on events (ctime); +grant select,delete on events to ptest1; +grant select,update,delete on events to ptest2 with grant option; + select create_partition('events', 'events_2011_01', 'id', 'ctime', '2011-01-01', 'month'); select create_partition('events', 'events_2011_01', 'id', 'ctime', '2011-01-01'::timestamptz, 'month'); @@ -24,4 +32,6 @@ select count(*) from pg_indexes where schemaname='public' and tablename = 'event select count(*) from pg_constraint where conrelid = 'public.events_2011_01'::regclass; -- \d events_2011_01 +-- \dp events +-- \dp events_2011_01 |