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" }
PQTRANS_IDLE = defined?(PGconn::PQTRANS_IDLE) ? PGconn::PQTRANS_IDLE : 0   ruby-pg defines Ruby constants for transaction status, ruby-postgres does not.

Public Class methods

Initializes and connects a PostgreSQL adapter.

[Source]

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

Returns ‘PostgreSQL’ as adapter name for identification purposes.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 201
201:       def adapter_name
202:         ADAPTER_NAME
203:       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 807
807:       def add_column(table_name, column_name, type, options = {})
808:         default = options[:default]
809:         notnull = options[:null] == false
810: 
811:         # Add the column.
812:         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])}")
813: 
814:         change_column_default(table_name, column_name, default) if options_include_default?(options)
815:         change_column_null(table_name, column_name, false, default) if notnull
816:       end

Begins a transaction.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 518
518:       def begin_db_transaction
519:         execute "BEGIN"
520:       end

Changes the column of a table.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 819
819:       def change_column(table_name, column_name, type, options = {})
820:         quoted_table_name = quote_table_name(table_name)
821: 
822:         begin
823:           execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
824:         rescue ActiveRecord::StatementInvalid => e
825:           raise e if postgresql_version > 80000
826:           # This is PostgreSQL 7.x, so we have to use a more arcane way of doing it.
827:           begin
828:             begin_db_transaction
829:             tmp_column_name = "#{column_name}_ar_tmp"
830:             add_column(table_name, tmp_column_name, type, options)
831:             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])})"
832:             remove_column(table_name, column_name)
833:             rename_column(table_name, tmp_column_name, column_name)
834:             commit_db_transaction
835:           rescue
836:             rollback_db_transaction
837:           end
838:         end
839: 
840:         change_column_default(table_name, column_name, options[:default]) if options_include_default?(options)
841:         change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null)
842:       end

Changes the default value of a table column.

[Source]

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

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 849
849:       def change_column_null(table_name, column_name, null, default = nil)
850:         unless null || default.nil?
851:           execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL")
852:         end
853:         execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL")
854:       end

Returns the current client message level.

[Source]

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

Set the client message level.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 718
718:       def client_min_messages=(level)
719:         execute("SET client_min_messages TO '#{level}'")
720:       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 674
674:       def columns(table_name, name = nil)
675:         # Limit, precision, and scale are all handled by the superclass.
676:         column_definitions(table_name).collect do |name, type, default, notnull|
677:           PostgreSQLColumn.new(name, default, type, notnull == 'f')
678:         end
679:       end

Commits a transaction.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 523
523:       def commit_db_transaction
524:         execute "COMMIT"
525:       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 585
585:       def create_database(name, options = {})
586:         options = options.reverse_merge(:encoding => "utf8")
587: 
588:         option_string = options.symbolize_keys.sum do |key, value|
589:           case key
590:           when :owner
591:             " OWNER = \"#{value}\""
592:           when :template
593:             " TEMPLATE = \"#{value}\""
594:           when :encoding
595:             " ENCODING = '#{value}'"
596:           when :tablespace
597:             " TABLESPACE = \"#{value}\""
598:           when :connection_limit
599:             " CONNECTION LIMIT = #{value}"
600:           else
601:             ""
602:           end
603:         end
604: 
605:         execute "CREATE DATABASE #{quote_table_name(name)}#{option_string}"
606:       end

Returns the current database name.

[Source]

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

Close the connection.

[Source]

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

Returns the current database encoding format.

[Source]

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

Escapes binary strings for bytea input to the database.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 285
285:       def escape_bytea(value)
286:         if PGconn.respond_to?(:escape_bytea)
287:           self.class.instance_eval do
288:             define_method(:escape_bytea) do |value|
289:               PGconn.escape_bytea(value) if value
290:             end
291:           end
292:         else
293:           self.class.instance_eval do
294:             define_method(:escape_bytea) do |value|
295:               if value
296:                 result = ''
297:                 value.each_byte { |c| result << sprintf('\\\\%03o', c) }
298:                 result
299:               end
300:             end
301:           end
302:         end
303:         escape_bytea(value)
304:       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 502
502:       def execute(sql, name = nil)
503:         log(sql, name) do
504:           if @async
505:             @connection.async_exec(sql)
506:           else
507:             @connection.exec(sql)
508:           end
509:         end
510:       end

Returns the list of all indexes for a table.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 637
637:       def indexes(table_name, name = nil)
638:          schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',')
639:          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)
640: 
641:         current_index = nil
642:         indexes = []
643: 
644:         result.each do |row|
645:           if current_index != row[0]
646:             indexes << IndexDefinition.new(table_name, row[0], row[1] == "t", [])
647:             current_index = row[0]
648:           end
649: 
650:           indexes.last.columns << row[2]
651:         end
652: 
653:         indexes
654:       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 435
435:       def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
436:         # Extract the table from the insert sql. Yuck.
437:         table = sql.split(" ", 4)[2].gsub('"', '')
438: 
439:         # Try an insert with 'returning id' if available (PG >= 8.2)
440:         if supports_insert_with_returning?
441:           pk, sequence_name = *pk_and_sequence_for(table) unless pk
442:           if pk
443:             id = select_value("#{sql} RETURNING #{quote_column_name(pk)}")
444:             clear_query_cache
445:             return id
446:           end
447:         end
448: 
449:         # Otherwise, insert then grab last_insert_id.
450:         if insert_id = super
451:           insert_id
452:         else
453:           # If neither pk nor sequence name is given, look them up.
454:           unless pk || sequence_name
455:             pk, sequence_name = *pk_and_sequence_for(table)
456:           end
457: 
458:           # If a pk is given, fallback to default sequence name.
459:           # Don't fetch last insert id for a table without a pk.
460:           if pk && sequence_name ||= default_sequence_name(table, pk)
461:             last_insert_id(table, sequence_name)
462:           end
463:         end
464:       end

Close then reopen the connection.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 228
228:       def reconnect!
229:         if @connection.respond_to?(:reset)
230:           @connection.reset
231:           configure_connection
232:         else
233:           disconnect!
234:           connect
235:         end
236:       end

Drops an index from a table.

[Source]

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

Renames a column in a table.

[Source]

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

Renames a table.

[Source]

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

Aborts a transaction.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 528
528:       def rollback_db_transaction
529:         execute "ROLLBACK"
530:       end

Returns the active schema search path.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 708
708:       def schema_search_path
709:         @schema_search_path ||= query('SHOW search_path')[0][0]
710:       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 700
700:       def schema_search_path=(schema_csv)
701:         if schema_csv
702:           execute "SET search_path TO #{schema_csv}"
703:           @schema_search_path = schema_csv
704:         end
705:       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 430
430:       def select_rows(sql, name = nil)
431:         select_raw(sql, name).last
432:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 272
272:       def supports_ddl_transactions?
273:         true
274:       end

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 268
268:       def supports_insert_with_returning?
269:         postgresql_version >= 80200
270:       end

Does PostgreSQL support migrations?

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 248
248:       def supports_migrations?
249:         true
250:       end

Does PostgreSQL support standard conforming strings?

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 253
253:       def supports_standard_conforming_strings?
254:         # Temporarily set the client message level above error to prevent unintentional
255:         # error messages in the logs when working on a PostgreSQL database server that
256:         # does not support standard conforming strings.
257:         client_min_messages_old = client_min_messages
258:         self.client_min_messages = 'panic'
259: 
260:         # postgres-pr does not raise an exception when client_min_messages is set higher
261:         # than error and "SHOW standard_conforming_strings" fails, but returns an empty
262:         # PGresult instead.
263:         has_support = query('SHOW standard_conforming_strings')[0][0] rescue false
264:         self.client_min_messages = client_min_messages_old
265:         has_support
266:       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 278
278:       def table_alias_length
279:         @table_alias_length ||= (postgresql_version >= 80000 ? query('SHOW max_identifier_length')[0][0].to_i : 63)
280:       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 626
626:       def tables(name = nil)
627:         schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',')
628:         query("SELECT tablename\nFROM pg_tables\nWHERE schemaname IN (\#{schemas})\n", name).map { |row| row[0] }
629:       end

Wrap a block in a transaction. Returns result of block.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 542
542:       def transaction(start_db_transaction = true)
543:         transaction_open = false
544:         begin
545:           if block_given?
546:             if start_db_transaction
547:               begin_db_transaction
548:               transaction_open = true
549:             end
550:             yield
551:           end
552:         rescue Exception => database_transaction_rollback
553:           if transaction_open && transaction_active?
554:             transaction_open = false
555:             rollback_db_transaction
556:           end
557:           raise unless database_transaction_rollback.is_a? ActiveRecord::Rollback
558:         end
559:       ensure
560:         if transaction_open && transaction_active?
561:           begin
562:             commit_db_transaction
563:           rescue Exception => database_transaction_rollback
564:             rollback_db_transaction
565:             raise
566:           end
567:         end
568:       end

Check whether a transaction is active.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 537
537:       def transaction_active?
538:         @connection.transaction_status != PQTRANS_IDLE
539:       end

Maps logical Rails types to PostgreSQL-specific data types.

[Source]

     # File vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 867
867:       def type_to_sql(type, limit = nil, precision = nil, scale = nil)
868:         return super unless type.to_s == 'integer'
869: 
870:         case limit
871:           when 1..2;      'smallint'
872:           when 3..4, nil; 'integer'
873:           when 5..8;      'bigint'
874:           else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with precision 0 instead.")
875:         end
876:       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 309
309:       def unescape_bytea(value)
310:         # In each case, check if the value actually is escaped PostgreSQL bytea output
311:         # or an unescaped Active Record attribute that was just written.
312:         if PGconn.respond_to?(:unescape_bytea)
313:           self.class.instance_eval do
314:             define_method(:unescape_bytea) do |value|
315:               if value =~ /\\\d{3}/
316:                 PGconn.unescape_bytea(value)
317:               else
318:                 value
319:               end
320:             end
321:           end
322:         else
323:           self.class.instance_eval do
324:             define_method(:unescape_bytea) do |value|
325:               if value =~ /\\\d{3}/
326:                 result = ''
327:                 i, max = 0, value.size
328:                 while i < max
329:                   char = value[i]
330:                   if char == ?\\
331:                     if value[i+1] == ?\\
332:                       char = ?\\
333:                       i += 1
334:                     else
335:                       char = value[i+1..i+3].oct
336:                       i += 3
337:                     end
338:                   end
339:                   result << char
340:                   i += 1
341:                 end
342:                 result
343:               else
344:                 value
345:               end
346:             end
347:           end
348:         end
349:         unescape_bytea(value)
350:       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 513
513:       def update_sql(sql, name = nil)
514:         super.cmd_tuples
515:       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 915
915:         def postgresql_version
916:           @postgresql_version ||=
917:             if @connection.respond_to?(:server_version)
918:               @connection.server_version
919:             else
920:               # Mimic PGconn.server_version behavior
921:               begin
922:                 query('SELECT version()')[0][0] =~ /PostgreSQL (\d+)\.(\d+)\.(\d+)/
923:                 ($1.to_i * 10000) + ($2.to_i * 100) + $3.to_i
924:               rescue
925:                 0
926:               end
927:             end
928:         end

[Validate]