Merge remote-tracking branch 'gamato/master'

4 files changed, 81 insertions, 3 deletions

diff --git a/sql/londiste/functions/londiste.create_partition.sql b/sql/londiste/functions/londiste.create_partition.sql
index b13448b4..7528acdc 100644
--- a/sql/londiste/functions/londiste.create_partition.sql
+++ b/sql/londiste/functions/londiste.create_partition.sql
@@ -47,6 +47,7 @@ declare
     parent_oid      oid;
     part_schema     text;
     part_name       text;
+    owner           name;
     pos             int4;
     fq_table        text;
     fq_part         text;
@@ -112,6 +113,20 @@ begin
     sql := sql || ') inherits (' || fq_table || ')';
     execute sql;
 
+    -- find out parent table owner
+    select o.rolname into owner
+      from pg_class t, pg_namespace s, pg_roles o
+     where t.relnamespace = s.oid
+       and s.nspname = parent_schema
+       and t.relname = parent_name
+       and t.relowner = o.oid;
+
+    -- set proper part table ownership
+    if owner != user then
+        sql = 'alter table ' || fq_part || ' owner to ' || quote_ident(owner);
+        execute sql;
+    end if;
+
     -- extra check constraint
     if i_part_field != '' then
         part_start := date_trunc(i_part_period, i_part_time);
@@ -183,7 +198,7 @@ begin
 
     -- copy rules
     for r in
-        select rw.rulename, rw.ev_enabled, pg_get_ruledef(rw.oid) as definition
+        select rw.rulename, rw.ev_enabled, pg_catalog.pg_get_ruledef(rw.oid) as definition
           from pg_catalog.pg_rewrite rw
          where rw.ev_class = parent_oid
            and rw.rulename <> '_RETURN'::name
@@ -226,4 +241,3 @@ begin
     return 1;
 end;
 $$ language plpgsql;
-
diff --git a/sql/londiste/functions/londiste.drop_obsolete_partitions.sql b/sql/londiste/functions/londiste.drop_obsolete_partitions.sql
new file mode 100644
index 00000000..ee18d904
--- /dev/null
+++ b/sql/londiste/functions/londiste.drop_obsolete_partitions.sql
@@ -0,0 +1,62 @@
+
+create or replace function londiste.drop_obsolete_partitions
+(
+    in i_parent_table text,
+    in i_retention_period interval,
+    in i_partition_period text
+)
+    returns setof text
+as $$
+-------------------------------------------------------------------------------
+--  Function: londiste.drop_obsolete_partitions(3)
+--
+--    Drop obsolete partitions of partition-by-date parent table.
+--
+--  Parameters:
+--    i_parent_table        Master table from which partitions are inherited
+--    i_retention_period    How long to keep partitions around
+--    i_partition_period    One of: year, month, day, hour
+--
+--  Returns:
+--    Names of partitions dropped
+-------------------------------------------------------------------------------
+declare
+    _schema text not null := lower (split_part (i_parent_table, '.', 1));
+    _table  text not null := lower (split_part (i_parent_table, '.', 2));
+    _part   text;
+    _expr   text;
+    _dfmt   text;
+begin
+    if i_partition_period in ('year', 'yearly') then
+        _expr := '_[0-9]{4}';
+        _dfmt := '_YYYY';
+    elsif i_partition_period in ('month', 'monthly') then
+        _expr := '_[0-9]{4}_[0-9]{2}';
+        _dfmt := '_YYYY_MM';
+    elsif i_partition_period in ('day', 'daily') then
+        _expr := '_[0-9]{4}_[0-9]{2}_[0-9]{2}';
+        _dfmt := '_YYYY_MM_DD';
+    elsif i_partition_period in ('hour', 'hourly') then
+        _expr := '_[0-9]{4}_[0-9]{2}_[0-9]{2}_[0-9]{2}';
+        _dfmt := '_YYYY_MM_DD_HH24';
+    else
+        raise exception 'not supported i_partition_period: %', i_partition_period;
+    end if;
+
+    if length (_table) = 0 then
+        _table := _schema;
+        _schema := 'public';
+    end if;
+
+    for _part in
+        select quote_ident (t.schemaname) ||'.'|| quote_ident (t.tablename)
+          from pg_catalog.pg_tables t
+         where t.schemaname = _schema
+           and t.tablename ~ ('^'|| _table || _expr ||'$')
+           and t.tablename < _table || to_char (now() - i_retention_period, _dfmt)
+    loop
+        execute 'drop table '|| _part;
+        return next _part;
+    end loop;
+end;
+$$ language plpgsql;
diff --git a/sql/londiste/structure/functions.sql b/sql/londiste/structure/functions.sql
index 560185ae..5936536b 100644
--- a/sql/londiste/structure/functions.sql
+++ b/sql/londiste/structure/functions.sql
@@ -52,4 +52,5 @@ select londiste.upgrade_schema();
 
 -- Group: Utility functions for handlers
 \i functions/londiste.create_partition.sql
+\i functions/londiste.drop_obsolete_partitions.sql
 
diff --git a/sql/londiste/structure/grants.ini b/sql/londiste/structure/grants.ini
index 9907989f..df88dacc 100644
--- a/sql/londiste/structure/grants.ini
+++ b/sql/londiste/structure/grants.ini
@@ -85,5 +85,6 @@ londiste_local_fns =
 	londiste.local_set_table_struct(text, text, text),
 	londiste.drop_table_triggers(text, text),
 	londiste.table_info_trigger(),
-	londiste.create_partition(text,text,text,text,timestamptz,text)
+	londiste.create_partition(text, text, text, text, timestamptz, text),
+	londiste.drop_obsolete_partitions (text, interval, text)