From 1618a622da732446b6004d34e2c46a5e92fc5be6 Mon Sep 17 00:00:00 2001 From: Marko Kreen Date: Fri, 25 May 2012 16:31:53 +0300 Subject: [PATCH] londiste.create_partitions: improved rule support --- .../expected/londiste_create_part.out | 84 +++++++++++++++++++ .../functions/londiste.create_partition.sql | 48 +++++++++-- sql/londiste/sql/londiste_create_part.sql | 61 ++++++++++++++ 3 files changed, 187 insertions(+), 6 deletions(-) create mode 100644 sql/londiste/expected/londiste_create_part.out create mode 100644 sql/londiste/sql/londiste_create_part.sql diff --git a/sql/londiste/expected/londiste_create_part.out b/sql/londiste/expected/londiste_create_part.out new file mode 100644 index 00000000..3036d92d --- /dev/null +++ b/sql/londiste/expected/londiste_create_part.out @@ -0,0 +1,84 @@ +\set ECHO none +drop role if exists londiste_test_part1; +drop role if exists londiste_test_part2; +create group londiste_test_part1; +create group londiste_test_part2; +create table events ( + id int4 primary key, + txt text not null, + ctime timestamptz not null default now(), + someval int4 check (someval > 0) +); +create index ctime_idx on events (ctime); +create rule ignore_dups AS + on insert to events + where (exists (select 1 from events + where (events.id = new.id))) + do instead nothing; +grant select,delete on events to londiste_test_part1; +grant select,update,delete on events to londiste_test_part2 with grant option; +grant select,insert on events to public; +select londiste.create_partition('events', 'events_2011_01', 'id', 'ctime', '2011-01-01', 'month'); + create_partition +------------------ + 1 +(1 row) + +select londiste.create_partition('events', 'events_2011_01', 'id', 'ctime', '2011-01-01'::timestamptz, 'month'); + create_partition +------------------ + 0 +(1 row) + +select londiste.create_partition('events', 'events_2011_01', 'id', 'ctime', '2011-01-01'::timestamp, 'month'); + create_partition +------------------ + 0 +(1 row) + +select count(*) from pg_indexes where schemaname='public' and tablename = 'events_2011_01'; + count +------- + 2 +(1 row) + +select count(*) from pg_constraint where conrelid = 'public.events_2011_01'::regclass; + count +------- + 3 +(1 row) + +select count(*) from pg_rules where schemaname = 'public' and tablename = 'events_2011_01'; + count +------- + 1 +(1 row) + +-- test weird quoting +create table "Bad "" table '.' name!" ( + id int4 primary key, + txt text not null, + ctime timestamptz not null default now(), + someval int4 check (someval > 0) +); +create rule "Ignore Dups" AS + on insert to "Bad "" table '.' name!" + where (exists (select 1 from "Bad "" table '.' name!" + where ("Bad "" table '.' name!".id = new.id))) + do instead nothing; +alter table "Bad "" table '.' name!" ENABLE ALWAYS RULE "Ignore Dups"; +select londiste.create_partition('public.Bad " table ''.'' name!', 'public.Bad " table ''.'' part!', 'id', 'ctime', '2011-01-01', 'month'); + create_partition +------------------ + 1 +(1 row) + +select count(*) from pg_rules where schemaname = 'public' and tablename ilike 'bad%'; + count +------- + 2 +(1 row) + +-- \d events_2011_01 +-- \dp events +-- \dp events_2011_01 diff --git a/sql/londiste/functions/londiste.create_partition.sql b/sql/londiste/functions/londiste.create_partition.sql index 7267defc..94c71677 100644 --- a/sql/londiste/functions/londiste.create_partition.sql +++ b/sql/londiste/functions/londiste.create_partition.sql @@ -13,6 +13,9 @@ create or replace function londiste.create_partition( -- Creates child table for aggregation function for either monthly or daily if it does not exist yet. -- Locks parent table for child table creating. -- +-- Problem: +-- Grants and rules should be part of CREATE TABLE x (LIKE y INCLUDING ALL)'. +-- -- Parameters: -- i_table - name of parent table -- i_part - name of partition table to create @@ -42,6 +45,9 @@ declare r record; sql text; pgver integer; + r_oldtbl text; + r_extra text; + r_sql text; begin if i_table is null or i_part is null then raise exception 'need table and part'; @@ -127,14 +133,44 @@ begin -- copy rules for r in - select rulename, definition, - substring(definition from ' TO (([a-z0-9._]+|"([^"]+|"")+")+)') as oldtbl - from pg_catalog.pg_rules - where schemaname = parent_schema - and tablename = parent_name + select rw.rulename, rw.ev_enabled, pg_get_ruledef(rw.oid) as definition + from pg_catalog.pg_rewrite rw + where rw.ev_class = fq_table::regclass::oid + and rw.rulename <> '_RETURN'::name loop - sql := replace(r.definition, r.oldtbl, fq_part); + -- try to skip rule name + r_extra := 'CREATE RULE ' || quote_ident(r.rulename) || ' AS'; + r_sql := substr(r.definition, 1, char_length(r_extra)); + if r_sql = r_extra then + r_sql := substr(r.definition, char_length(r_extra)); + else + raise exception 'failed to match rule name'; + end if; + + -- no clue what name was used in defn, so find it from sql + r_oldtbl := substring(r_sql from ' TO (([[:alnum:]_.]+|"([^"]+|"")+")+)[[:space:]]'); + if char_length(r_oldtbl) > 0 then + sql := replace(r.definition, r_oldtbl, fq_part); + else + raise exception 'failed to find original table name'; + end if; execute sql; + + -- rule flags + r_extra := NULL; + if r.ev_enabled = 'R' then + r_extra = ' ENABLE REPLICA RULE '; + elsif r.ev_enabled = 'A' then + r_extra = ' ENABLE ALWAYS RULE '; + elsif r.ev_enabled = 'D' then + r_extra = ' DISABLE RULE '; + elsif r.ev_enabled <> 'O' then + raise exception 'unknown rule option: %', r.ev_enabled; + end if; + if r_extra is not null then + sql := 'ALTER TABLE ' || fq_part || r_extra + || quote_ident(r.rulename); + end if; end loop; return 1; diff --git a/sql/londiste/sql/londiste_create_part.sql b/sql/londiste/sql/londiste_create_part.sql new file mode 100644 index 00000000..4ee3e325 --- /dev/null +++ b/sql/londiste/sql/londiste_create_part.sql @@ -0,0 +1,61 @@ + + +\set ECHO none +set log_error_verbosity = 'terse'; +set client_min_messages = 'warning'; +\set ECHO all + +drop role if exists londiste_test_part1; +drop role if exists londiste_test_part2; +create group londiste_test_part1; +create group londiste_test_part2; + +create table events ( + id int4 primary key, + txt text not null, + ctime timestamptz not null default now(), + someval int4 check (someval > 0) +); +create index ctime_idx on events (ctime); + +create rule ignore_dups AS + on insert to events + where (exists (select 1 from events + where (events.id = new.id))) + do instead nothing; + + + +grant select,delete on events to londiste_test_part1; +grant select,update,delete on events to londiste_test_part2 with grant option; +grant select,insert on events to public; + +select londiste.create_partition('events', 'events_2011_01', 'id', 'ctime', '2011-01-01', 'month'); +select londiste.create_partition('events', 'events_2011_01', 'id', 'ctime', '2011-01-01'::timestamptz, 'month'); +select londiste.create_partition('events', 'events_2011_01', 'id', 'ctime', '2011-01-01'::timestamp, 'month'); + +select count(*) from pg_indexes where schemaname='public' and tablename = 'events_2011_01'; +select count(*) from pg_constraint where conrelid = 'public.events_2011_01'::regclass; +select count(*) from pg_rules where schemaname = 'public' and tablename = 'events_2011_01'; + +-- test weird quoting + +create table "Bad "" table '.' name!" ( + id int4 primary key, + txt text not null, + ctime timestamptz not null default now(), + someval int4 check (someval > 0) +); +create rule "Ignore Dups" AS + on insert to "Bad "" table '.' name!" + where (exists (select 1 from "Bad "" table '.' name!" + where ("Bad "" table '.' name!".id = new.id))) + do instead nothing; +alter table "Bad "" table '.' name!" ENABLE ALWAYS RULE "Ignore Dups"; +select londiste.create_partition('public.Bad " table ''.'' name!', 'public.Bad " table ''.'' part!', 'id', 'ctime', '2011-01-01', 'month'); +select count(*) from pg_rules where schemaname = 'public' and tablename ilike 'bad%'; + +-- \d events_2011_01 +-- \dp events +-- \dp events_2011_01 + -- 2.39.5