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

Constants

ADAPTER_NAME = 'PostgreSQL'.freeze
NATIVE_DATABASE_TYPES = { :primary_key => "serial primary key".freeze, :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" }, :xml => { :name => "xml" }

Public Class methods

Initializes and connects a PostgreSQL adapter.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 213
213:       def initialize(connection, logger, connection_parameters, config)
214:         super(connection, logger)
215:         @connection_parameters, @config = connection_parameters, config
216: 
217:         connect
218:       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 221
221:       def active?
222:         if @connection.respond_to?(:status)
223:           @connection.status == PGconn::CONNECTION_OK
224:         else
225:           # We're asking the driver, not ActiveRecord, so use @connection.query instead of #query
226:           @connection.query 'SELECT 1'
227:           true
228:         end
229:       # postgres-pr raises a NoMethodError when querying if no connection is available.
230:       rescue PGError, NoMethodError
231:         false
232:       end

Returns ‘PostgreSQL’ as adapter name for identification purposes.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 208
208:       def adapter_name
209:         ADAPTER_NAME
210:       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 845
845:       def add_column(table_name, column_name, type, options = {})
846:         default = options[:default]
847:         notnull = options[:null] == false
848: 
849:         # Add the column.
850:         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])}")
851: 
852:         change_column_default(table_name, column_name, default) if options_include_default?(options)
853:         change_column_null(table_name, column_name, false, default) if notnull
854:       end

Begins a transaction.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 565
565:       def begin_db_transaction
566:         execute "BEGIN"
567:       end

Changes the column of a table.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 857
857:       def change_column(table_name, column_name, type, options = {})
858:         quoted_table_name = quote_table_name(table_name)
859: 
860:         begin
861:           execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
862:         rescue ActiveRecord::StatementInvalid => e
863:           raise e if postgresql_version > 80000
864:           # This is PostgreSQL 7.x, so we have to use a more arcane way of doing it.
865:           begin
866:             begin_db_transaction
867:             tmp_column_name = "#{column_name}_ar_tmp"
868:             add_column(table_name, tmp_column_name, type, options)
869:             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])})"
870:             remove_column(table_name, column_name)
871:             rename_column(table_name, tmp_column_name, column_name)
872:             commit_db_transaction
873:           rescue
874:             rollback_db_transaction
875:           end
876:         end
877: 
878:         change_column_default(table_name, column_name, options[:default]) if options_include_default?(options)
879:         change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null)
880:       end

Changes the default value of a table column.

[Source]

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

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 887
887:       def change_column_null(table_name, column_name, null, default = nil)
888:         unless null || default.nil?
889:           execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL")
890:         end
891:         execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL")
892:       end

Returns the current client message level.

[Source]

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

Set the client message level.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 750
750:       def client_min_messages=(level)
751:         execute("SET client_min_messages TO '#{level}'")
752:       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 706
706:       def columns(table_name, name = nil)
707:         # Limit, precision, and scale are all handled by the superclass.
708:         column_definitions(table_name).collect do |name, type, default, notnull|
709:           PostgreSQLColumn.new(name, default, type, notnull == 'f')
710:         end
711:       end

Commits a transaction.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 570
570:       def commit_db_transaction
571:         execute "COMMIT"
572:       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 613
613:       def create_database(name, options = {})
614:         options = options.reverse_merge(:encoding => "utf8")
615: 
616:         option_string = options.symbolize_keys.sum do |key, value|
617:           case key
618:           when :owner
619:             " OWNER = \"#{value}\""
620:           when :template
621:             " TEMPLATE = \"#{value}\""
622:           when :encoding
623:             " ENCODING = '#{value}'"
624:           when :tablespace
625:             " TABLESPACE = \"#{value}\""
626:           when :connection_limit
627:             " CONNECTION LIMIT = #{value}"
628:           else
629:             ""
630:           end
631:         end
632: 
633:         execute "CREATE DATABASE #{quote_table_name(name)}#{option_string}"
634:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 587
587:       def create_savepoint
588:         execute("SAVEPOINT #{current_savepoint_name}")
589:       end

Returns the current database name.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 714
714:       def current_database
715:         query('select current_database()')[0][0]
716:       end

Close the connection.

[Source]

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

Returns the current database encoding format.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 719
719:       def encoding
720:         query("SELECT pg_encoding_to_char(pg_database.encoding) FROM pg_database\nWHERE pg_database.datname LIKE '\#{current_database}'\n")[0][0]
721:       end

Escapes binary strings for bytea input to the database.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 301
301:       def escape_bytea(value)
302:         if @connection.respond_to?(:escape_bytea)
303:           self.class.instance_eval do
304:             define_method(:escape_bytea) do |value|
305:               @connection.escape_bytea(value) if value
306:             end
307:           end
308:         elsif PGconn.respond_to?(:escape_bytea)
309:           self.class.instance_eval do
310:             define_method(:escape_bytea) do |value|
311:               PGconn.escape_bytea(value) if value
312:             end
313:           end
314:         else
315:           self.class.instance_eval do
316:             define_method(:escape_bytea) do |value|
317:               if value
318:                 result = ''
319:                 value.each_byte { |c| result << sprintf('\\\\%03o', c) }
320:                 result
321:               end
322:             end
323:           end
324:         end
325:         escape_bytea(value)
326:       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 549
549:       def execute(sql, name = nil)
550:         log(sql, name) do
551:           if @async
552:             @connection.async_exec(sql)
553:           else
554:             @connection.exec(sql)
555:           end
556:         end
557:       end

Returns the list of all indexes for a table.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 665
665:       def indexes(table_name, name = nil)
666:          schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',')
667:          result = query("SELECT distinct i.relname, d.indisunique, d.indkey, t.oid\nFROM pg_class t, pg_class i, pg_index d\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}) )\nORDER BY i.relname\n", name)
668: 
669: 
670:         indexes = []
671: 
672:         indexes = result.map do |row|
673:           index_name = row[0]
674:           unique = row[1] == 't'
675:           indkey = row[2].split(" ")
676:           oid = row[3]
677: 
678:           columns = query("SELECT a.attname, a.attnum\nFROM pg_attribute a\nWHERE a.attrelid = \#{oid}\nAND a.attnum IN (\#{indkey.join(\",\")})\n", "Columns for index #{row[0]} on #{table_name}").inject({}) {|attlist, r| attlist[r[1]] = r[0]; attlist}
679: 
680:           column_names = indkey.map {|attnum| columns[attnum] }
681:           IndexDefinition.new(table_name, index_name, unique, column_names)
682: 
683:         end
684: 
685:         indexes
686:       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 482
482:       def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
483:         # Extract the table from the insert sql. Yuck.
484:         table = sql.split(" ", 4)[2].gsub('"', '')
485: 
486:         # Try an insert with 'returning id' if available (PG >= 8.2)
487:         if supports_insert_with_returning?
488:           pk, sequence_name = *pk_and_sequence_for(table) unless pk
489:           if pk
490:             id = select_value("#{sql} RETURNING #{quote_column_name(pk)}")
491:             clear_query_cache
492:             return id
493:           end
494:         end
495: 
496:         # Otherwise, insert then grab last_insert_id.
497:         if insert_id = super
498:           insert_id
499:         else
500:           # If neither pk nor sequence name is given, look them up.
501:           unless pk || sequence_name
502:             pk, sequence_name = *pk_and_sequence_for(table)
503:           end
504: 
505:           # If a pk is given, fallback to default sequence name.
506:           # Don't fetch last insert id for a table without a pk.
507:           if pk && sequence_name ||= default_sequence_name(table, pk)
508:             last_insert_id(table, sequence_name)
509:           end
510:         end
511:       end

The ruby-pg driver supports inspecting the transaction status, while the ruby-postgres driver does not.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 582
582:         def outside_transaction?
583:           @connection.transaction_status == PGconn::PQTRANS_IDLE
584:         end

Returns just a table‘s primary key

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 833
833:       def primary_key(table)
834:         pk_and_sequence = pk_and_sequence_for(table)
835:         pk_and_sequence && pk_and_sequence.first
836:       end

Checks the following cases:

  • table_name
  • "table.name"
  • schema_name.table_name
  • schema_name."table.name"
  • "schema.name".table_name
  • "schema.name"."table.name"

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 427
427:       def quote_table_name(name)
428:         schema, name_part = extract_pg_identifier_from_name(name.to_s)
429: 
430:         unless name_part
431:           quote_column_name(schema)
432:         else
433:           table_name, name_part = extract_pg_identifier_from_name(name_part)
434:           "#{quote_column_name(schema)}.#{quote_column_name(table_name)}"
435:         end
436:       end

Close then reopen the connection.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 235
235:       def reconnect!
236:         if @connection.respond_to?(:reset)
237:           @connection.reset
238:           configure_connection
239:         else
240:           disconnect!
241:           connect
242:         end
243:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 595
595:       def release_savepoint
596:         execute("RELEASE SAVEPOINT #{current_savepoint_name}")
597:       end

Drops an index from a table.

[Source]

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

Renames a column in a table.

[Source]

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

Renames a table.

[Source]

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

Aborts a transaction.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 575
575:       def rollback_db_transaction
576:         execute "ROLLBACK"
577:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 591
591:       def rollback_to_savepoint
592:         execute("ROLLBACK TO SAVEPOINT #{current_savepoint_name}")
593:       end

Returns the active schema search path.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 740
740:       def schema_search_path
741:         @schema_search_path ||= query('SHOW search_path')[0][0]
742:       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 732
732:       def schema_search_path=(schema_csv)
733:         if schema_csv
734:           execute "SET search_path TO #{schema_csv}"
735:           @schema_search_path = schema_csv
736:         end
737:       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 477
477:       def select_rows(sql, name = nil)
478:         select_raw(sql, name).last
479:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 284
284:       def supports_ddl_transactions?
285:         true
286:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 280
280:       def supports_insert_with_returning?
281:         postgresql_version >= 80200
282:       end

Does PostgreSQL support migrations?

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 255
255:       def supports_migrations?
256:         true
257:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 288
288:       def supports_savepoints?
289:         true
290:       end

Does PostgreSQL support standard conforming strings?

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 265
265:       def supports_standard_conforming_strings?
266:         # Temporarily set the client message level above error to prevent unintentional
267:         # error messages in the logs when working on a PostgreSQL database server that
268:         # does not support standard conforming strings.
269:         client_min_messages_old = client_min_messages
270:         self.client_min_messages = 'panic'
271: 
272:         # postgres-pr does not raise an exception when client_min_messages is set higher
273:         # than error and "SHOW standard_conforming_strings" fails, but returns an empty
274:         # PGresult instead.
275:         has_support = query('SHOW standard_conforming_strings')[0][0] rescue false
276:         self.client_min_messages = client_min_messages_old
277:         has_support
278:       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 294
294:       def table_alias_length
295:         @table_alias_length ||= (postgresql_version >= 80000 ? query('SHOW max_identifier_length')[0][0].to_i : 63)
296:       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 654
654:       def tables(name = nil)
655:         schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',')
656:         query("SELECT tablename\nFROM pg_tables\nWHERE schemaname IN (\#{schemas})\n", name).map { |row| row[0] }
657:       end

Maps logical Rails types to PostgreSQL-specific data types.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 905
905:       def type_to_sql(type, limit = nil, precision = nil, scale = nil)
906:         return super unless type.to_s == 'integer'
907: 
908:         case limit
909:           when 1..2;      'smallint'
910:           when 3..4, nil; 'integer'
911:           when 5..8;      'bigint'
912:           else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with precision 0 instead.")
913:         end
914:       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 331
331:       def unescape_bytea(value)
332:         # In each case, check if the value actually is escaped PostgreSQL bytea output
333:         # or an unescaped Active Record attribute that was just written.
334:         if PGconn.respond_to?(:unescape_bytea)
335:           self.class.instance_eval do
336:             define_method(:unescape_bytea) do |value|
337:               if value =~ /\\\d{3}/
338:                 PGconn.unescape_bytea(value)
339:               else
340:                 value
341:               end
342:             end
343:           end
344:         else
345:           self.class.instance_eval do
346:             define_method(:unescape_bytea) do |value|
347:               if value =~ /\\\d{3}/
348:                 result = ''
349:                 i, max = 0, value.size
350:                 while i < max
351:                   char = value[i]
352:                   if char == ?\\
353:                     if value[i+1] == ?\\
354:                       char = ?\\
355:                       i += 1
356:                     else
357:                       char = value[i+1..i+3].oct
358:                       i += 3
359:                     end
360:                   end
361:                   result << char
362:                   i += 1
363:                 end
364:                 result
365:               else
366:                 value
367:               end
368:             end
369:           end
370:         end
371:         unescape_bytea(value)
372:       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 560
560:       def update_sql(sql, name = nil)
561:         super.cmd_tuples
562:       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 953
953:         def postgresql_version
954:           @postgresql_version ||=
955:             if @connection.respond_to?(:server_version)
956:               @connection.server_version
957:             else
958:               # Mimic PGconn.server_version behavior
959:               begin
960:                 query('SELECT version()')[0][0] =~ /PostgreSQL (\d+)\.(\d+)\.(\d+)/
961:                 ($1.to_i * 10000) + ($2.to_i * 100) + $3.to_i
962:               rescue
963:                 0
964:               end
965:             end
966:         end

[Validate]