Class: Partitioned::PartitionedBase::RedshiftSqlAdapter

Inherits:
Object
  • Object
show all
Extended by:
Forwardable
Defined in:
lib/partitioned/partitioned_base/redshift_sql_adapter.rb

Overview

SqlAdapter manages requests of partitioned tables.

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(parent_table_class) ⇒ RedshiftSqlAdapter

Returns a new instance of RedshiftSqlAdapter.



12
13
14
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 12

def initialize(parent_table_class)
  @parent_table_class = parent_table_class
end

Instance Attribute Details

#parent_table_class ⇒ Object (readonly)

Returns the value of attribute parent_table_class.



10
11
12
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 10

def parent_table_class
  @parent_table_class
end

Instance Method Details

#add_parent_table_rules(*partition_key_values) ⇒ Object

Used to create the parent table rule to ensure.

This will cause an error on attempt to insert into the parent table.

We want all records to exist in one of the child tables so the query planner can optimize access to the records.



95
96
97
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 95

def add_parent_table_rules(*partition_key_values)
  # XXX nothing can be done here
end

#add_partition_table_index(*partition_key_values) ⇒ Object

Add indexes that must exist on child tables. Only leaf child tables need indexes as parent table indexes are not used in postgres.



138
139
140
141
142
143
144
145
146
147
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 138

def add_partition_table_index(*partition_key_values)
  configurator.indexes(*partition_key_values).each do |field,options|
    used_options = options.clone
    unless used_options.has_key?(:name)
      name = [*field].join('_')
      used_options[:name] = used_options[:unique] ? unique_index_name(name, *partition_key_values) : index_name(name, *partition_key_values)
    end
    add_index(partition_table_name(*partition_key_values), field, used_options)
  end
end

#add_references_to_partition_table(*partition_key_values) ⇒ Object

This is here for derived classes to set up references to added columns (or columns in the parent that need foreign key constraints).

Foreign keys are not inherited in postgres. So, a parent table of the form:

-- this is the referenced table
create table companies
(
    id               serial not null primary key,
    created_at       timestamp not null default now(),
    updated_at       timestamp,
    name             text not null
);

-- this is the parent table
create table employees
(
    id               serial not null primary key,
    created_at       timestamp not null default now(),
    updated_at       timestamp,
    name             text not null,
    company_id       integer not null references companies,
    supervisor_id    integer not null references employees
);

-- some children
create table employees_of_company_1 ( CHECK ( company_id = 1 ) ) INHERITS (employees);
create table employees_of_company_2 ( CHECK ( company_id = 2 ) ) INHERITS (employees);
create table employees_of_company_3 ( CHECK ( company_id = 3 ) ) INHERITS (employees);

Since postgres does not inherit referential integrity from parent tables, the following insert will work:

insert into employees_of_company_1 (name, company_id, supervisor_id) values ('joe', 1, 10);

even if there is no record in companies with id = 1 and there is no record in employees with id = 10

For proper referential integrity handling you must do the following:

ALTER TABLE employees_of_company_1 add foreign key (company_id) references companies(id)
ALTER TABLE employees_of_company_2 add foreign key (company_id) references companies(id)
ALTER TABLE employees_of_company_3 add foreign key (company_id) references companies(id)

ALTER TABLE employees_of_company_1 add foreign key (supervisor_id) references employees_of_company_1(id)
ALTER TABLE employees_of_company_2 add foreign key (supervisor_id) references employees_of_company_2(id)
ALTER TABLE employees_of_company_3 add foreign key (supervisor_id) references employees_of_company_3(id)

The second set of alter tables brings up a good another consideration about postgres references and partitions. postgres will not follow references to a child table. So, a foreign key reference to “employees” in this set of alter statements would not work because postgres would expect the table “employees” to have the specific referenced record, but the record really exists in a child of employees. So, the alter statement forces the reference check on the specific child table we know must contain this employees supervisor (since such a supervisor would have to work for the same company in our model).



223
224
225
226
227
228
229
230
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 223

def add_references_to_partition_table(*partition_key_values)
  configurator.foreign_keys(*partition_key_values).each do |foreign_key|
    add_foreign_key(partition_table_name(*partition_key_values),
                    foreign_key.referencing_field,
                    foreign_key.referenced_table,
                    foreign_key.referenced_field)
  end
end

#create_partition_schema(*partition_key_values) ⇒ Object

Child tables whose parent table is ‘foos’, typically exist in a schema named foos_partitions.

*partition_key_values are needed here to support the use of multiple schemas to keep tables in.



33
34
35
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 33

def create_partition_schema(*partition_key_values)
  create_schema(configurator.schema_name, :unless_exists => true)
end

#create_partition_table(*partition_key_values) ⇒ Object

Create a single child table.



116
117
118
119
120
121
122
123
124
125
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 116

def create_partition_table(*partition_key_values)
  options = {
    :partitioned_model => @parent_table_class,
    :temporary => false,
    :table_name => configurator.part_name(*partition_key_values)
  }
  @redshift_table_creator =
    ::ActiverecordRedshift::TableManager.new(@parent_table_class.connection, options)
  @redshift_table_creator.duplicate_table
end

#drop_partition_table(*partition_key_values) ⇒ Object

Remove a specific single child table.



130
131
132
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 130

def drop_partition_table(*partition_key_values)
  drop_table(configurator.table_name(*partition_key_values))
end

#ensure_always_fail_on_insert_exists ⇒ Object

Ensure our function for warning about improper partition usage is in place.

Name: always_fail_on_insert(text); Type: FUNCTION; Schema: public

Used to raise an exception explaining why a specific insert (into a parent table which should never have records) should never be attempted.



24
25
26
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 24

def ensure_always_fail_on_insert_exists
  # XXX nothing can be done here
end

#index_name(name, *partition_key_values) ⇒ Object

Used to create index names.



159
160
161
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 159

def index_name(name, *partition_key_values)
  return "#{configurator.part_name(*partition_key_values)}_#{name}_idx"
end

#last_n_partition_names(how_many = 1) ⇒ Object

Returns an array of partition table names from last to first limited to the number of entries requested by its first parameter.

The magic here is in the overridden method “last_n_partitions_order_by_clause” which is designed to order a list of partition table names (table names without their schema name) from last to first.

If the child table names are the format “pYYYYMMDD” where YYYY is a four digit year, MM is a month number and DD is a day number, you would use the following to order from last to first:

tablename desc

For child table names of the format “pXXXX” where XXXX is a number, you may want something like:

substring(tablename, 2)::integer desc

For clarity, the sql executed is:

select tablename from pg_tables where schemaname = $1 order by $2 limit $3

where:

$1 = the name of schema (foos_partitions)
$2 = the order by clause that would make the greatest table name listed first
$3 = the parameter 'how_many'



71
72
73
74
75
76
77
78
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 71

def last_n_partition_names(how_many = 1)
  return find(:all,
              :from => "pg_tables",
              :select => :tablename,
              :conditions  => ["schemaname = ?", configurator.schema_name],
              :order => last_n_partitions_order_by_clause,
              :limit => how_many).map(&:tablename)
end

#last_n_partitions_order_by_clause ⇒ Object

Override this or order the tables from last (greatest value? greatest date?) to first.



83
84
85
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 83

def last_n_partitions_order_by_clause
  return configurator.last_partitions_order_by_clause
end

#parent_table_rule_name(name, suffix = "rule", *partition_key_values) ⇒ Object

Used when creating the name of a SQL rule.



152
153
154
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 152

def parent_table_rule_name(name, suffix = "rule", *partition_key_values)
  return "#{configurator.table_name(*partition_key_values).gsub(/[.]/, '_')}_#{name}_#{suffix}"
end

#partition_exists?(*partition_key_values) ⇒ Boolean

Does a specific child partition exist.

Returns:

  • (Boolean) 


40
41
42
43
44
45
46
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 40

def partition_exists?(*partition_key_values)
  return find_by_sql([
                       "SELECT COUNT(*) as count FROM pg_tables WHERE schemaname = ? AND tablename = ?;",
                       configurator.schema_name,
                       configurator.part_name(*partition_key_values)
                     ]).first.count.to_i == 1
end

#partition_table_alias_name(*partition_key_values) ⇒ Object

A reasonable alias for the partition table



109
110
111
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 109

def partition_table_alias_name(*partition_key_values)
  return configurator.table_alias_name(*partition_key_values)
end

#partition_table_name(*partition_key_values) ⇒ Object

The name of the table (schemaname.childtablename) given the check constraint values.



102
103
104
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 102

def partition_table_name(*partition_key_values)
  return configurator.table_name(*partition_key_values)
end

#unique_index_name(name, *partition_key_values) ⇒ Object

Used to create index names.



166
167
168
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 166

def unique_index_name(name, *partition_key_values)
  return "#{configurator.part_name(*partition_key_values)}_#{name}_udx"
end