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
- active?
- adapter_name
- add_column
- begin_db_transaction
- change_column
- change_column_default
- change_column_null
- client_min_messages
- client_min_messages=
- columns
- commit_db_transaction
- create_database
- disconnect!
- execute
- indexes
- insert
- new
- postgresql_version
- reconnect!
- remove_index
- rename_column
- rename_table
- rollback_db_transaction
- schema_search_path
- schema_search_path=
- select_rows
- supports_migrations?
- supports_standard_conforming_strings?
- table_alias_length
- tables
- type_to_sql
- update_sql
Public Class methods
Initializes and connects a PostgreSQL adapter.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 256
256: def initialize(connection, logger, connection_parameters, config)
257: super(connection, logger)
258: @connection_parameters, @config = connection_parameters, config
259:
260: connect
261: end
Public Instance methods
Is this connection alive and ready for queries?
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 264
264: def active?
265: if @connection.respond_to?(:status)
266: @connection.status == PGconn::CONNECTION_OK
267: else
268: # We're asking the driver, not ActiveRecord, so use @connection.query instead of #query
269: @connection.query 'SELECT 1'
270: true
271: end
272: # postgres-pr raises a NoMethodError when querying if no connection is available.
273: rescue PGError, NoMethodError
274: false
275: end
Returns ‘PostgreSQL’ as adapter name for identification purposes.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 251
251: def adapter_name
252: 'PostgreSQL'
253: end
Adds a new column to the named table. See TableDefinition#column for details of the options you can use.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 721
721: def add_column(table_name, column_name, type, options = {})
722: default = options[:default]
723: notnull = options[:null] == false
724:
725: # Add the column.
726: 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])}")
727:
728: change_column_default(table_name, column_name, default) if options_include_default?(options)
729: change_column_null(table_name, column_name, false, default) if notnull
730: end
Begins a transaction.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 485
485: def begin_db_transaction
486: execute "BEGIN"
487: end
Changes the column of a table.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 733
733: def change_column(table_name, column_name, type, options = {})
734: quoted_table_name = quote_table_name(table_name)
735:
736: begin
737: execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
738: rescue ActiveRecord::StatementInvalid
739: # This is PostgreSQL 7.x, so we have to use a more arcane way of doing it.
740: begin
741: begin_db_transaction
742: tmp_column_name = "#{column_name}_ar_tmp"
743: add_column(table_name, tmp_column_name, type, options)
744: 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])})"
745: remove_column(table_name, column_name)
746: rename_column(table_name, tmp_column_name, column_name)
747: commit_db_transaction
748: rescue
749: rollback_db_transaction
750: end
751: end
752:
753: change_column_default(table_name, column_name, options[:default]) if options_include_default?(options)
754: change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null)
755: end
Changes the default value of a table column.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 758
758: def change_column_default(table_name, column_name, default)
759: execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote(default)}"
760: end
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 762
762: def change_column_null(table_name, column_name, null, default = nil)
763: unless null || default.nil?
764: execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL")
765: end
766: execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL")
767: end
Returns the current client message level.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 627
627: def client_min_messages
628: query('SHOW client_min_messages')[0][0]
629: end
Set the client message level.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 632
632: def client_min_messages=(level)
633: execute("SET client_min_messages TO '#{level}'")
634: end
Returns the list of all column definitions for a table.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 602
602: def columns(table_name, name = nil)
603: # Limit, precision, and scale are all handled by the superclass.
604: column_definitions(table_name).collect do |name, type, default, notnull|
605: PostgreSQLColumn.new(name, default, type, notnull == 'f')
606: end
607: end
Commits a transaction.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 490
490: def commit_db_transaction
491: execute "COMMIT"
492: 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'
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 513
513: def create_database(name, options = {})
514: options = options.reverse_merge(:encoding => "utf8")
515:
516: option_string = options.symbolize_keys.sum do |key, value|
517: case key
518: when :owner
519: " OWNER = '#{value}'"
520: when :template
521: " TEMPLATE = #{value}"
522: when :encoding
523: " ENCODING = '#{value}'"
524: when :tablespace
525: " TABLESPACE = #{value}"
526: when :connection_limit
527: " CONNECTION LIMIT = #{value}"
528: else
529: ""
530: end
531: end
532:
533: execute "CREATE DATABASE #{quote_table_name(name)}#{option_string}"
534: end
Close the connection.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 289
289: def disconnect!
290: @connection.close rescue nil
291: end
Executes an SQL statement, returning a PGresult object on success or raising a PGError exception otherwise.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 469
469: def execute(sql, name = nil)
470: log(sql, name) do
471: if @async
472: @connection.async_exec(sql)
473: else
474: @connection.exec(sql)
475: end
476: end
477: end
Returns the list of all indexes for a table.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 565
565: def indexes(table_name, name = nil)
566: schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',')
567: 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)
568:
569: current_index = nil
570: indexes = []
571:
572: result.each do |row|
573: if current_index != row[0]
574: indexes << IndexDefinition.new(table_name, row[0], row[1] == "t", [])
575: current_index = row[0]
576: end
577:
578: indexes.last.columns << row[2]
579: end
580:
581: indexes
582: end
Executes an INSERT query and returns the new record‘s ID
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 422
422: def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
423: if insert_id = super
424: insert_id
425: else
426: # Extract the table from the insert sql. Yuck.
427: table = sql.split(" ", 4)[2].gsub('"', '')
428:
429: # If neither pk nor sequence name is given, look them up.
430: unless pk || sequence_name
431: pk, sequence_name = *pk_and_sequence_for(table)
432: end
433:
434: # If a pk is given, fallback to default sequence name.
435: # Don't fetch last insert id for a table without a pk.
436: if pk && sequence_name ||= default_sequence_name(table, pk)
437: last_insert_id(table, sequence_name)
438: end
439: end
440: end
Close then reopen the connection.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 278
278: def reconnect!
279: if @connection.respond_to?(:reset)
280: @connection.reset
281: configure_connection
282: else
283: disconnect!
284: connect
285: end
286: end
Drops an index from a table.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 775
775: def remove_index(table_name, options = {})
776: execute "DROP INDEX #{index_name(table_name, options)}"
777: end
Renames a column in a table.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 770
770: def rename_column(table_name, column_name, new_column_name)
771: execute "ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}"
772: end
Renames a table.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 715
715: def rename_table(name, new_name)
716: execute "ALTER TABLE #{quote_table_name(name)} RENAME TO #{quote_table_name(new_name)}"
717: end
Aborts a transaction.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 495
495: def rollback_db_transaction
496: execute "ROLLBACK"
497: end
Returns the active schema search path.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 622
622: def schema_search_path
623: @schema_search_path ||= query('SHOW search_path')[0][0]
624: 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.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 614
614: def schema_search_path=(schema_csv)
615: if schema_csv
616: execute "SET search_path TO #{schema_csv}"
617: @schema_search_path = schema_csv
618: end
619: end
Executes a SELECT query and returns an array of rows. Each row is an array of field values.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 417
417: def select_rows(sql, name = nil)
418: select_raw(sql, name).last
419: end
Does PostgreSQL support migrations?
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 311
311: def supports_migrations?
312: true
313: end
Does PostgreSQL support standard conforming strings?
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 316
316: def supports_standard_conforming_strings?
317: # Temporarily set the client message level above error to prevent unintentional
318: # error messages in the logs when working on a PostgreSQL database server that
319: # does not support standard conforming strings.
320: client_min_messages_old = client_min_messages
321: self.client_min_messages = 'panic'
322:
323: # postgres-pr does not raise an exception when client_min_messages is set higher
324: # than error and "SHOW standard_conforming_strings" fails, but returns an empty
325: # PGresult instead.
326: has_support = query('SHOW standard_conforming_strings')[0][0] rescue false
327: self.client_min_messages = client_min_messages_old
328: has_support
329: end
Returns the configured supported identifier length supported by PostgreSQL, or report the default of 63 on PostgreSQL 7.x.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 333
333: def table_alias_length
334: @table_alias_length ||= (postgresql_version >= 80000 ? query('SHOW max_identifier_length')[0][0].to_i : 63)
335: end
Returns the list of all tables in the schema search path or a specified schema.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 554
554: def tables(name = nil)
555: schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',')
556: query("SELECT tablename\nFROM pg_tables\nWHERE schemaname IN (\#{schemas})\n", name).map { |row| row[0] }
557: end
Maps logical Rails types to PostgreSQL-specific data types.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 780
780: def type_to_sql(type, limit = nil, precision = nil, scale = nil)
781: return super unless type.to_s == 'integer'
782:
783: case limit
784: when 1..2; 'smallint'
785: when 3..4, nil; 'integer'
786: when 5..8; 'bigint'
787: else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with precision 0 instead.")
788: end
789: end
Executes an UPDATE query and returns the number of affected tuples.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 480
480: def update_sql(sql, name = nil)
481: super.cmd_tuples
482: end
Protected Instance methods
Returns the version of the connected PostgreSQL version.
[ show source ]
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 828
828: def postgresql_version
829: @postgresql_version ||=
830: if @connection.respond_to?(:server_version)
831: @connection.server_version
832: else
833: # Mimic PGconn.server_version behavior
834: begin
835: query('SELECT version()')[0][0] =~ /PostgreSQL (\d+)\.(\d+)\.(\d+)/
836: ($1.to_i * 10000) + ($2.to_i * 100) + $3.to_i
837: rescue
838: 0
839: end
840: end
841: end