Class: Rake::DataTask::Greenplum

Inherits:
Postgres show all
Defined in:
lib/data_task/adapters/greenplum.rb

Constant Summary collapse

TABLE_TRACKER_HELPER_NAME = 
"operations"

Constants inherited from Db

Db::LOG, Db::TABLE_TRACKER_COLUMNS, Db::TABLE_TRACKER_NAME

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from Postgres

#[], #create_table, #create_view, #drop_view, #execute, #initialize, #operations_supported, #reset_tracking, #set_up_tracking, #table_exists?, #table_mtime, #table_tracker_columns, #tear_down_tracking, #truncate_table, #view_exists?, #with_role, #with_search_path

Methods included from ConnectionPersistence

#persist_connection, #persisted_connection

Methods inherited from Db

#operation_values, #relation_type_values, #table_tracker_columns

Constructor Details

This class inherits a constructor from Rake::DataTask::Postgres

Class Method Details

.set_up_tracking(options) ⇒ Object 



13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
 
# File 'lib/data_task/adapters/greenplum.rb', line 13

def self.set_up_tracking options
  tear_down_tracking options
  super

  execute "alter table #{TABLE_TRACKER_NAME} rename to #{TABLE_TRACKER_HELPER_NAME}"

  # Greenplum tracks CREATE and TRUNCATE operations in its pg_stat_operations system view.
  # Join this view with the tracking table so that we can track CREATE and TRUNCATE from within
  # the database instead of from application code.

  execute <<-EOSQL
    create view fixed_pg_stat_operations as
    -- GP's pg_stat_operations enum values like 'TABLE' are inconsistent so fix them here
    select
      pso.classname, 
      pso.objname,
      pso.objid,
      pso.schemaname,
      pso.usestatus,
      pso.usename,
      pso.actionname,
      case 
        when pso.actionname = 'TRUNCATE' then '#{relation_type_values[:table]}'
        when pso.subtype = 'TABLE' then '#{relation_type_values[:table]}'
        else pso.subtype
      end as subtype,
      pso.statime
    from pg_stat_operations pso
  EOSQL

  execute <<-EOSQL
    create view #{TABLE_TRACKER_NAME} as 
    select
      relation_name,
      relation_type,
      operation,
      time
    from (

      select
        a.*,
        rank() over (partition by relation_name, relation_type order by time desc)
      from (

        -- select all CREATE and TRUNCATE operations tracked by Greenplum
        select
          pso.objname as relation_name,
          pso.subtype as relation_type,
          pso.actionname as operation,
          pso.statime as time
        from fixed_pg_stat_operations pso
        where pso.actionname not in ('ANALYZE', 'VACUUM')

        union all

        -- select all operations tracked by Greenplum (PostgreSQL) table rules 
        select
          ttb.relation_name,
          ttb.relation_type,
          ttb.operation,
          ttb.time
        from 
          #{TABLE_TRACKER_HELPER_NAME} ttb
          -- return only operations for tables that exist in system tables
          join fixed_pg_stat_operations pso on (
            ttb.relation_name = pso.objname and
            ttb.relation_type = pso.subtype and
            pso.actionname = 'CREATE'
          )

        ) a
      ) b 
    -- take only the latest operation per table
    where rank = 1
  EOSQL

  # make sure we do deletes and inserts on the helper table, not the view
  execute <<-EOSQL
    create rule delete_operation_record as on delete to #{TABLE_TRACKER_NAME} 
      do instead
      delete from #{TABLE_TRACKER_HELPER_NAME} 
      where
        relation_name = OLD.relation_name and
        relation_type = OLD.relation_type and
        operation = OLD.operation
    ;

    create rule insert_operation_record as on insert to #{TABLE_TRACKER_NAME} 
      do instead
      insert into #{TABLE_TRACKER_HELPER_NAME} values (
        NEW.relation_name,
        NEW.relation_type,
        NEW.operation,
        NEW.time
      )
    ;
  EOSQL
end

.tear_down_tracking(options) ⇒ Object 



112
113
114
115
116
 
# File 'lib/data_task/adapters/greenplum.rb', line 112

def self.tear_down_tracking options
  drop_view "fixed_pg_stat_operations"
  drop_view TABLE_TRACKER_NAME
  drop_table TABLE_TRACKER_HELPER_NAME
end

Instance Method Details

#drop_table(table_name) ⇒ Object 



122
123
124
125
126
127
 
# File 'lib/data_task/adapters/greenplum.rb', line 122

def drop_table table_name
  execute "drop table if exists #{table_name} cascade"
  return if table_name.casecmp(TABLE_TRACKER_HELPER_NAME) == 0 || 
    table_name.casecmp(TABLE_TRACKER_NAME) == 0
  track_drop table_name
end

#track_drop(table_name) ⇒ Object 



129
130
131
132
133
134
135
136
 
# File 'lib/data_task/adapters/greenplum.rb', line 129

def track_drop table_name
  execute <<-EOSQL
    delete from #{TABLE_TRACKER_HELPER_NAME} 
    where 
      relation_name = '#{table_name}' and 
      relation_type = '#{relation_type_values[:table]}'
  EOSQL
end

#tracking_tables? ⇒ Boolean

Returns:

  • (Boolean) 


118
119
120
 
# File 'lib/data_task/adapters/greenplum.rb', line 118

def tracking_tables?
  view_exists?(TABLE_TRACKER_NAME)
end