Module: PgPartitioner::SeparationType::Month

Defined in:
lib/pg_partitioner/separation_type/month.rb

Instance Method Summary collapse

Instance Method Details

#create_current_month_table ⇒ Object 



4
5
6
 
# File 'lib/pg_partitioner/separation_type/month.rb', line 4

def create_current_month_table
  create_month_table(Date.today)
end

#create_month_table(date = Date.today) ⇒ Object 



16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
# File 'lib/pg_partitioner/separation_type/month.rb', line 16

def create_month_table(date = Date.today)
  date_start = date.at_beginning_of_month
  date_end = date.at_beginning_of_month.next_month
  partition_table_name = name_of_partition_table(date)
  sql = "CREATE TABLE IF NOT EXISTS #{partition_table_name} (
         CHECK ( #{parting_column} >= DATE('#{date_start}') AND #{parting_column} < DATE('#{date_end}') )
         ) INHERITS (#{table_name});"
  execute_sql(sql)
  sql = "ALTER TABLE #{partition_table_name} ADD PRIMARY KEY (id);"
  execute_sql(sql)

  create_partition_indexes(partition_table_name)
  create_partition_named_indexes(partition_table_name)
  create_partition_unique_indexes(partition_table_name)
  create_partition_named_unique_indexes(partition_table_name)
end

#create_next_month_table ⇒ Object 



8
9
10
 
# File 'lib/pg_partitioner/separation_type/month.rb', line 8

def create_next_month_table
  create_month_table(Date.today.next_month)
end

#create_partition_indexes(partition_table_name) ⇒ Object 



87
88
89
90
91
92
 
# File 'lib/pg_partitioner/separation_type/month.rb', line 87

def create_partition_indexes(partition_table_name)
  custom_indexes = partition_table_indexes.presence
  return unless custom_indexes

  custom_indexes.each { |custom_index| create_custom_index(partition_table_name, custom_index) }
end

#create_partition_named_indexes(partition_table_name) ⇒ Object 



94
95
96
97
98
99
100
101
102
 
# File 'lib/pg_partitioner/separation_type/month.rb', line 94

def create_partition_named_indexes(partition_table_name)
  custom_indexes = partition_table_named_indexes.presence
  return unless custom_indexes

  custom_indexes.map do |name, custom_index|
    index_name = "index_#{partition_table_name}_#{name}"
    create_custom_named_index(partition_table_name, custom_index, index_name)
  end
end

#create_partition_named_unique_indexes(partition_table_name) ⇒ Object 



111
112
113
114
115
116
117
118
119
 
# File 'lib/pg_partitioner/separation_type/month.rb', line 111

def create_partition_named_unique_indexes(partition_table_name)
  custom_indexes = partition_table_named_unique_indexes.presence
  return unless custom_indexes

  custom_indexes.map do |name, custom_index|
    index_name = "index_#{partition_table_name}_#{name}"
    create_custom_named_index(partition_table_name, custom_index, index_name, true)
  end
end

#create_partition_unique_indexes(partition_table_name) ⇒ Object 



104
105
106
107
108
109
 
# File 'lib/pg_partitioner/separation_type/month.rb', line 104

def create_partition_unique_indexes(partition_table_name)
  custom_unique_indexes = partition_table_unique_indexes.presence
  return unless custom_unique_indexes

  custom_unique_indexes.each { |custom_index| create_custom_index(partition_table_name, custom_index, true) }
end

#create_partitioning_by_month_triggers ⇒ Object 



38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
 
# File 'lib/pg_partitioner/separation_type/month.rb', line 38

def create_partitioning_by_month_triggers
  sql = "CREATE OR REPLACE FUNCTION #{table_name}_insert_trigger() RETURNS trigger AS
    $$
     DECLARE
       curY varchar(4);
       curM varchar(2);
       tbl varchar(40);
     BEGIN
        select cast(DATE_PART('year', new.#{parting_column}) as varchar) into curY;
        select lpad(cast(DATE_PART('month', new.#{parting_column}) as varchar), 2, '0') into curM;
        tbl := '#{table_name}_y' || curY || 'm' || curM;
        EXECUTE format('INSERT into %I values ($1.*);', tbl) USING NEW;
        return NEW;
     END;
     $$
   LANGUAGE plpgsql;

   CREATE TRIGGER #{table_name}_insert
   BEFORE INSERT ON #{table_name}
   FOR EACH ROW
   EXECUTE PROCEDURE #{table_name}_insert_trigger();

   -- Trigger function to delete from the master table after the insert
   CREATE OR REPLACE FUNCTION #{table_name}_delete_trigger() RETURNS trigger
       AS $$
   DECLARE
       r #{table_name}%rowtype;
   BEGIN
       DELETE FROM ONLY #{table_name} where id = new.id returning * into r;
       RETURN r;
   end;
   $$
   LANGUAGE plpgsql;

   CREATE TRIGGER #{table_name}_after_insert
   AFTER INSERT ON #{table_name}
   FOR EACH ROW
   EXECUTE PROCEDURE #{table_name}_delete_trigger();"

  execute_sql(sql)
end

#drop_month_table(date = Date.today) ⇒ Object 



33
34
35
36
 
# File 'lib/pg_partitioner/separation_type/month.rb', line 33

def drop_month_table(date = Date.today)
  sql = "DROP TABLE IF EXISTS #{name_of_partition_table(date)};"
  execute_sql(sql)
end

#drop_old_month_table ⇒ Object 



12
13
14
 
# File 'lib/pg_partitioner/separation_type/month.rb', line 12

def drop_old_month_table
  drop_month_table(Date.today.prev_month.prev_month)
end

#drop_partitioning_by_month_trigger_sql ⇒ Object 



80
81
82
83
84
85
 
# File 'lib/pg_partitioner/separation_type/month.rb', line 80

def drop_partitioning_by_month_trigger_sql
  "DROP TRIGGER #{table_name}_insert ON #{table_name};
   DROP FUNCTION #{table_name}_insert_trigger();
   DROP TRIGGER #{table_name}_after_insert ON #{table_name};
   DROP FUNCTION #{table_name}_delete_trigger();"
end

#name_of_partition_table(date = Date.today) ⇒ Object 



121
122
123
 
# File 'lib/pg_partitioner/separation_type/month.rb', line 121

def name_of_partition_table(date = Date.today)
  date.strftime("#{table_name}_y%Ym%m")
end