Class: ActiveRecord::ConnectionAdapters::PostgreSQLAdapter

Inherits:
AbstractAdapter
  • Object
show all
Defined in:
lib/active_record/connection_adapters/postgresql_adapter.rb

Overview

The PostgreSQL adapter works both with the C-based (www.postgresql.jp/interfaces/ruby/) and the Ruby-base (available both as gem and from rubyforge.org/frs/?group_id=234&release_id=1145) drivers.

Options:

  • :host – Defaults to localhost

  • :port – Defaults to 5432

  • :username – Defaults to nothing

  • :password – Defaults to nothing

  • :database – The name of the database. No default, must be provided.

  • :schema_search_path – An optional schema search path for the connection given as a string of comma-separated schema names. This is backward-compatible with the :schema_order option.

  • :encoding – An optional client encoding that is using in a SET client_encoding TO <encoding> call on connection.

  • :min_messages – An optional client min messages that is using in a SET client_min_messages TO <min_messages> call on connection.

  • :allow_concurrency – If true, use async query methods so Ruby threads don’t deadlock; otherwise, use blocking query methods.

Instance Method Summary collapse

Methods inherited from AbstractAdapter

#prefetch_primary_key?, #raw_connection, #requires_reloading?, #reset_runtime, #supports_count_distinct?, #verify!

Methods included from Quoting

#quote_string, #quoted_false, #quoted_true

Methods included from SchemaStatements

#add_column_options!, #add_index, #create_table, #drop_table, #dump_schema_information, #index_name, #initialize_schema_information, #remove_column, #structure_dump, #table_alias_for

Constructor Details

#initialize(connection, logger, config = {}) ⇒ PostgreSQLAdapter

Returns a new instance of PostgreSQLAdapter.



55
56
57
58
59
60
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 55

def initialize(connection, logger, config = {})
  super(connection, logger)
  @config = config
  @async = config[:allow_concurrency]
  configure_connection
end

Instance Method Details

#active? ⇒ Boolean

Is this connection alive and ready for queries?

Returns:

  • (Boolean) 


63
64
65
66
67
68
69
70
71
72
73
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 63

def active?
  if @connection.respond_to?(:status)
    @connection.status == PGconn::CONNECTION_OK
  else
    @connection.query 'SELECT 1'
    true
  end
# postgres-pr raises a NoMethodError when querying if no conn is available
rescue PGError, NoMethodError
  false
end

#adapter_name ⇒ Object 



51
52
53
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 51

def adapter_name
  'PostgreSQL'
end

#add_column(table_name, column_name, type, options = {}) ⇒ Object 



316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 316

def add_column(table_name, column_name, type, options = {})
  default = options[:default]
  notnull = options[:null] == false

  # Add the column.
  execute("ALTER TABLE #{table_name} ADD COLUMN #{column_name} #{type_to_sql(type, options[:limit])}")

  # Set optional default. If not null, update nulls to the new default.
  if options_include_default?(options)
    change_column_default(table_name, column_name, default)
    if notnull
      execute("UPDATE #{table_name} SET #{column_name}=#{quote(default, options[:column])} WHERE #{column_name} IS NULL")
    end
  end

  if notnull
    execute("ALTER TABLE #{table_name} ALTER #{column_name} SET NOT NULL")
  end
end

#add_order_by_for_association_limiting!(sql, options) ⇒ Object

ORDER BY clause for the passed order option.

PostgreSQL does not allow arbitrary ordering when using DISTINCT ON, so we work around this by wrapping the sql as a sub-select and ordering in that query.



403
404
405
406
407
408
409
410
411
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 403

def add_order_by_for_association_limiting!(sql, options)
  return sql if options[:order].blank?
  
  order = options[:order].split(',').collect { |s| s.strip }.reject(&:blank?)
  order.map! { |s| 'DESC' if s =~ /\bdesc$/i }
  order = order.zip((0...order.size).to_a).map { |s,i| "id_list.alias_#{i} #{s}" }.join(', ')
  
  sql.replace "SELECT * FROM (#{sql}) AS id_list ORDER BY #{order}"
end

#begin_db_transaction ⇒ Object

:nodoc:



165
166
167
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 165

def begin_db_transaction #:nodoc:
  execute "BEGIN"
end

#change_column(table_name, column_name, type, options = {}) ⇒ Object

:nodoc:



336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 336

def change_column(table_name, column_name, type, options = {}) #:nodoc:
  begin
    execute "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
  rescue ActiveRecord::StatementInvalid
    # This is PG7, so we use a more arcane way of doing it.
    begin_db_transaction
    add_column(table_name, "#{column_name}_ar_tmp", type, options)
    execute "UPDATE #{table_name} SET #{column_name}_ar_tmp = CAST(#{column_name} AS #{type_to_sql(type, options[:limit], options[:precision], options[:scale])})"
    remove_column(table_name, column_name)
    rename_column(table_name, "#{column_name}_ar_tmp", column_name)
    commit_db_transaction
  end

  if options_include_default?(options)
    change_column_default(table_name, column_name, options[:default])
  end
end

#change_column_default(table_name, column_name, default) ⇒ Object

:nodoc:



354
355
356
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 354

def change_column_default(table_name, column_name, default) #:nodoc:
  execute "ALTER TABLE #{table_name} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote(default)}"
end

#columns(table_name, name = nil) ⇒ Object

:nodoc:



222
223
224
225
226
227
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 222

def columns(table_name, name = nil) #:nodoc:
  column_definitions(table_name).collect do |name, type, default, notnull, typmod|
    # typmod now unused as limit, precision, scale all handled by superclass
    Column.new(name, default_value(default), translate_field_type(type), notnull == "f")
  end
end

#commit_db_transaction ⇒ Object

:nodoc:



169
170
171
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 169

def commit_db_transaction #:nodoc:
  execute "COMMIT"
end

#default_sequence_name(table_name, pk = nil) ⇒ Object 



243
244
245
246
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 243

def default_sequence_name(table_name, pk = nil)
  default_pk, default_seq = pk_and_sequence_for(table_name)
  default_seq || "#{table_name}_#{pk || default_pk || 'id'}_seq"
end

#disconnect! ⇒ Object 



84
85
86
87
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 84

def disconnect!
  # Both postgres and postgres-pr respond to :close
  @connection.close rescue nil
end

#distinct(columns, order_by) ⇒ Object

SELECT DISTINCT clause for a given set of columns and a given ORDER BY clause.

PostgreSQL requires the ORDER BY columns in the select list for distinct queries, and requires that the ORDER BY include the distinct column.

distinct("posts.id", "posts.created_at desc")



384
385
386
387
388
389
390
391
392
393
394
395
396
397
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 384

def distinct(columns, order_by)
  return "DISTINCT #{columns}" if order_by.blank?

  # construct a clean list of column names from the ORDER BY clause, removing
  # any asc/desc modifiers
  order_columns = order_by.split(',').collect { |s| s.split.first }
  order_columns.delete_if &:blank?
  order_columns = order_columns.zip((0...order_columns.size).to_a).map { |s,i| "#{s} AS alias_#{i}" }

  # return a DISTINCT ON() clause that's distinct on the columns we want but includes
  # all the required columns for the ORDER BY to work properly
  sql = "DISTINCT ON (#{columns}) #{columns}, "
  sql << order_columns * ', '
end

#execute(sql, name = nil) ⇒ Object

:nodoc:



151
152
153
154
155
156
157
158
159
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 151

def execute(sql, name = nil) #:nodoc:
  log(sql, name) do
    if @async
      @connection.async_exec(sql)
    else
      @connection.exec(sql)
    end
  end
end

#indexes(table_name, name = nil) ⇒ Object

:nodoc:



189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 189

def indexes(table_name, name = nil) #:nodoc:
  result = query(<<-SQL, name)
    SELECT i.relname, d.indisunique, a.attname
      FROM pg_class t, pg_class i, pg_index d, pg_attribute a
     WHERE i.relkind = 'i'
       AND d.indexrelid = i.oid
       AND d.indisprimary = 'f'
       AND t.oid = d.indrelid
       AND t.relname = '#{table_name}'
       AND a.attrelid = t.oid
       AND ( d.indkey[0]=a.attnum OR d.indkey[1]=a.attnum
          OR d.indkey[2]=a.attnum OR d.indkey[3]=a.attnum
          OR d.indkey[4]=a.attnum OR d.indkey[5]=a.attnum
          OR d.indkey[6]=a.attnum OR d.indkey[7]=a.attnum
          OR d.indkey[8]=a.attnum OR d.indkey[9]=a.attnum )
    ORDER BY i.relname
  SQL

  current_index = nil
  indexes = []

  result.each do |row|
    if current_index != row[0]
      indexes << IndexDefinition.new(table_name, row[0], row[1] == "t", [])
      current_index = row[0]
    end

    indexes.last.columns << row[2]
  end

  indexes
end

#insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) ⇒ Object

DATABASE STATEMENTS ======================================



135
136
137
138
139
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 135

def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) #:nodoc:
  execute(sql, name)
  table = sql.split(" ", 4)[2]
  id_value || last_insert_id(table, sequence_name || default_sequence_name(table, pk))
end

#native_database_types ⇒ Object 



89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 89

def native_database_types
  {
    :primary_key => "serial primary key",
    :string      => { :name => "character varying", :limit => 255 },
    :text        => { :name => "text" },
    :integer     => { :name => "integer" },
    :float       => { :name => "float" },
    :decimal     => { :name => "decimal" },
    :datetime    => { :name => "timestamp" },
    :timestamp   => { :name => "timestamp" },
    :time        => { :name => "time" },
    :date        => { :name => "date" },
    :binary      => { :name => "bytea" },
    :boolean     => { :name => "boolean" }
  }
end

#pk_and_sequence_for(table) ⇒ Object

Find a table’s primary key and sequence.



267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 267

def pk_and_sequence_for(table)
  # First try looking for a sequence with a dependency on the
  # given table's primary key.
  result = query(<<-end_sql, 'PK and serial sequence')[0]
    SELECT attr.attname, name.nspname, seq.relname
    FROM pg_class      seq,
         pg_attribute  attr,
         pg_depend     dep,
         pg_namespace  name,
         pg_constraint cons
    WHERE seq.oid           = dep.objid
      AND seq.relnamespace  = name.oid
      AND seq.relkind       = 'S'
      AND attr.attrelid     = dep.refobjid
      AND attr.attnum       = dep.refobjsubid
      AND attr.attrelid     = cons.conrelid
      AND attr.attnum       = cons.conkey[1]
      AND cons.contype      = 'p'
      AND dep.refobjid      = '#{table}'::regclass
  end_sql

  if result.nil? or result.empty?
    # If that fails, try parsing the primary key's default value.
    # Support the 7.x and 8.0 nextval('foo'::text) as well as
    # the 8.1+ nextval('foo'::regclass).
    # TODO: assumes sequence is in same schema as table.
    result = query(<<-end_sql, 'PK and custom sequence')[0]
      SELECT attr.attname, name.nspname, split_part(def.adsrc, '''', 2)
      FROM pg_class       t
      JOIN pg_namespace   name ON (t.relnamespace = name.oid)
      JOIN pg_attribute   attr ON (t.oid = attrelid)
      JOIN pg_attrdef     def  ON (adrelid = attrelid AND adnum = attnum)
      JOIN pg_constraint  cons ON (conrelid = adrelid AND adnum = conkey[1])
      WHERE t.oid = '#{table}'::regclass
        AND cons.contype = 'p'
        AND def.adsrc ~* 'nextval'
    end_sql
  end
  # check for existence of . in sequence name as in public.foo_sequence.  if it does not exist, return unqualified sequence
  # We cannot qualify unqualified sequences, as rails doesn't qualify any table access, using the search path
  [result.first, result.last]
rescue
  nil
end

#query(sql, name = nil) ⇒ Object

:nodoc:



141
142
143
144
145
146
147
148
149
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 141

def query(sql, name = nil) #:nodoc:
  log(sql, name) do
    if @async
      @connection.async_query(sql)
    else
      @connection.query(sql)
    end
  end
end

#quote(value, column = nil) ⇒ Object

QUOTING ==================================================



116
117
118
119
120
121
122
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 116

def quote(value, column = nil)
  if value.kind_of?(String) && column && column.type == :binary
    "'#{escape_bytea(value)}'"
  else
    super
  end
end

#quote_column_name(name) ⇒ Object 



124
125
126
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 124

def quote_column_name(name)
  %("#{name}")
end

#quoted_date(value) ⇒ Object 



128
129
130
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 128

def quoted_date(value)
  value.strftime("%Y-%m-%d %H:%M:%S.#{sprintf("%06d", value.usec)}")
end

#reconnect! ⇒ Object

Close then reopen the connection.



76
77
78
79
80
81
82
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 76

def reconnect!
  # TODO: postgres-pr doesn't have PGconn#reset.
  if @connection.respond_to?(:reset)
    @connection.reset
    configure_connection
  end
end

#remove_index(table_name, options) ⇒ Object

:nodoc:



362
363
364
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 362

def remove_index(table_name, options) #:nodoc:
  execute "DROP INDEX #{index_name(table_name, options)}"
end

#rename_column(table_name, column_name, new_column_name) ⇒ Object

:nodoc:



358
359
360
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 358

def rename_column(table_name, column_name, new_column_name) #:nodoc:
  execute "ALTER TABLE #{table_name} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}"
end

#rename_table(name, new_name) ⇒ Object 



312
313
314
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 312

def rename_table(name, new_name)
  execute "ALTER TABLE #{name} RENAME TO #{new_name}"
end

#reset_pk_sequence!(table, pk = nil, sequence = nil) ⇒ Object

Resets sequence to the max value of the table’s pk if present.



249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 249

def reset_pk_sequence!(table, pk = nil, sequence = nil)
  unless pk and sequence
    default_pk, default_sequence = pk_and_sequence_for(table)
    pk ||= default_pk
    sequence ||= default_sequence
  end
  if pk
    if sequence
      select_value <<-end_sql, 'Reset sequence'
        SELECT setval('#{sequence}', (SELECT COALESCE(MAX(#{pk})+(SELECT increment_by FROM #{sequence}), (SELECT min_value FROM #{sequence})) FROM #{table}), false)
      end_sql
    else
      @logger.warn "#{table} has primary key #{pk} with no default sequence" if @logger
    end
  end
end

#rollback_db_transaction ⇒ Object

:nodoc:



173
174
175
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 173

def rollback_db_transaction #:nodoc:
  execute "ROLLBACK"
end

#schema_search_path ⇒ Object

:nodoc:



239
240
241
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 239

def schema_search_path #:nodoc:
  @schema_search_path ||= query('SHOW search_path')[0][0]
end

#schema_search_path=(schema_csv) ⇒ Object

Set the schema search path to a string of comma-separated schema names. Names beginning with $ are quoted (e.g. $user => ‘$user’) See www.postgresql.org/docs/8.0/interactive/ddl-schemas.html



232
233
234
235
236
237
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 232

def schema_search_path=(schema_csv) #:nodoc:
  if schema_csv
    execute "SET search_path TO #{schema_csv}"
    @schema_search_path = nil
  end
end

#supports_migrations? ⇒ Boolean

Returns:

  • (Boolean) 


106
107
108
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 106

def supports_migrations?
  true
end

#table_alias_length ⇒ Object 



110
111
112
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 110

def table_alias_length
  63
end

#tables(name = nil) ⇒ Object

Return the list of all tables in the schema search path.



180
181
182
183
184
185
186
187
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 180

def tables(name = nil) #:nodoc:
  schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',')
  query(<<-SQL, name).map { |row| row[0] }
    SELECT tablename
      FROM pg_tables
     WHERE schemaname IN (#{schemas})
  SQL
end

#type_to_sql(type, limit = nil, precision = nil, scale = nil) ⇒ Object

:nodoc:



366
367
368
369
370
371
372
373
374
375
376
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 366

def type_to_sql(type, limit = nil, precision = nil, scale = nil) #:nodoc:
  return super unless type.to_s == 'integer'

  if limit.nil? || limit == 4
    'integer'
  elsif limit < 4
    'smallint'
  else
    'bigint'
  end
end

#update(sql, name = nil) ⇒ Object

:nodoc:



161
162
163
 
# File 'lib/active_record/connection_adapters/postgresql_adapter.rb', line 161

def update(sql, name = nil) #:nodoc:
  execute(sql, name).cmdtuples
end