Changeset 7329

Timestamp:

08/16/07 06:26:30 (1 year ago)

Author:

nzkoz

Message:

Improve performance and functionality of the postgresql adapter. Closes #8049 [roderickvd]

Files:

• trunk/activerecord/CHANGELOG (modified) (1 diff)
• trunk/activerecord/lib/active_record/connection_adapters/abstract/quoting.rb (modified) (3 diffs)
• trunk/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb (modified) (28 diffs)
• trunk/activerecord/test/datatype_test_postgresql.rb (modified) (1 diff)
• trunk/activerecord/test/finder_test.rb (modified) (3 diffs)
• trunk/activerecord/test/fixtures/db_definitions/postgresql.drop.sql (modified) (1 diff)
• trunk/activerecord/test/fixtures/db_definitions/postgresql.sql (modified) (1 diff)
• trunk/railties/configs/databases/postgresql.yml (modified) (2 diffs)

trunk/activerecord/CHANGELOG

@@ -1 +1 @@
-*SVN*
+
+* Improve performance and functionality of the postgresql adapter.  Closes #8049 [roderickvd]
+
+        For more information see: http://dev.rubyonrails.org/ticket/8049
-
-* Don't clobber includes passed to has_many.count [danger]

trunk/activerecord/lib/active_record/connection_adapters/abstract/quoting.rb

@@ -12 +12 @@
-            value = value.to_s
-            if column && column.type == :binary && column.class.respond_to?(:string_to_binary)
-
+#{quoted_string_prefix}
-            elsif column && [:integer, :float].include?(column.type)
-              value = column.type == :integer ? value.to_i : value.to_f
-              value.to_s
-            else
-
+#{quoted_string_prefix}
-            end
-          when NilClass                 then "NULL"
@@ -29 +29 @@
-              "'#{quoted_date(value)}'"
-            else
-
+#{quoted_string_prefix}
-            end
-        end
@@ -57 +57 @@
-        value.to_s(:db)
-      end
+
+      def quoted_string_prefix
+        ''
+      end
-    end
-  end

trunk/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb

@@ -13 +13 @@
-      password = config[:password].to_s
-
-      min_messages = config[:min_messages]
-
-      if config.has_key?(:database)
-        database = config[:database]
@@ -21 +19 @@
-      end
-
-
-
-
-
-
-
-
-
-
+
+
+
-    end
-  end
-
-  module ConnectionAdapters
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-    #
-    # Options:
@@ -49 +222 @@
-    # * <tt>:allow_concurrency</tt> -- If true, use async query methods so Ruby threads don't deadlock; otherwise, use blocking query methods.
-    class PostgreSQLAdapter < AbstractAdapter
+      # Returns 'PostgreSQL' as adapter name for identification purposes.
-      def adapter_name
-        'PostgreSQL'
-      end
-
-
+
+
-        super(connection, logger)
-
-
-
-
-
-
+
+
+
-      end
-
@@ -68 +240 @@
-          @connection.status == PGconn::CONNECTION_OK
-        else
+          # We're asking the driver, not ActiveRecord, so use @connection.query instead of #query
-          @connection.query 'SELECT 1'
-          true
-        end
- is available
+ection is available.
-      rescue PGError, NoMethodError
-        false
@@ -78 +251 @@
-      # Close then reopen the connection.
-      def reconnect!
-        # TODO: postgres-pr doesn't have PGconn#reset.
-        if @connection.respond_to?(:reset)
-          @connection.reset
-          configure_connection
-
-
-
+
+
+
+
+
+
+
-      def disconnect!
-        # Both postgres and postgres-pr respond to :close
-        @connection.close rescue nil
-      end
-
-
+ #:nodoc:
-        {
-          :primary_key => "serial primary key",
@@ -107 +282 @@
-      end
-
+      # Does PostgreSQL support migrations?
-      def supports_migrations?
-        true
-      end
-
+      # Does PostgreSQL support standard conforming strings?
+      def supports_standard_conforming_strings?
+        # Temporarily set the client message level above error to prevent unintentional
+        # error messages in the logs when working on a PostgreSQL database server that
+        # does not support standard conforming strings.
+        client_min_messages_old = client_min_messages
+        self.client_min_messages = 'panic'
+
+        # postgres-pr does not raise an exception when client_min_messages is set higher
+        # than error and "SHOW standard_conforming_strings" fails, but returns an empty
+        # PGresult instead.
+        has_support = execute('SHOW standard_conforming_strings')[0][0] rescue false
+        self.client_min_messages = client_min_messages_old
+        has_support
+      end
+
+      # Returns the configured supported identifier length supported by PostgreSQL,
+      # or report the default of 63 on PostgreSQL 7.x.
-      def table_alias_length
-63
+@table_alias_length ||= (postgresql_version >= 80000 ? query('SHOW max_identifier_length')[0][0].to_i : 63)
-      end
-
-      # QUOTING ==================================================
-
-
+
+
-        if value.kind_of?(String) && column && column.type == :binary
-
+
+
+
+
+
+
+
+
+
+
+
+
+
-        else
-          super
@@ -125 +332 @@
-      end
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-        %("#{name}")
-      end
-
-
-
+
+
+
-        if value.acts_like?(:time) && value.respond_to?(:usec)
-          "#{super}.#{sprintf("%06d", value.usec)}"
@@ -141 +368 @@
-      # DATABASE STATEMENTS ======================================
-
-
+
+
-        execute(sql, name)
-        table = sql.split(" ", 4)[2]
@@ -147 +375 @@
-      end
-
+      # Queries the database and returns the results in an Array or nil otherwise.
-      def query(sql, name = nil) #:nodoc:
-        log(sql, name) do
@@ -157 +386 @@
-      end
-
-
+
+
+
-        log(sql, name) do
-          if @async
@@ -167 +398 @@
-      end
-
-
+
+
-        execute(sql, name).cmdtuples
-      end
-
-
+
+
-        execute "BEGIN"
-      end
-
-
+
+
-        execute "COMMIT"
-      end
-
-
+
+
-        execute "ROLLBACK"
-      end
@@ -185 +420 @@
-      # SCHEMA STATEMENTS ========================================
-
- the list of all tables in the schema search path
- #:nodoc:
+s the list of all tables in the schema search path or a specified schema
+
-        schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',')
-        query(<<-SQL, name).map { |row| row[0] }
@@ -195 +430 @@
-      end
-
-
+
+
-        result = query(<<-SQL, name)
-          SELECT i.relname, d.indisunique, a.attname
@@ -228 +464 @@
-      end
-
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-        if schema_csv
-          execute "SET search_path TO #{schema_csv}"
-
-
-
-
-
+
+
+
+
+
+
-        @schema_search_path ||= query('SHOW search_path')[0][0]
-      end
-
-
+
+
+
+
+
+
+
+
+
+
+
+
-        default_pk, default_seq = pk_and_sequence_for(table_name)
-        default_seq || "#{table_name}_#{pk || default_pk || 'id'}_seq"
-      end
-
-sequence to the max value of the table's pk if present
-
+the sequence of a table's primary key to the maximum value
+ #:nodoc:
-        unless pk and sequence
-          default_pk, default_sequence = pk_and_sequence_for(table)
@@ -272 +523 @@
-      end
-
-Find a table's primary key and
-
+Returns a table's primary key and belonging
+ #:nodoc:
-        # First try looking for a sequence with a dependency on the
-        # given table's primary key.
-        result = query(<<-end_sql, 'PK and serial sequence')[0]
-name.nspname, 
+
-          FROM pg_class      seq,
-               pg_attribute  attr,
@@ -284 +535 @@
-               pg_constraint cons
-          WHERE seq.oid           = dep.objid
-            AND seq.relnamespace  = name.oid
-            AND seq.relkind       = 'S'
-            AND attr.attrelid     = dep.refobjid
@@ -298 +548 @@
-          # Support the 7.x and 8.0 nextval('foo'::text) as well as
-          # the 8.1+ nextval('foo'::regclass).
-          # TODO: assumes sequence is in same schema as table.
-          result = query(<<-end_sql, 'PK and custom sequence')[0]
-name.nspname, 
+
-            FROM pg_class       t
-            JOIN pg_namespace   name ON (t.relnamespace = name.oid)
-            JOIN pg_attribute   attr ON (t.oid = attrelid)
-            JOIN pg_attrdef     def  ON (adrelid = attrelid AND adnum = attnum)
@@ -311 +559 @@
-          end_sql
-        end
-
-
+
-        [result.first, result.last]
-      rescue
@@ -318 +565 @@
-      end
-
+      # Renames a table.
-      def rename_table(name, new_name)
-        execute "ALTER TABLE #{name} RENAME TO #{new_name}"
-      end
-
+      # Adds a column to a table.
-      def add_column(table_name, column_name, type, options = {})
-        default = options[:default]
@@ -344 +593 @@
-      end
-
-
+
+
-        begin
-          execute "ALTER TABLE #{table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
-        rescue ActiveRecord::StatementInvalid
-G7, so we
+ostgreSQL 7.x, so we have to
-          begin_db_transaction
-          tmp_column_name = "#{column_name}_ar_tmp"
@@ -363 +613 @@
-      end
-
-
+
+
-        execute "ALTER TABLE #{table_name} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote(default)}"
-      end
-
-
+
+
-        execute "ALTER TABLE #{table_name} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}"
-      end
-
-
+
+
-        execute "DROP INDEX #{index_name(table_name, options)}"
-      end
-
-
+
+
-        return super unless type.to_s == 'integer'
-
@@ -387 +641 @@
-      end
-      
-
+Returns a 
-      #
-      # PostgreSQL requires the ORDER BY columns in the select list for distinct queries, and
@@ -393 +647 @@
-      #
-      #   distinct("posts.id", "posts.created_at desc")
-
+ #:nodoc:
-        return "DISTINCT #{columns}" if order_by.blank?
-
-c
-asc/desc
+C
+ASC/DESC
-        order_columns = order_by.split(',').collect { |s| s.split.first }
-        order_columns.delete_if &:blank?
-        order_columns = order_columns.zip((0...order_columns.size).to_a).map { |s,i| "#{s} AS alias_#{i}" }
-
-r
-
+R
+.
-        sql = "DISTINCT ON (#{columns}) #{columns}, "
-        sql << order_columns * ', '
-      end
-      
-
+Returns a 
-      # 
-      # PostgreSQL does not allow arbitrary ordering when using DISTINCT ON, so we work around this
-      # by wrapping the sql as a sub-select and ordering in that query.
-
+ #:nodoc:
-        return sql if options[:order].blank?
-        
@@ -422 +676 @@
-      end
-
+      protected
+        # Returns the version of the connected PostgreSQL version.
+        def postgresql_version
+          @postgresql_version ||=
+            if @connection.respond_to?(:server_version)
+              @connection.server_version
+            else
+              # Mimic PGconn.server_version behavior
+              begin
+                query('SELECT version()')[0][0] =~ /PostgreSQL (\d+)\.(\d+)\.(\d+)/
+                ($1.to_i * 10000) + ($2.to_i * 100) + $3.to_i
+              rescue
+                0
+              end
+            end
+        end
+
-      private
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-        def configure_connection
-          if @config[:encoding]
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
-          Integer(select_value("SELECT currval('#{sequence_name}')"))
-        end
-
+        # Executes a SELECT query and returns the results, performing any data type
+        # conversions that require to be performed here instead of in PostgreSQLColumn.
-        def select(sql, name = nil)
-          res = execute(sql, name)
@@ -449 +763 @@
-            results.each do |row|
-              hashed_row = {}
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-                end
-
-
+l
-              end
-              rows << hashed_row
@@ -468 +792 @@
-        end
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-        #
-        # The underlying query is roughly:
@@ -534 +810 @@
-        #  - format_type includes the column size constraint, e.g. varchar(50)
-        #  - ::regclass is a function that gives the id for a table name
-
+ #:nodoc:
-          query <<-end_sql
-            SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull
@@ -544 +820 @@
-          end_sql
-        end
-
-        # Translate PostgreSQL-specific types into simplified SQL types.
-        # These are special cases; standard types are handled by
-        # ConnectionAdapters::Column#simplified_type.
-        def translate_field_type(field_type)
-          # Match the beginning of field_type since it may have a size constraint on the end.
-          case field_type
-            # PostgreSQL array data types.
-            when /\[\]$/i  then 'string'
-            when /^timestamp/i    then 'datetime'
-            when /^real|^money/i  then 'float'
-            when /^interval/i     then 'string'
-            # geometric types (the line type is currently not implemented in postgresql)
-            when /^(?:point|lseg|box|"?path"?|polygon|circle)/i  then 'string'
-            when /^bytea/i        then 'binary'
-            else field_type       # Pass through standard types.
-          end
-        end
-
-        def default_value(value)
-          # Boolean types
-          return "t" if value =~ /true/i
-          return "f" if value =~ /false/i
-
-          # Char/String/Bytea type values
-          return $1 if value =~ /^'(.*)'::(bpchar|text|character varying|bytea)$/
-
-          # Numeric values
-          return value if value =~ /^-?[0-9]+(\.[0-9]*)?/
-
-          # Fixed dates / times
-          return $1 if value =~ /^'(.+)'::(date|timestamp)/
-
-          # Anything else is blank, some user type, or some function
-          # and we can't know the value of that, so return nil.
-          return nil
-        end
-    end
-  end

trunk/activerecord/test/datatype_test_postgresql.rb

@@ -1 +1 @@
-require 'abstract_unit'
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-  self.use_transactional_fixtures = false