Class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
In: vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
Parent: AbstractAdapter

The PostgreSQL adapter works both with the native C (ruby.scripting.ca/postgres/) and the pure Ruby (available both as gem and from rubyforge.org/frs/?group_id=234&release_id=1944) 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 used in a SET client_encoding TO <encoding> call on the connection.
  • :min_messages - An optional client min messages that is used in a SET client_min_messages TO <min_messages> call on the connection.
  • :allow_concurrency - If true, use async query methods so Ruby threads don‘t deadlock; otherwise, use blocking query methods.

Methods

Public Class methods

Initializes and connects a PostgreSQL adapter.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 181
181:       def initialize(connection, logger, connection_parameters, config)
182:         super(connection, logger)
183:         @connection_parameters, @config = connection_parameters, config
184: 
185:         connect
186:       end

Public Instance methods

Is this connection alive and ready for queries?

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 189
189:       def active?
190:         if @connection.respond_to?(:status)
191:           @connection.status == PGconn::CONNECTION_OK
192:         else
193:           # We're asking the driver, not ActiveRecord, so use @connection.query instead of #query
194:           @connection.query 'SELECT 1'
195:           true
196:         end
197:       # postgres-pr raises a NoMethodError when querying if no connection is available.
198:       rescue PGError, NoMethodError
199:         false
200:       end

Returns ‘PostgreSQL’ as adapter name for identification purposes.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 176
176:       def adapter_name
177:         'PostgreSQL'
178:       end

Adds a new column to the named table. See TableDefinition#column for details of the options you can use.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 700
700:       def add_column(table_name, column_name, type, options = {})
701:         default = options[:default]
702:         notnull = options[:null] == false
703: 
704:         # Add the column.
705:         execute("ALTER TABLE #{quote_table_name(table_name)} ADD COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}")
706: 
707:         change_column_default(table_name, column_name, default) if options_include_default?(options)
708:         change_column_null(table_name, column_name, false, default) if notnull
709:       end

Begins a transaction.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 472
472:       def begin_db_transaction
473:         execute "BEGIN"
474:       end

Changes the column of a table.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 712
712:       def change_column(table_name, column_name, type, options = {})
713:         quoted_table_name = quote_table_name(table_name)
714: 
715:         begin
716:           execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
717:         rescue ActiveRecord::StatementInvalid
718:           # This is PostgreSQL 7.x, so we have to use a more arcane way of doing it.
719:           begin
720:             begin_db_transaction
721:             tmp_column_name = "#{column_name}_ar_tmp"
722:             add_column(table_name, tmp_column_name, type, options)
723:             execute "UPDATE #{quoted_table_name} SET #{quote_column_name(tmp_column_name)} = CAST(#{quote_column_name(column_name)} AS #{type_to_sql(type, options[:limit], options[:precision], options[:scale])})"
724:             remove_column(table_name, column_name)
725:             rename_column(table_name, tmp_column_name, column_name)
726:             commit_db_transaction
727:           rescue
728:             rollback_db_transaction
729:           end
730:         end
731: 
732:         change_column_default(table_name, column_name, options[:default]) if options_include_default?(options)
733:         change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null)
734:       end

Changes the default value of a table column.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 737
737:       def change_column_default(table_name, column_name, default)
738:         execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote(default)}"
739:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 741
741:       def change_column_null(table_name, column_name, null, default = nil)
742:         unless null || default.nil?
743:           execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL")
744:         end
745:         execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL")
746:       end

Returns the current client message level.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 606
606:       def client_min_messages
607:         query('SHOW client_min_messages')[0][0]
608:       end

Set the client message level.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 611
611:       def client_min_messages=(level)
612:         execute("SET client_min_messages TO '#{level}'")
613:       end

Returns the list of all column definitions for a table.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 581
581:       def columns(table_name, name = nil)
582:         # Limit, precision, and scale are all handled by the superclass.
583:         column_definitions(table_name).collect do |name, type, default, notnull|
584:           PostgreSQLColumn.new(name, default, type, notnull == 'f')
585:         end
586:       end

Commits a transaction.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 477
477:       def commit_db_transaction
478:         execute "COMMIT"
479:       end

Create a new PostgreSQL database. Options include :owner, :template, :encoding, :tablespace, and :connection_limit (note that MySQL uses :charset while PostgreSQL uses :encoding).

Example:

  create_database config[:database], config
  create_database 'foo_development', :encoding => 'unicode'

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 500
500:       def create_database(name, options = {})
501:         options = options.reverse_merge(:encoding => "utf8")
502: 
503:         option_string = options.symbolize_keys.sum do |key, value|
504:           case key
505:           when :owner
506:             " OWNER = '#{value}'"
507:           when :template
508:             " TEMPLATE = #{value}"
509:           when :encoding
510:             " ENCODING = '#{value}'"
511:           when :tablespace
512:             " TABLESPACE = #{value}"
513:           when :connection_limit
514:             " CONNECTION LIMIT = #{value}"
515:           else
516:             ""
517:           end
518:         end
519: 
520:         execute "CREATE DATABASE #{name}#{option_string}"
521:       end

Close the connection.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 214
214:       def disconnect!
215:         @connection.close rescue nil
216:       end

Escapes binary strings for bytea input to the database.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 265
265:       def escape_bytea(value)
266:         if PGconn.respond_to?(:escape_bytea)
267:           self.class.instance_eval do
268:             define_method(:escape_bytea) do |value|
269:               PGconn.escape_bytea(value) if value
270:             end
271:           end
272:         else
273:           self.class.instance_eval do
274:             define_method(:escape_bytea) do |value|
275:               if value
276:                 result = ''
277:                 value.each_byte { |c| result << sprintf('\\\\%03o', c) }
278:                 result
279:               end
280:             end
281:           end
282:         end
283:         escape_bytea(value)
284:       end

Executes an SQL statement, returning a PGresult object on success or raising a PGError exception otherwise.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 456
456:       def execute(sql, name = nil)
457:         log(sql, name) do
458:           if @async
459:             @connection.async_exec(sql)
460:           else
461:             @connection.exec(sql)
462:           end
463:         end
464:       end

Returns the list of all indexes for a table.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 544
544:       def indexes(table_name, name = nil)
545:          schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',')
546:          result = query("SELECT distinct i.relname, d.indisunique, a.attname\nFROM pg_class t, pg_class i, pg_index d, pg_attribute a\nWHERE i.relkind = 'i'\nAND d.indexrelid = i.oid\nAND d.indisprimary = 'f'\nAND t.oid = d.indrelid\nAND t.relname = '\#{table_name}'\nAND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN (\#{schemas}) )\nAND a.attrelid = t.oid\nAND ( d.indkey[0]=a.attnum OR d.indkey[1]=a.attnum\nOR d.indkey[2]=a.attnum OR d.indkey[3]=a.attnum\nOR d.indkey[4]=a.attnum OR d.indkey[5]=a.attnum\nOR d.indkey[6]=a.attnum OR d.indkey[7]=a.attnum\nOR d.indkey[8]=a.attnum OR d.indkey[9]=a.attnum )\nORDER BY i.relname\n", name)
547: 
548:         current_index = nil
549:         indexes = []
550: 
551:         result.each do |row|
552:           if current_index != row[0]
553:             indexes << IndexDefinition.new(table_name, row[0], row[1] == "t", [])
554:             current_index = row[0]
555:           end
556: 
557:           indexes.last.columns << row[2]
558:         end
559: 
560:         indexes
561:       end

Executes an INSERT query and returns the new record‘s ID

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 415
415:       def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
416:         table = sql.split(" ", 4)[2].gsub('"', '')
417:         super || pk && last_insert_id(table, sequence_name || default_sequence_name(table, pk))
418:       end

Close then reopen the connection.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 203
203:       def reconnect!
204:         if @connection.respond_to?(:reset)
205:           @connection.reset
206:           configure_connection
207:         else
208:           disconnect!
209:           connect
210:         end
211:       end

Drops an index from a table.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 754
754:       def remove_index(table_name, options = {})
755:         execute "DROP INDEX #{index_name(table_name, options)}"
756:       end

Renames a column in a table.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 749
749:       def rename_column(table_name, column_name, new_column_name)
750:         execute "ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}"
751:       end

Renames a table.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 694
694:       def rename_table(name, new_name)
695:         execute "ALTER TABLE #{name} RENAME TO #{new_name}"
696:       end

Aborts a transaction.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 482
482:       def rollback_db_transaction
483:         execute "ROLLBACK"
484:       end

Returns the active schema search path.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 601
601:       def schema_search_path
602:         @schema_search_path ||= query('SHOW search_path')[0][0]
603:       end

Sets the schema search path to a string of comma-separated schema names. Names beginning with $ have to be quoted (e.g. $user => ’$user’). See: www.postgresql.org/docs/current/static/ddl-schemas.html

This should be not be called manually but set in database.yml.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 593
593:       def schema_search_path=(schema_csv)
594:         if schema_csv
595:           execute "SET search_path TO #{schema_csv}"
596:           @schema_search_path = schema_csv
597:         end
598:       end

Executes a SELECT query and returns an array of rows. Each row is an array of field values.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 410
410:       def select_rows(sql, name = nil)
411:         select_raw(sql, name).last
412:       end

Does PostgreSQL support migrations?

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 236
236:       def supports_migrations?
237:         true
238:       end

Does PostgreSQL support standard conforming strings?

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 241
241:       def supports_standard_conforming_strings?
242:         # Temporarily set the client message level above error to prevent unintentional
243:         # error messages in the logs when working on a PostgreSQL database server that
244:         # does not support standard conforming strings.
245:         client_min_messages_old = client_min_messages
246:         self.client_min_messages = 'panic'
247: 
248:         # postgres-pr does not raise an exception when client_min_messages is set higher
249:         # than error and "SHOW standard_conforming_strings" fails, but returns an empty
250:         # PGresult instead.
251:         has_support = query('SHOW standard_conforming_strings')[0][0] rescue false
252:         self.client_min_messages = client_min_messages_old
253:         has_support
254:       end

Returns the configured supported identifier length supported by PostgreSQL, or report the default of 63 on PostgreSQL 7.x.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 258
258:       def table_alias_length
259:         @table_alias_length ||= (postgresql_version >= 80000 ? query('SHOW max_identifier_length')[0][0].to_i : 63)
260:       end

Returns the list of all tables in the schema search path or a specified schema.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 533
533:       def tables(name = nil)
534:         schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',')
535:         query("SELECT tablename\nFROM pg_tables\nWHERE schemaname IN (\#{schemas})\n", name).map { |row| row[0] }
536:       end

Maps logical Rails types to PostgreSQL-specific data types.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 759
759:       def type_to_sql(type, limit = nil, precision = nil, scale = nil)
760:         return super unless type.to_s == 'integer'
761: 
762:         if limit.nil? || limit == 4
763:           'integer'
764:         elsif limit < 4
765:           'smallint'
766:         else
767:           'bigint'
768:         end
769:       end

Unescapes bytea output from a database to the binary string it represents. NOTE: This is NOT an inverse of escape_bytea! This is only to be used

      on escaped binary output from database drive.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 289
289:       def unescape_bytea(value)
290:         # In each case, check if the value actually is escaped PostgreSQL bytea output
291:         # or an unescaped Active Record attribute that was just written.
292:         if PGconn.respond_to?(:unescape_bytea)
293:           self.class.instance_eval do
294:             define_method(:unescape_bytea) do |value|
295:               if value =~ /\\\d{3}/
296:                 PGconn.unescape_bytea(value)
297:               else
298:                 value
299:               end
300:             end
301:           end
302:         else
303:           self.class.instance_eval do
304:             define_method(:unescape_bytea) do |value|
305:               if value =~ /\\\d{3}/
306:                 result = ''
307:                 i, max = 0, value.size
308:                 while i < max
309:                   char = value[i]
310:                   if char == ?\\
311:                     if value[i+1] == ?\\
312:                       char = ?\\
313:                       i += 1
314:                     else
315:                       char = value[i+1..i+3].oct
316:                       i += 3
317:                     end
318:                   end
319:                   result << char
320:                   i += 1
321:                 end
322:                 result
323:               else
324:                 value
325:               end
326:             end
327:           end
328:         end
329:         unescape_bytea(value)
330:       end

Executes an UPDATE query and returns the number of affected tuples.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 467
467:       def update_sql(sql, name = nil)
468:         super.cmd_tuples
469:       end

Protected Instance methods

Returns the version of the connected PostgreSQL version.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 808
808:         def postgresql_version
809:           @postgresql_version ||=
810:             if @connection.respond_to?(:server_version)
811:               @connection.server_version
812:             else
813:               # Mimic PGconn.server_version behavior
814:               begin
815:                 query('SELECT version()')[0][0] =~ /PostgreSQL (\d+)\.(\d+)\.(\d+)/
816:                 ($1.to_i * 10000) + ($2.to_i * 100) + $3.to_i
817:               rescue
818:                 0
819:               end
820:             end
821:         end

[Validate]