class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
The PostgreSQL adapter works with the native C (bitbucket.org/ged/ruby-pg) driver.
Options:
-
:host
- Defaults to a Unix-domain socket in /tmp. On machines without Unix-domain sockets, the default is to connect to localhost. -
:port
- Defaults to 5432. -
:username
- Defaults to be the same as the operating system name of the user running the application. -
:password
- Password to be used if the server demands password authentication. -
:database
- Defaults to be the same as the user name. -
: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 aSET client_encoding TO <encoding>
call on the connection. -
:min_messages
- An optional client min messages that is used in aSET client_min_messages TO <min_messages>
call on the connection. -
:variables
- An optional hash of additional parameters that will be used inSET SESSION key = val
calls on the connection. -
:insert_returning
- An optional boolean to control the use ofRETURNING
forINSERT
statements defaults to true.
Any further options are used as connection parameters to libpq. See www.postgresql.org/docs/current/static/libpq-connect.html for the list of parameters.
In addition, default connection parameters of libpq can be set per environment variables. See www.postgresql.org/docs/current/static/libpq-envars.html .
Constants
- ADAPTER_NAME
- CACHED_PLAN_HEURISTIC
Annoyingly, the code for prepared statements whose return value may have changed is FEATURE_NOT_SUPPORTED.
This covers various different error types so we need to do additional work to classify the exception definitively as a ActiveRecord::PreparedStatementCacheExpired
Check here for more details: git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/cache/plancache.c#l573
- DEADLOCK_DETECTED
- FOREIGN_KEY_VIOLATION
- NATIVE_DATABASE_TYPES
- NOT_NULL_VIOLATION
- NUMERIC_VALUE_OUT_OF_RANGE
- SERIALIZATION_FAILURE
- UNIQUE_VIOLATION
- VALUE_LIMIT_VIOLATION
See www.postgresql.org/docs/current/static/errcodes-appendix.html
Public Class Methods
Initializes and connects a PostgreSQL adapter.
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 211 def initialize(connection, logger, connection_parameters, config) super(connection, logger, config) @connection_parameters = connection_parameters # @local_tz is initialized as nil to avoid warnings when connect tries to use it @local_tz = nil @max_identifier_length = nil connect add_pg_encoders @statements = StatementPool.new @connection, self.class.type_cast_config_to_integer(config[:statement_limit]) if postgresql_version < 90100 raise "Your version of PostgreSQL (#{postgresql_version}) is too old. Active Record supports PostgreSQL >= 9.1." end add_pg_decoders @type_map = Type::HashLookupTypeMap.new initialize_type_map(type_map) @local_tz = execute("SHOW TIME ZONE", "SCHEMA").first["TimeZone"] @use_insert_returning = @config.key?(:insert_returning) ? self.class.type_cast_config_to_boolean(@config[:insert_returning]) : true end
Public Instance Methods
Is this connection alive and ready for queries?
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 249 def active? @lock.synchronize do @connection.query "SELECT 1" end true rescue PG::Error false end
Clears the prepared statements cache.
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 238 def clear_cache! @lock.synchronize do @statements.clear end end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 345 def disable_extension(name) exec_query("DROP EXTENSION IF EXISTS \"#{name}\" CASCADE").tap { reload_type_map } end
Disconnects from the database if already connected. Otherwise, this method does nothing.
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 281 def disconnect! @lock.synchronize do super @connection.close rescue nil end end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 339 def enable_extension(name) exec_query("CREATE EXTENSION IF NOT EXISTS \"#{name}\"").tap { reload_type_map } end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 351 def extension_enabled?(name) if supports_extensions? res = exec_query("SELECT EXISTS(SELECT * FROM pg_available_extensions WHERE name = '#{name}' AND installed_version IS NOT NULL) as enabled", "SCHEMA") res.cast_values.first end end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 358 def extensions if supports_extensions? exec_query("SELECT extname FROM pg_extension", "SCHEMA").cast_values else super end end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 178 def index_algorithms { concurrently: "CONCURRENTLY" } end
Returns the version of the connected PostgreSQL server.
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 397 def postgresql_version @connection.server_version end
Close then reopen the connection.
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 259 def reconnect! @lock.synchronize do super @connection.reset configure_connection end end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 267 def reset! @lock.synchronize do clear_cache! reset_transaction unless @connection.transaction_status == ::PG::PQTRANS_IDLE @connection.query "ROLLBACK" end @connection.query "DISCARD ALL" configure_connection end end
Set the authorized user for this session
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 373 def session_auth=(user) clear_cache! execute("SET SESSION AUTHORIZATION #{user}") end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 292 def set_standard_conforming_strings execute("SET standard_conforming_strings = on", "SCHEMA") end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 300 def supports_advisory_locks? true end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 170 def supports_comments? true end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 162 def supports_datetime_with_precision? true end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 296 def supports_ddl_transactions? true end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 304 def supports_explain? true end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 146 def supports_expression_index? true end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 308 def supports_extensions? true end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 154 def supports_foreign_keys? true end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 138 def supports_index_sort_order? true end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 166 def supports_json? postgresql_version >= 90200 end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 317 def supports_materialized_views? postgresql_version >= 90300 end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 142 def supports_partial_index? true end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 321 def supports_pgcrypto_uuid? postgresql_version >= 90400 end
Range datatypes weren't introduced until PostgreSQL 9.2
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 313 def supports_ranges? postgresql_version >= 90200 end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 174 def supports_savepoints? true end
Returns true, since this connection adapter supports prepared statement caching.
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 134 def supports_statement_cache? true end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 150 def supports_transaction_isolation? true end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 158 def supports_views? true end
Returns the configured supported identifier length supported by PostgreSQL
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 367 def table_alias_length @max_identifier_length ||= query_value("SHOW max_identifier_length", "SCHEMA").to_i end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 244 def truncate(table_name, name = nil) exec_query "TRUNCATE TABLE #{quote_table_name(table_name)}", name, [] end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 378 def use_insert_returning? @use_insert_returning end
Private Instance Methods
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 816 def add_pg_decoders coders_by_name = { "int2" => PG::TextDecoder::Integer, "int4" => PG::TextDecoder::Integer, "int8" => PG::TextDecoder::Integer, "oid" => PG::TextDecoder::Integer, "float4" => PG::TextDecoder::Float, "float8" => PG::TextDecoder::Float, "bool" => PG::TextDecoder::Boolean, } known_coder_types = coders_by_name.keys.map { |n| quote(n) } query = " SELECT t.oid, t.typname FROM pg_type as t WHERE t.typname IN (%s) " % known_coder_types.join(", ") coders = execute_and_clear(query, "SCHEMA", []) do |result| result .map { |row| construct_coder(row, coders_by_name[row["typname"]]) } .compact end map = PG::TypeMapByOid.new coders.each { |coder| map.add_coder(coder) } @connection.type_map_for_results = map end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 808 def add_pg_encoders map = PG::TypeMapByClass.new map[Integer] = PG::TextEncoder::Integer.new map[TrueClass] = PG::TextEncoder::Boolean.new map[FalseClass] = PG::TextEncoder::Boolean.new @connection.type_map_for_queries = map end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 786 def can_perform_case_insensitive_comparison_for?(column) @case_insensitive_cache ||= {} @case_insensitive_cache[column.sql_type] ||= begin sql = <<-end_sql SELECT exists( SELECT * FROM pg_proc WHERE proname = 'lower' AND proargtypes = ARRAY[#{quote column.sql_type}::regtype]::oidvector ) OR exists( SELECT * FROM pg_proc INNER JOIN pg_cast ON ARRAY[casttarget]::oidvector = proargtypes WHERE proname = 'lower' AND castsource = #{quote column.sql_type}::regtype ) end_sql execute_and_clear(sql, "SCHEMA", []) do |result| result.getvalue(0, 0) end end end
Returns the list of a table's column names, data types, and default values.
The underlying query is roughly:
SELECT column.name, column.type, default.value, column.comment FROM column LEFT JOIN default ON column.table_id = default.table_id AND column.num = default.column_num WHERE column.table_id = get_table_id('table_name') AND column.num > 0 AND NOT column.is_dropped ORDER BY column.num
If the table name is not prefixed with a schema, the database will take the first match from the schema search path.
Query implementation notes:
- format_type includes the column size constraint, e.g. varchar(50) - ::regclass is a function that gives the id for a table name
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 762 def column_definitions(table_name) query(" SELECT a.attname, format_type(a.atttypid, a.atttypmod), pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod, c.collname, col_description(a.attrelid, a.attnum) AS comment FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum LEFT JOIN pg_type t ON a.atttypid = t.oid LEFT JOIN pg_collation c ON a.attcollation = c.oid AND a.attcollation <> t.typcollation WHERE a.attrelid = #{quote(quote_table_name(table_name))}::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum ", "SCHEMA") end
Configures the encoding, verbosity, schema search path, and time zone of the connection. This is called by connect and should not be called manually.
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 707 def configure_connection if @config[:encoding] @connection.set_client_encoding(@config[: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 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 @local_tz execute("SET time zone '#{@local_tz}'", "SCHEMA") end # SET statements from :variables config hash # http://www.postgresql.org/docs/current/static/sql-set.html variables = @config[:variables] || {} 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
Connects to a PostgreSQL server and sets up the adapter depending on the connected server's characteristics.
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 694 def connect @connection = PG.connect(@connection_parameters) configure_connection rescue ::PG::Error => error if error.message.include?("does not exist") raise ActiveRecord::NoDatabaseError else raise end end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 843 def construct_coder(row, coder_class) return unless coder_class coder_class.new(oid: row["oid"].to_i, name: row["typname"]) end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 782 def create_table_definition(*args) PostgreSQL::TableDefinition.new(*args) end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 619 def exec_cache(sql, name, binds) stmt_key = prepare_statement(sql) type_casted_binds = type_casted_binds(binds) log(sql, name, binds, type_casted_binds, stmt_key) do ActiveSupport::Dependencies.interlock.permit_concurrent_loads do @connection.exec_prepared(stmt_key, type_casted_binds) end end rescue ActiveRecord::StatementInvalid => e raise unless is_cached_plan_failure?(e) # Nothing we can do if we are in a transaction because all commands # will raise InFailedSQLTransaction if in_transaction? raise ActiveRecord::PreparedStatementCacheExpired.new(e.cause.message) else @lock.synchronize do # outside of transactions we can simply flush this query and retry @statements.delete sql_key(sql) end retry end end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 610 def exec_no_cache(sql, name, binds) type_casted_binds = type_casted_binds(binds) log(sql, name, binds, type_casted_binds) do ActiveSupport::Dependencies.interlock.permit_concurrent_loads do @connection.async_exec(sql, type_casted_binds) end end end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 597 def execute_and_clear(sql, name, binds, prepare: false) if without_prepared_statement?(binds) result = exec_no_cache(sql, name, []) elsif !prepare result = exec_no_cache(sql, name, binds) else result = exec_cache(sql, name, binds) end ret = yield result result.clear ret end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 560 def extract_default_function(default_value, default) default if has_default_function?(default_value, default) end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 522 def extract_limit(sql_type) case sql_type when /^bigint/i, /^int8/i 8 when /^smallint/i 2 else super end end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 777 def extract_table_ref_from_insert_sql(sql) sql[/into\s("[A-Za-z0-9_."\[\]\s]+"|[A-Za-z0-9_."\[\]]+)\s*/im] $1.strip if $1 end
Extracts the value from a PostgreSQL column default definition.
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 534 def extract_value_from_default(default) case default # Quoted types when /\A[\(B]?'(.*)'.*::"?([\w. ]+)"?(?:\[\])?\z/m # The default 'now'::date is CURRENT_DATE if $1 == "now".freeze && $2 == "date".freeze nil else $1.gsub("''".freeze, "'".freeze) end # Boolean types when "true".freeze, "false".freeze default # Numeric types when /\A\(?(-?\d+(\.\d*)?)\)?(::bigint)?\z/ $1 # Object identifier types when /\A-?\d+\z/ $1 else # Anything else is blank, some user type, or some function # and we can't know the value of that, so return nil. nil end end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 439 def get_oid_type(oid, fmod, column_name, sql_type = "".freeze) if !type_map.key?(oid) load_additional_types(type_map, [oid]) end type_map.fetch(oid, fmod, sql_type) { warn "unknown OID #{oid}: failed to recognize type of '#{column_name}'. It will be treated as String." Type.default_value.tap do |cast_type| type_map.register_type(oid, cast_type) end } end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 564 def has_default_function?(default_value, default) !default_value && %r{\w+\(.*\)|\(.*\)::\w+|CURRENT_DATE|CURRENT_TIMESTAMP}.match?(default) end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 662 def in_transaction? open_transactions > 0 end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 452 def initialize_type_map(m) register_class_with_limit m, "int2", Type::Integer register_class_with_limit m, "int4", Type::Integer register_class_with_limit m, "int8", Type::Integer m.register_type "oid", OID::Oid.new m.register_type "float4", Type::Float.new m.alias_type "float8", "float4" m.register_type "text", Type::Text.new register_class_with_limit m, "varchar", Type::String m.alias_type "char", "varchar" m.alias_type "name", "varchar" m.alias_type "bpchar", "varchar" m.register_type "bool", Type::Boolean.new register_class_with_limit m, "bit", OID::Bit register_class_with_limit m, "varbit", OID::BitVarying m.alias_type "timestamptz", "timestamp" m.register_type "date", Type::Date.new m.register_type "money", OID::Money.new m.register_type "bytea", OID::Bytea.new m.register_type "point", OID::Point.new m.register_type "hstore", OID::Hstore.new m.register_type "json", OID::Json.new m.register_type "jsonb", OID::Jsonb.new m.register_type "cidr", OID::Cidr.new m.register_type "inet", OID::Inet.new m.register_type "uuid", OID::Uuid.new m.register_type "xml", OID::Xml.new m.register_type "tsvector", OID::SpecializedString.new(:tsvector) m.register_type "macaddr", OID::SpecializedString.new(:macaddr) m.register_type "citext", OID::SpecializedString.new(:citext) m.register_type "ltree", OID::SpecializedString.new(:ltree) m.register_type "line", OID::SpecializedString.new(:line) m.register_type "lseg", OID::SpecializedString.new(:lseg) m.register_type "box", OID::SpecializedString.new(:box) m.register_type "path", OID::SpecializedString.new(:path) m.register_type "polygon", OID::SpecializedString.new(:polygon) m.register_type "circle", OID::SpecializedString.new(:circle) m.register_type "interval" do |_, _, sql_type| precision = extract_precision(sql_type) OID::SpecializedString.new(:interval, precision: precision) end register_class_with_precision m, "time", Type::Time register_class_with_precision m, "timestamp", OID::DateTime m.register_type "numeric" do |_, fmod, sql_type| precision = extract_precision(sql_type) scale = extract_scale(sql_type) # The type for the numeric depends on the width of the field, # so we'll do something special here. # # When dealing with decimal columns: # # places after decimal = fmod - 4 & 0xffff # places before decimal = (fmod - 4) >> 16 & 0xffff if fmod && (fmod - 4 & 0xffff).zero? # FIXME: Remove this class, and the second argument to # lookups on PG Type::DecimalWithoutScale.new(precision: precision) else OID::Decimal.new(precision: precision, scale: scale) end end load_additional_types(m) end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 654 def is_cached_plan_failure?(e) pgerror = e.cause code = pgerror.result.result_error_field(PG::PG_DIAG_SQLSTATE) code == FEATURE_NOT_SUPPORTED && pgerror.message.include?(CACHED_PLAN_HEURISTIC) rescue false end
Returns the current ID of a table's sequence.
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 740 def last_insert_id_result(sequence_name) exec_query("SELECT currval('#{sequence_name}')", "SQL") end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 568 def load_additional_types(type_map, oids = nil) initializer = OID::TypeMapInitializer.new(type_map) if supports_ranges? query = <<-SQL SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype FROM pg_type as t LEFT JOIN pg_range as r ON oid = rngtypid SQL else query = <<-SQL SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, t.typtype, t.typbasetype FROM pg_type as t SQL end if oids query += "WHERE t.oid::integer IN (%s)" % oids.join(", ") else query += initializer.query_conditions_for_initial_load(type_map) end execute_and_clear(query, "SCHEMA", []) do |records| initializer.run(records) end end
Prepare the statement if it hasn't been prepared, return the statement key.
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 674 def prepare_statement(sql) @lock.synchronize do sql_key = sql_key(sql) unless @statements.key? sql_key nextkey = @statements.next_key begin @connection.prepare nextkey, sql rescue => e raise translate_exception_class(e, sql) end # Clear the queue @connection.get_last_result @statements[sql_key] = nextkey end @statements[sql_key] end end
Returns the statement identifier for the client side cache of statements
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 668 def sql_key(sql) "#{schema_search_path}-#{sql}" end
# File lib/active_record/connection_adapters/postgresql_adapter.rb, line 416 def translate_exception(exception, message) return exception unless exception.respond_to?(:result) case exception.result.try(:error_field, PG::PG_DIAG_SQLSTATE) when UNIQUE_VIOLATION RecordNotUnique.new(message) when FOREIGN_KEY_VIOLATION InvalidForeignKey.new(message) when VALUE_LIMIT_VIOLATION ValueTooLong.new(message) when NUMERIC_VALUE_OUT_OF_RANGE RangeError.new(message) when NOT_NULL_VIOLATION NotNullViolation.new(message) when SERIALIZATION_FAILURE SerializationFailure.new(message) when DEADLOCK_DETECTED Deadlocked.new(message) else super end end