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.



97
98
99
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 97

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.



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

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).



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

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.



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

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.



132
133
134
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 132

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.



161
162
163
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 161

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'



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

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.



85
86
87
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 85

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.



154
155
156
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 154

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
47
48
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 40

def partition_exists?(*partition_key_values)
  return find(:first,
              :from => "pg_tables",
              :select => "count(*) as count",
              :conditions  => ["schemaname = ? and tablename = ?",
                                configurator.schema_name,
                                configurator.part_name(*partition_key_values)
              ]).count.to_i == 1
end

#partition_table_alias_name(*partition_key_values) ⇒ Object

A reasonable alias for the partition table



111
112
113
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 111

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.



104
105
106
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 104

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.



168
169
170
 
# File 'lib/partitioned/partitioned_base/redshift_sql_adapter.rb', line 168

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