Module: ArJdbc::PostgreSQL

Included in:
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
Defined in:
lib/arjdbc/postgresql/adapter.rb,
lib/arjdbc/postgresql/column.rb,
lib/arjdbc/postgresql/oid_types.rb

Overview

Strives to provide Rails built-in PostgreSQL adapter (API) compatibility.

Constant Summary collapse

ADAPTER_NAME = 
'PostgreSQL'.freeze
NATIVE_DATABASE_TYPES = 
{
  primary_key:  'bigserial primary key',
  bigint:       { name: 'bigint' },
  binary:       { name: 'bytea' },
  bit:          { name: 'bit' },
  bit_varying:  { name: 'bit varying' },
  boolean:      { name: 'boolean' },
  box:          { name: 'box' },
  char:         { name: 'char' },
  cidr:         { name: 'cidr' },
  circle:       { name: 'circle' },
  citext:       { name: 'citext' },
  date:         { name: 'date' },
  daterange:    { name: 'daterange' },
  datetime:     { name: 'timestamp' },
  decimal:      { name: 'decimal' }, # :limit => 1000
  float:        { name: 'float' },
  hstore:       { name: 'hstore' },
  inet:         { name: 'inet' },
  int4range:    { name: 'int4range' },
  int8range:    { name: 'int8range' },
  integer:      { name: 'integer' },
  interval:     { name: 'interval' }, # This doesn't get added to AR's postgres adapter until 5.1 but it fixes broken tests in 5.0 ...
  json:         { name: 'json' },
  jsonb:        { name: 'jsonb' },
  line:         { name: 'line' },
  lseg:         { name: 'lseg' },
  ltree:        { name: 'ltree' },
  macaddr:      { name: 'macaddr' },
  money:        { name: 'money' },
  numeric:      { name: 'numeric' },
  numrange:     { name: 'numrange' },
  oid:          { name: 'oid' },
  path:         { name: 'path' },
  point:        { name: 'point' },
  polygon:      { name: 'polygon' },
  string:       { name: 'character varying' },
  text:         { name: 'text' },
  time:         { name: 'time' },
  timestamp:    { name: 'timestamp' },
  tsrange:      { name: 'tsrange' },
  tstzrange:    { name: 'tstzrange' },
  tsvector:     { name: 'tsvector' },
  uuid:         { name: 'uuid' },
  xml:          { name: 'xml' }
}

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.jdbc_connection_class ⇒ Object

See Also:



38
39
40
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 38

def self.jdbc_connection_class
  ::ActiveRecord::ConnectionAdapters::PostgreSQLJdbcConnection
end

Instance Method Details

#adapter_name ⇒ Object 



47
48
49
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 47

def adapter_name
  ADAPTER_NAME
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.



406
407
408
409
410
411
412
413
414
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 406

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

#all_schemas ⇒ Object 



384
385
386
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 384

def all_schemas
  select('SELECT nspname FROM pg_namespace').map { |row| row["nspname"] }
end

#clear_cache! ⇒ Object

We need to make sure to deallocate all the prepared statements since apparently calling close on the statement object doesn't always free the server resources and calling 'DISCARD ALL' fails if we are inside a transaction



366
367
368
369
370
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 366

def clear_cache!
  super
  # Make sure all query plans are *really* gone
  @connection.execute 'DEALLOCATE ALL' if active?
end

#client_min_messages ⇒ Object

Returns the current client message level.



389
390
391
392
393
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 389

def client_min_messages
  return nil if redshift? # not supported on Redshift
  # Need to use #execute so we don't try to access the type map before it is initialized
  execute('SHOW client_min_messages', 'SCHEMA').values.first.first
end

#client_min_messages=(level) ⇒ Object

Set the client message level.



396
397
398
399
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 396

def client_min_messages=(level)
  # Not supported on Redshift
  redshift? ? nil : super
end

#configure_connection ⇒ Object

Configures the encoding, verbosity, schema search path, and time zone of the connection. This is called on connection.connect and should not be called manually.



87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 87

def configure_connection
  #if encoding = config[:encoding]
    # The client_encoding setting is set by the driver and should not be altered.
    # If the driver detects a change it will abort the connection.
    # see http://jdbc.postgresql.org/documentation/91/connect.html
    # self.set_client_encoding(encoding)
  #end
  self.client_min_messages = config[:min_messages] || 'warning'
  self.schema_search_path = config[:schema_search_path] || config[:schema_order]

  # Use standard-conforming strings if available so we don't have to do the E'...' dance.
  set_standard_conforming_strings

  # If using Active Record's time zone support configure the connection to return
  # TIMESTAMP WITH ZONE types in UTC.
  # (SET TIME ZONE does not use an equals sign like other SET variables)
  if ActiveRecord::Base.default_timezone == :utc
    execute("SET time zone 'UTC'", 'SCHEMA')
  elsif tz = local_tz
    execute("SET time zone '#{tz}'", 'SCHEMA')
  end unless redshift?

  # SET statements from :variables config hash
  # http://www.postgresql.org/docs/8.3/static/sql-set.html
  (config[:variables] || {}).map do |k, v|
    if v == ':default' || v == :default
      # Sets the value to the global or compile default
      execute("SET SESSION #{k} TO DEFAULT", 'SCHEMA')
    elsif ! v.nil?
      execute("SET SESSION #{k} TO #{quote(v)}", 'SCHEMA')
    end
  end
end

#default_index_type?(index) ⇒ Boolean

From AR 5.1 postgres_adapter.rb

Returns:

  • (Boolean) 


265
266
267
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 265

def default_index_type?(index) # :nodoc:
  index.using == :btree || super
end

#disable_extension(name) ⇒ Object 



273
274
275
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 273

def disable_extension(name)
  execute("DROP EXTENSION IF EXISTS \"#{name}\" CASCADE")
end

#enable_extension(name) ⇒ Object 



269
270
271
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 269

def enable_extension(name)
  execute("CREATE EXTENSION IF NOT EXISTS \"#{name}\"")
end

#escape_bytea(string) ⇒ Object

Note:

quote_string implemented as native



418
419
420
421
422
423
424
425
426
427
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 418

def escape_bytea(string)
  return unless string
  if supports_hex_escaped_bytea?
    "\\x#{string.unpack("H*")[0]}"
  else
    result = ''
    string.each_byte { |c| result << sprintf('\\\\%03o', c) }
    result
  end
end

#exec_insert(sql, name, binds, pk = nil, sequence_name = nil) ⇒ Object 



327
328
329
330
331
332
333
334
335
336
337
338
339
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 327

def exec_insert(sql, name, binds, pk = nil, sequence_name = nil)
  val = super
  if !use_insert_returning? && pk
    unless sequence_name
      table_ref = extract_table_ref_from_insert_sql(sql)
      sequence_name = default_sequence_name(table_ref, pk)
      return val unless sequence_name
    end
    last_insert_id_result(sequence_name)
  else
    val
  end
end

#explain(arel, binds = []) ⇒ Object 



341
342
343
344
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 341

def explain(arel, binds = [])
  sql, binds = to_sql_and_binds(arel, binds)
  ActiveRecord::ConnectionAdapters::PostgreSQL::ExplainPrettyPrinter.new.pp(exec_query("EXPLAIN #{sql}", 'EXPLAIN', binds))
end

#extension_enabled?(name) ⇒ Boolean

Returns:

  • (Boolean) 


277
278
279
280
281
282
283
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 277

def extension_enabled?(name)
  if supports_extensions?
    rows = select_rows("SELECT EXISTS(SELECT * FROM pg_available_extensions WHERE name = '#{name}' AND installed_version IS NOT NULL)", 'SCHEMA')
    available = rows.first.first # true/false or 't'/'f'
    available == true || available == 't'
  end
end

#extensions ⇒ Object 



285
286
287
288
289
290
291
292
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 285

def extensions
  if supports_extensions?
    rows = select_rows "SELECT extname from pg_extension", "SCHEMA"
    rows.map { |row| row.first }
  else
    []
  end
end

#get_advisory_lock(lock_id) ⇒ Object

Came from postgres_adapter



305
306
307
308
309
310
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 305

def get_advisory_lock(lock_id) # :nodoc:
  unless lock_id.is_a?(Integer) && lock_id.bit_length <= 63
    raise(ArgumentError, "Postgres requires advisory lock ids to be a signed 64 bit integer")
  end
  select_value("SELECT pg_try_advisory_lock(#{lock_id});")
end

#index_algorithms ⇒ Object 



294
295
296
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 294

def index_algorithms
  { :concurrently => 'CONCURRENTLY' }
end

#indexes(table_name) ⇒ Object

Returns an array of indexes for the given table.



489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 489

def indexes(table_name)

  # FIXME: AR version => table = Utils.extract_schema_qualified_name(table_name.to_s)
  schema, table = extract_schema_and_table(table_name.to_s)

  result = query(<<-SQL, 'SCHEMA')
        SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid,
                        pg_catalog.obj_description(i.oid, 'pg_class') AS comment
        FROM pg_class t
        INNER JOIN pg_index d ON t.oid = d.indrelid
        INNER JOIN pg_class i ON d.indexrelid = i.oid
        LEFT JOIN pg_namespace n ON n.oid = i.relnamespace
        WHERE i.relkind = 'i'
          AND d.indisprimary = 'f'
          AND t.relname = '#{table}'
          AND n.nspname = #{schema ? "'#{schema}'" : 'ANY (current_schemas(false))'}
        ORDER BY i.relname
  SQL

  result.map do |row|
    index_name = row[0]
    # FIXME: These values [1,2] are returned in a different format than AR expects, maybe we could update it on the Java side to be more accurate
    unique = row[1].is_a?(String) ? row[1] == 't' : row[1] # JDBC gets us a boolean
    indkey = row[2].is_a?(Java::OrgPostgresqlUtil::PGobject) ? row[2].value : row[2]
    indkey = indkey.split(" ").map(&:to_i)
    inddef = row[3]
    oid = row[4]
    comment = row[5]

    using, expressions, where = inddef.scan(/ USING (\w+?) \((.+?)\)(?: WHERE (.+))?\z/m).flatten

    orders = {}
    opclasses = {}

    if indkey.include?(0)
      columns = expressions
    else
      columns = Hash[query(<<-SQL.strip_heredoc, "SCHEMA")].values_at(*indkey).compact
            SELECT a.attnum, a.attname
            FROM pg_attribute a
            WHERE a.attrelid = #{oid}
            AND a.attnum IN (#{indkey.join(",")})
      SQL

      # add info on sort order (only desc order is explicitly specified, asc is the default)
      # and non-default opclasses
      expressions.scan(/(?<column>\w+)\s?(?<opclass>\w+_ops)?\s?(?<desc>DESC)?\s?(?<nulls>NULLS (?:FIRST|LAST))?/).each do |column, opclass, desc, nulls|
        opclasses[column] = opclass.to_sym if opclass
        if nulls
          orders[column] = [desc, nulls].compact.join(' ')
        elsif desc
          orders[column] = :desc
        end
      end
    end

    IndexDefinition.new(
        table_name,
        index_name,
        unique,
        columns,
        orders: orders,
        opclasses: opclasses,
        where: where,
        using: using.to_sym,
        comment: comment.presence
    )
  end
end

#jdbc_column_class ⇒ Object

See Also:



43
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 43

def jdbc_column_class; ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn end

#last_insert_id_result(sequence_name) ⇒ Object 



380
381
382
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 380

def last_insert_id_result(sequence_name)
  exec_query("SELECT currval('#{sequence_name}')", 'SQL')
end

#max_identifier_length ⇒ Object Also known as: table_alias_length, index_name_length

Returns the max identifier length supported by PostgreSQL



321
322
323
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 321

def max_identifier_length
  @max_identifier_length ||= select_one('SHOW max_identifier_length', 'SCHEMA'.freeze)['max_identifier_length'].to_i
end

#native_database_types ⇒ Object 



171
172
173
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 171

def native_database_types
  NATIVE_DATABASE_TYPES
end

#postgresql_version ⇒ Object 



51
52
53
54
55
56
57
58
59
60
61
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 51

def postgresql_version
  @postgresql_version ||=
    begin
      version = @connection.database_product
      if version =~ /PostgreSQL (\d+)\.(\d+)\.(\d+)/
        ($1.to_i * 10000) + ($2.to_i * 100) + $3.to_i
      else
        0
      end
    end
end

#quote_table_name(name) ⇒ Object 



430
431
432
433
434
435
436
437
438
439
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 430

def quote_table_name(name)
  schema, name_part = extract_pg_identifier_from_name(name.to_s)

  unless name_part
    quote_column_name(schema)
  else
    table_name, name_part = extract_pg_identifier_from_name(name_part)
    "#{quote_column_name(schema)}.#{quote_column_name(table_name)}"
  end
end

#release_advisory_lock(lock_id) ⇒ Object

Came from postgres_adapter



313
314
315
316
317
318
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 313

def release_advisory_lock(lock_id) # :nodoc:
  unless lock_id.is_a?(Integer) && lock_id.bit_length <= 63
    raise(ArgumentError, "Postgres requires advisory lock ids to be a signed 64 bit integer")
  end
  select_value("SELECT pg_advisory_unlock(#{lock_id})")
end

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

Need to clear the cache even though the AR adapter doesn't for some reason



445
446
447
448
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 445

def remove_column(table_name, column_name, type = nil, options = {})
  super
  clear_cache!
end

#reset! ⇒ Object 



372
373
374
375
376
377
378
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 372

def reset!
  clear_cache!
  reset_transaction
  @connection.rollback # Have to deal with rollbacks differently than the AR adapter
  @connection.execute 'DISCARD ALL'
  @connection.configure_connection
end

#session_auth=(user) ⇒ Object

Set the authorized user for this session.



299
300
301
302
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 299

def session_auth=(user)
  clear_cache!
  execute "SET SESSION AUTHORIZATION #{user}"
end

#set_client_encoding(encoding) ⇒ Object 



80
81
82
83
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 80

def set_client_encoding(encoding)
  ActiveRecord::Base.logger.warn "client_encoding is set by the driver and should not be altered, ('#{encoding}' ignored)"
  ActiveRecord::Base.logger.debug "Set the 'allowEncodingChanges' driver property (e.g. using config[:properties]) if you need to override the client encoding when doing a copy."
end

#set_standard_conforming_strings ⇒ Object

Enable standard-conforming strings if available.



180
181
182
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 180

def set_standard_conforming_strings
  self.standard_conforming_strings=(true)
end

#standard_conforming_strings=(enable) ⇒ Object

Enable standard-conforming strings if available.



185
186
187
188
189
190
191
192
193
194
195
196
197
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 185

def standard_conforming_strings=(enable)
  client_min_messages = self.client_min_messages
  begin
    self.client_min_messages = 'panic'
    value = enable ? "on" : "off"
    execute("SET standard_conforming_strings = #{value}", 'SCHEMA')
    @standard_conforming_strings = ( value == "on" )
  rescue
    @standard_conforming_strings = :unsupported
  ensure
    self.client_min_messages = client_min_messages
  end
end

#standard_conforming_strings? ⇒ Boolean

Returns:

  • (Boolean) 


199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 199

def standard_conforming_strings?
  if @standard_conforming_strings.nil?
    client_min_messages = self.client_min_messages
    begin
      self.client_min_messages = 'panic'
      value = select_one('SHOW standard_conforming_strings', 'SCHEMA')['standard_conforming_strings']
      @standard_conforming_strings = ( value == "on" )
    rescue
      @standard_conforming_strings = :unsupported
    ensure
      self.client_min_messages = client_min_messages
    end
  end
  @standard_conforming_strings == true # return false if :unsupported
end

#supports_ddl_transactions? ⇒ Boolean

Returns:

  • (Boolean) 


215
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 215

def supports_ddl_transactions?; true end

#supports_explain? ⇒ Boolean

Returns:

  • (Boolean) 


217
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 217

def supports_explain?; true end

#supports_expression_index? ⇒ Boolean

Returns:

  • (Boolean) 


219
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 219

def supports_expression_index?; true end

#supports_extensions? ⇒ Boolean

Returns:

  • (Boolean) 


260
261
262
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 260

def supports_extensions?
  postgresql_version >= 90200
end

#supports_foreign_keys? ⇒ Boolean

Returns:

  • (Boolean) 


221
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 221

def supports_foreign_keys?; true end

#supports_foreign_tables? ⇒ Boolean

we don't really support this yet, its a reminder :)

Returns:

  • (Boolean) 


239
240
241
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 239

def supports_foreign_tables? # we don't really support this yet, its a reminder :)
  postgresql_version >= 90300
end

#supports_hex_escaped_bytea? ⇒ Boolean

Returns:

  • (Boolean) 


243
244
245
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 243

def supports_hex_escaped_bytea?
  postgresql_version >= 90000
end

#supports_index_sort_order? ⇒ Boolean

Returns:

  • (Boolean) 


223
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 223

def supports_index_sort_order?; true end

#supports_insert_with_returning? ⇒ Boolean

Returns:

  • (Boolean) 


247
248
249
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 247

def supports_insert_with_returning?
  postgresql_version >= 80200
end

#supports_partial_index? ⇒ Boolean

Returns:

  • (Boolean) 


225
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 225

def supports_partial_index?; true end

#supports_pgcrypto_uuid? ⇒ Boolean

Returns:

  • (Boolean) 


251
252
253
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 251

def supports_pgcrypto_uuid?
  postgresql_version >= 90400
end

#supports_ranges? ⇒ Boolean

Range data-types weren't introduced until PostgreSQL 9.2.

Returns:

  • (Boolean) 


256
257
258
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 256

def supports_ranges?
  postgresql_version >= 90200
end

#supports_savepoints? ⇒ Boolean

Returns:

  • (Boolean) 


227
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 227

def supports_savepoints?; true end

#supports_standard_conforming_strings? ⇒ Boolean

Does PostgreSQL support standard conforming strings?

Returns:

  • (Boolean) 


234
235
236
237
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 234

def supports_standard_conforming_strings?
  standard_conforming_strings?
  @standard_conforming_strings != :unsupported
end

#supports_transaction_isolation? ⇒ Boolean

Returns:

  • (Boolean) 


229
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 229

def supports_transaction_isolation?; true end

#supports_views? ⇒ Boolean

Returns:

  • (Boolean) 


231
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 231

def supports_views?; true end

#truncate(table_name, name = nil) ⇒ Object 



484
485
486
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 484

def truncate(table_name, name = nil)
  execute "TRUNCATE TABLE #{quote_table_name(table_name)}", name
end

#use_insert_returning? ⇒ Boolean

Returns:

  • (Boolean) 


73
74
75
76
77
78
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 73

def use_insert_returning?
  if @use_insert_returning.nil?
    @use_insert_returning = supports_insert_with_returning?
  end
  @use_insert_returning
end

#valid_type?(type) ⇒ Boolean

Returns:

  • (Boolean) 


175
176
177
 
# File 'lib/arjdbc/postgresql/adapter.rb', line 175

def valid_type?(type)
  !native_database_types[type].nil?
end